Hello!
First and foremost, I want to apology if this email shouldn't go into
this email list.
Ok, I'm facing this ugly problem in SQLAlchemy with MySQL. Every time I
want to insert a Unicode string into the database this error is
triggered:
sqlalchemy.exc.DBAPIError: (CollatedWarningsError) (None, 'query
caused warnings', [(<class 'oursql.Warning'>, (u"Incorrect string
value: '\\xF0\\x9F\\x92\\xAF' for column 'text' at row 1", 1366L))])
I have searched on the Internet for an answer for my problem, but, so
far, the solutions I have found do not work. I know that for 4-byte
Unicode characters you need your database to use the `utf8mb4`
charset. I did exactly that, I delete my database and created it with
`CREATE DATABASE test_facebook_db CHARACTER SET utf8mb4;`, here are some
variables and their values:
MariaDB [test_facebook_db]> show variables like 'coll%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
MariaDB [test_facebook_db]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
MariaDB [test_facebook_db]> show full columns from tt_post;
+-------------------------+--------------+--------------------+-
| Field | Type | Collation |
+-------------------------+--------------+--------------------+-
| id | int(11) | NULL |
| username_id | int(11) | NULL |
| added | datetime | NULL |
| created_at | datetime | NULL |
| favorite_count | bigint(20) | NULL |
| filter_level | varchar(10) | utf8mb4_general_ci |
| tweet_id | bigint(20) | NULL |
| in_reply_to_screen_name | varchar(40) | utf8mb4_general_ci |
| in_reply_to_status_id | bigint(20) | NULL |
| in_reply_to_user_id | bigint(20) | NULL |
| lang | varchar(3) | utf8mb4_general_ci |
| possibly_sensitive | tinyint(1) | NULL |
| retweet_count | bigint(20) | NULL |
| text | varchar(160) | utf8mb4_general_ci |
+-------------------------+--------------+--------------------+-
And yet, if something like the string u'You gotta be who you are in this
world. \ud83d\udcaf' is received and I try to insert it into the
database, the CollatedWarningsError is triggered.
The string I'm using to create my sqlalchemy engine is this
`mysql+oursql://dev:devpassword@localhost/test_facebook_db?default_charset=1&charset=utf8mb4"`
I'm not doing anything special in my tables or when I issue the command
to create the tables in the database.
--
Pax et bonum.
Jorge Araya Navarro.
Diseñador Publicitario, Programador Python y colaborador en Parabola
GNU/Linux-libre
https://es.gravatar.com/shackra
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
