On Sun, Mar 11, 2018 at 4:32 PM,  <jens.troe...@gmail.com> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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">&lt;<a href="mailto:jens.troe...@gmail.com"; 
> target="_blank">jens.troe...@gmail.com</a>&gt;</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&nbsp; 
> [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&nbsp; [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&lt;\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&gt; show create table 
> profiles;</div><div class="m_3473975489019439721subprettyprint">| profiles | 
> CREATE TABLE `profiles` (</div><div 
> class="m_3473975489019439721subprettyprint">&nbsp; `id` varchar(32) COLLATE 
> utf8mb4_unicode_ci NOT NULL,</div><div 
> class="m_3473975489019439721subprettyprint">&nbsp; …</div><div 
> class="m_3473975489019439721subprettyprint">&nbsp; `address1` blob,</div><div 
> class="m_3473975489019439721subprettyprint">&nbsp; `address2` blob,</div><div 
> class="m_3473975489019439721subprettyprint">&nbsp; `city` blob NOT 
> NULL,</div><div class="m_3473975489019439721subprettyprint">&nbsp; `state` 
> blob,</div><div class="m_3473975489019439721subprettyprint">&nbsp; `zipcode` 
> tinyblob,</div><div class="m_3473975489019439721subprettyprint">&nbsp; 
> `country` blob NOT NULL,</div><div 
> class="m_3473975489019439721subprettyprint">&nbsp; …</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&amp;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. &nbsp; COLLATE is supported by
<br>the method:
<br>
<br>conn.execute(
<br>&nbsp; &nbsp;
table.insert().values(foobar=b<wbr>indparam("value").collate("som<wbr>e_collation"),
<br>&nbsp; &nbsp; {"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. &nbsp;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>
&nbsp;<br>
<a href="http://www.sqlalchemy.org/"; target="_blank"
data-saferedirecturl="https://www.google.com/url?hl=en&amp;q=http://www.sqlalchemy.org/&amp;source=gmail&amp;ust=1520901705485000&amp;usg=AFQjCNEMCUiYTx4D06o5Vca5W-gzwS03VA";>http://www.sqlalchemy.org/</a><br>
&nbsp;<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&amp;q=http://stackoverflow.com/help/mcve&amp;source=gmail&amp;ust=1520901705485000&amp;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:sqlalchemy+unsubscr...@googlegroups.com";
target="_blank">sqlalchemy+unsubscribe@<wbr>googlegroups.com</a>.<br>
To post to this group, send email to <a
href="mailto:sqlalchemy@googlegroups.com";
target="_blank">sqlalchemy@googlegroups.com</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&amp;q=https://groups.google.com/group/sqlalchemy&amp;source=gmail&amp;ust=1520901705485000&amp;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&amp;q=https://groups.google.com/d/optout&amp;source=gmail&amp;ust=1520901705485000&amp;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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Reply via email to