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