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, 
21:17:19 INFO  [sqlalchemy.engine.base.Engine][MainThread] {…, 'state': 
None, 'address1': None, 'city': 

In that particular statement, five `blob` columns are updated which results 
in five warnings one of which is:

/…/lib/python3.5/site-packages/pymysql/ 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:


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,

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

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See 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 post to this group, send email to
Visit this group at
For more options, visit

Reply via email to