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. 
>

-- 
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