On Sun, Mar 11, 2018 at 4:32 PM, <[email protected]> wrote: > Thank you Mike! > > I hoped that we can do without all the details, but I guess not. This > warning has been (quite literally) bugging me for days. I’m very tempted to > ignore it because the warning seems to be a recent regression (see below) > and also because we’re moving to PostgreSQL very soon… > > First off, I do pass a byte string and have from the beginning of our > project. Here is SA’s debug output of the offending statement (I’ve > shortened it somewhat to remove clutter): > > 1:17:19 INFO [sqlalchemy.engine.base.Engine][MainThread] INSERT INTO > profiles (id, …, address1, address2, city, state, zipcode, country) VALUES > (%(id)s, …, %(address1)s, %(address2)s, %(city)s, %(state)s, %(zipcode)s, > %(country)s) > 21:17:19 INFO [sqlalchemy.engine.base.Engine][MainThread] {…, 'state': > None, 'address1': None, 'city': > b'z\xf9\x87jS?\xd4i\xa5\xa3\r\xa7\x1e\xed\x16\xe0\xb5\x05R\xa4\xec\x16\x8f\x06\xb5\xea+\xaf<\x00\\\x94I9A\xe0\x82\xa7\x13\x0c\x8c', > …} > > In that particular statement, five `blob` columns are updated which results > in five warnings one of which is: > > /…/lib/python3.5/site-packages/pymysql/cursors.py:165: Warning: (1300, > "Invalid utf8mb4 character string: 'F9876A'") > > The character string 'F9876A' are bytes 1-3 b'\xf9\x87j' passed to the > `city` column. The column and table are defined like so: > > mysql> show create table profiles; > | profiles | CREATE TABLE `profiles` ( > `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, > … > `address1` blob, > `address2` blob, > `city` blob NOT NULL, > `state` blob, > `zipcode` tinyblob, > `country` blob NOT NULL, > … > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | > > My db connect string is: > > mysql+pymysql://jens@localhost/test?charset=utf8mb4&unix_socket=/opt/local/var/run/mysql56/mysqld.sock > > This happens with Python 3.5.5 and 3.6.3. In a venv update one week ago I > updated from the following packet versions: > > pymysql==0.7.11 → 0.8.0 > sqlalchemy==1.1.13 → 1.2.4 > zope.sqlalchemy==0.7.7 → 1.0 > > These warnings started to appear since that update; and I wonder if perhaps > they are a result of a regression from that update. > > Please let me know if I can add more details, > Jens > > > > > On Monday, March 12, 2018 at 12:11:35 AM UTC+10, Mike Bayer wrote: >> >> first off I have a feeling this could be avoided if you passed a >> bytestring to the driver and not a unicode object, however I would >> need complete details on python version, driver in use, an example >> string, and the specific column type you are working with. >> >> I've not seen an "introducer" before and not sure that is applicable >> to a bound parameter passed by the driver. COLLATE is supported by >> the method: >> >> conn.execute( >> >> table.insert().values(foobar=bindparam("value").collate("some_collation"), >> {"value": "myvalue"} >> ) >> >> but that's with Core, not ORM, and as usual, I really need to see >> completely what you are doing. This is likely resolvable by just >> passing a python bytestring and not a unicode object.
the issue is Python 3 only and reproduces with PyMySQL 0.8.0 and not with 0.7.1. Hopefully this is a PyMySQL bug that can be resolved quickly (note I might recall seeing this recently but could not find any discussion about it). Posted up as https://github.com/PyMySQL/PyMySQL/issues/644 . > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout.<div > class="gmail_extra"><br><div class="gmail_quote">On Sun, Mar 11, 2018 at 4:32 > PM, <span dir="ltr"><<a href="mailto:[email protected]"; > target="_blank">[email protected]</a>></span> wrote:<br><blockquote > class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc > solid;padding-left:1ex"><div dir="ltr">Thank you Mike!<div><br></div><div>I > hoped that we can do without all the details, but I guess not. This warning > has been (quite literally) bugging me for days. I’m very tempted to ignore it > because the warning seems to be a recent regression (see below) and also > because we’re moving to PostgreSQL very soon…</div><div><br></div><div>First > off, I <i>do</i> pass a byte string and have from the beginning of our > project. Here is SA’s debug output of the offending statement (I’ve shortened > it somewhat to remove clutter):</div><div><br></div><div><div > class="m_3473975489019439721prettyprint" > style="background-color:rgb(250,250,250);border-color:rgb(187,187,187);border-style:solid;border-width:1px;word-wrap:break-word"><code > class="m_3473975489019439721prettyprint"><div > class="m_3473975489019439721subprettyprint"><div > class="m_3473975489019439721subprettyprint">1:17:19 INFO > [sqlalchemy.engine.base.<wbr>Engine][MainThread] INSERT INTO profiles (id, …, > address1, address2, city, state, zipcode, country) VALUES (%(id)s, …, > %(address1)s, %(address2)s, %(city)s, %(state)s, %(zipcode)s, > %(country)s)</div><div class="m_3473975489019439721subprettyprint">21:17:19 > INFO [sqlalchemy.engine.base.<wbr>Engine][MainThread] {…, 'state': > None, 'address1': None, 'city': > b'z\xf9\x87jS?\xd4i\xa5\xa3\r\<wbr>xa7\x1e\xed\x16\xe0\xb5\x05R\<wbr>xa4\xec\x16\x8f\x06\xb5\xea+\<wbr>xaf<\x00\\\x94I9A\xe0\x82\xa7\<wbr>x13\x0c\x8c', > …}</div></div></code></div><div><br></div>In that particular statement, five > `blob` columns are updated which results in five warnings one of which > is:</div><div><br></div><div><div class="m_3473975489019439721prettyprint" > style="background-color:rgb(250,250,250);border-color:rgb(187,187,187);border-style:solid;border-width:1px;word-wrap:break-word"><code > class="m_3473975489019439721prettyprint"><div > class="m_3473975489019439721subprettyprint"><font > color="#660066">/…/lib/python3.5/site-<wbr>packages/pymysql/cursors.py:<wbr>165: > Warning: (1300, "Invalid utf8mb4 character string: > 'F9876A'")</font><br></div></code></div><br>The character string 'F9876A' are > bytes 1-3 b'\xf9\x87j' passed to the `city` column. The column and table are > defined like so:</div><div><br></div><div><div > class="m_3473975489019439721prettyprint" > style="background-color:rgb(250,250,250);border-color:rgb(187,187,187);border-style:solid;border-width:1px;word-wrap:break-word"><code > class="m_3473975489019439721prettyprint"><div > class="m_3473975489019439721subprettyprint"><div > class="m_3473975489019439721subprettyprint">mysql> show create table > profiles;</div><div class="m_3473975489019439721subprettyprint">| profiles | > CREATE TABLE `profiles` (</div><div > class="m_3473975489019439721subprettyprint"> `id` varchar(32) COLLATE > utf8mb4_unicode_ci NOT NULL,</div><div > class="m_3473975489019439721subprettyprint"> …</div><div > class="m_3473975489019439721subprettyprint"> `address1` blob,</div><div > class="m_3473975489019439721subprettyprint"> `address2` blob,</div><div > class="m_3473975489019439721subprettyprint"> `city` blob NOT > NULL,</div><div class="m_3473975489019439721subprettyprint"> `state` > blob,</div><div class="m_3473975489019439721subprettyprint"> `zipcode` > tinyblob,</div><div class="m_3473975489019439721subprettyprint"> > `country` blob NOT NULL,</div><div > class="m_3473975489019439721subprettyprint"> …</div><div > class="m_3473975489019439721subprettyprint">) ENGINE=InnoDB DEFAULT > CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci > |</div></div></code></div><div><br></div>My db connect string > is:</div><div><br></div><div><div class="m_3473975489019439721prettyprint" > style="background-color:rgb(250,250,250);border-color:rgb(187,187,187);border-style:solid;border-width:1px;word-wrap:break-word"><code > class="m_3473975489019439721prettyprint"><div > class="m_3473975489019439721subprettyprint"><font > color="#660066">mysql+pymysql://jens@localhost<wbr>/test?charset=utf8mb4&unix_<wbr>socket=/opt/local/var/run/<wbr>mysql56/mysqld.sock</font><br></div></code></div><br>This > happens with Python 3.5.5 and 3.6.3. In a venv update one week ago I updated > from the following packet versions:</div><div><br></div><div><div > class="m_3473975489019439721prettyprint" > style="background-color:rgb(250,250,250);border-color:rgb(187,187,187);border-style:solid;border-width:1px;word-wrap:break-word"><code > class="m_3473975489019439721prettyprint"><div > class="m_3473975489019439721subprettyprint"><span style="color:#000" > class="m_3473975489019439721styled-by-prettify">pymysql</span><span > style="color:#660" > class="m_3473975489019439721styled-by-prettify">==</span><span > style="color:#066" > class="m_3473975489019439721styled-by-prettify">0.7</span><span > style="color:#660" > class="m_3473975489019439721styled-by-prettify">.</span><span > style="color:#066" > class="m_3473975489019439721styled-by-prettify">11</span><span > style="color:#000" class="m_3473975489019439721styled-by-prettify"> > </span><span style="color:#660" > class="m_3473975489019439721styled-by-prettify">→</span><span > style="color:#000" class="m_3473975489019439721styled-by-prettify"> > </span><span style="color:#066" > class="m_3473975489019439721styled-by-prettify">0.8</span><span > style="color:#660" > class="m_3473975489019439721styled-by-prettify">.</span><span > style="color:#066" > class="m_3473975489019439721styled-by-prettify">0</span><span > style="color:#000" > class="m_3473975489019439721styled-by-prettify"><br>sqlalchemy</span><span > style="color:#660" > class="m_3473975489019439721styled-by-prettify">==</span><span > style="color:#066" > class="m_3473975489019439721styled-by-prettify">1.1</span><span > style="color:#660" > class="m_3473975489019439721styled-by-prettify">.</span><span > style="color:#066" > class="m_3473975489019439721styled-by-prettify">13</span><span > style="color:#000" class="m_3473975489019439721styled-by-prettify"> > </span><span style="color:#660" > class="m_3473975489019439721styled-by-prettify">→</span><span > style="color:#000" class="m_3473975489019439721styled-by-prettify"> > </span><span style="color:#066" > class="m_3473975489019439721styled-by-prettify">1.2</span><span > style="color:#660" > class="m_3473975489019439721styled-by-prettify">.</span><span > style="color:#066" > class="m_3473975489019439721styled-by-prettify">4</span><span > style="color:#000" > class="m_3473975489019439721styled-by-prettify"><br>zope</span><span > style="color:#660" > class="m_3473975489019439721styled-by-prettify">.</span><span > style="color:#000" > class="m_3473975489019439721styled-by-prettify">sqlalchemy</span><span > style="color:#660" > class="m_3473975489019439721styled-by-prettify">==</span><span > style="color:#066" > class="m_3473975489019439721styled-by-prettify">0.7</span><span > style="color:#660" > class="m_3473975489019439721styled-by-prettify">.</span><span > style="color:#066" > class="m_3473975489019439721styled-by-prettify">7</span><span > style="color:#000" class="m_3473975489019439721styled-by-prettify"> > </span><span style="color:#660" > class="m_3473975489019439721styled-by-prettify">→</span><span > style="color:#000" class="m_3473975489019439721styled-by-prettify"> > </span><span style="color:#066" > class="m_3473975489019439721styled-by-prettify">1.0</span></div></code></div><div><br></div></div><div>These > warnings started to appear since that update; and I wonder if perhaps they > are a result of a regression from that > update.</div><div><br></div><div>Please let me know if I can add more > details,<br>Jens</div><span > class=""><div><br></div><div><br></div><div><br><br>On Monday, March 12, 2018 > at 12:11:35 AM UTC+10, Mike Bayer wrote:<blockquote class="gmail_quote" > style="margin:0;margin-left:0.8ex;border-left:1px #ccc > solid;padding-left:1ex">first off I have a feeling this could be avoided if > you passed a <br>bytestring to the driver and not a unicode object, however I would <br>need complete details on python version, driver in use, an example <br>string, and the specific column type you are working with. <br> <br>I've not seen an "introducer" before and not sure that is applicable <br>to a bound parameter passed by the driver. COLLATE is supported by <br>the method: <br> <br>conn.execute( <br> table.insert().values(foobar=b<wbr>indparam("value").collate("som<wbr>e_collation"), <br> {"value": "myvalue"} <br>) <br> <br>but that's with Core, not ORM, and as usual, I really need to see <br>completely what you are doing. This is likely resolvable by just <br>passing a python bytestring and not a unicode object. <br></blockquote></div></span></div><div class="HOEnZb"><div class="h5"> <p></p> -- <br> SQLAlchemy - <br> The Python SQL Toolkit and Object Relational Mapper<br> <br> <a href="http://www.sqlalchemy.org/"; target="_blank" data-saferedirecturl="https://www.google.com/url?hl=en&q=http://www.sqlalchemy.org/&source=gmail&ust=1520901705485000&usg=AFQjCNEMCUiYTx4D06o5Vca5W-gzwS03VA";>http://www.sqlalchemy.org/</a><br> <br> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See <a href="http://stackoverflow.com/help/mcve"; target="_blank" data-saferedirecturl="https://www.google.com/url?hl=en&q=http://stackoverflow.com/help/mcve&source=gmail&ust=1520901705485000&usg=AFQjCNGTnPQJdxIoXaFINJcJkj90mI_-Fg";>http://stackoverflow.com/help/<wbr>mcve</a> for a full description.<br> --- <br> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.<br> To unsubscribe from this group and stop receiving emails from it, send an email to <a href="mailto:[email protected]"; target="_blank">sqlalchemy+unsubscribe@<wbr>googlegroups.com</a>.<br> To post to this group, send email to <a href="mailto:[email protected]"; target="_blank">[email protected]</a>.<br> Visit this group at <a href="https://groups.google.com/group/sqlalchemy"; target="_blank" data-saferedirecturl="https://www.google.com/url?hl=en&q=https://groups.google.com/group/sqlalchemy&source=gmail&ust=1520901705485000&usg=AFQjCNF_Da52mL33Cs5o735WIm7zChy3uA";>https://groups.google.com/<wbr>group/sqlalchemy</a>.<br> For more options, visit <a href="https://groups.google.com/d/optout"; target="_blank" data-saferedirecturl="https://www.google.com/url?hl=en&q=https://groups.google.com/d/optout&source=gmail&ust=1520901705485000&usg=AFQjCNED-DMNm7Vx7jyhOMmnN68eG-yRBQ";>https://groups.google.com/d/<wbr>optout</a>.<br> </div></div></blockquote></div><br></div> -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
