Jim DeLaHunt, world-ready

I post on various forums around the net, and a few of my posts there get some very gratifying kudos. I’ve been a diligent contributor to StackOverflow, the Q-and-A site for software developers. I’m in the top 15% of contributors overall, and one of the top 25 answerers of Unicode-related questions. Here’s my second best-voted answer in StackOverflow so far.

The question, How do I get SQLAlchemy to correctly insert a unicode ellipsis into a mySQL table?,  was asked by user kvedananda in February 2012. In abbreviated form, it was:

I am trying to parse an RSS feed with feedparser and insert it into a mySQL table using SQLAlchemy. I was actually able to get this running just fine but today the feed had an item with an ellipsis character in the description and I get the following error:

UnicodeEncodeError: 'latin-1' codec can't encode character u'\u2026'
   in position 35: ordinal not in range(256)

If I add the convert_unicode=True option to the engine I am able to get the insert to go through, but the ellipsis doesn’t show up — it’s just weird characters. This seems to make sense, since to the best of my knowledge there is no horizontal ellipsis in latin-1. Even if I set the encoding to utf-8 it doesn’t seem to make a difference. If I do an insert using phpmyadmin, and include the ellipsis, it goes through fine.

I’m thinking I just don’t understand character encodings or how to get SQLAlchemy to use one I specify. Does anyone know how to get the text to go in without weird characters?

This is my answer to StackOverflow.

The error message

UnicodeEncodeError: 'latin-1' codec can't encode character u'\u2026'
   in position 35: ordinal not in range(256)

seems to indicate that some Python language code is trying to convert the character \u2026 into a Latin-1 (ISO8859-1) string, and it is failing. Not surprising, that character is U+2026 HORIZONTAL ELLIPSIS, which has no single equivalent character in ISO8859-1.You fixed the problem by adding the query ?charset=utf8 in your SQLAlchemy connection call:

import sqlalchemy 
from sqlalchemy import create_engine, MetaData, Table

  db = create_engine('mysql://user:pass@localhost/db?charset=utf8')

The section Database Urls of the SQLAlchemy documentation tells us that a URL beginning with mysql indicates a MySQL dialect, using the mysql-python driver.

The following section, Custom DBAPI connect() arguments, tells us that query arguments are passed to the underlying DBAPI.

So, what does the mysql-python driver make of a parameter {charset: 'utf8'}? Section Functions and attributes of their documentation says of the charset attribute “…If present, the connection character set will be changed to this character set, if they are not equal.”

To find out what the connection character set means, we turn to 10.1.4. Connection Character Sets and Collations of the MySQL 5.6 reference manual. To make a long story short, MySQL can have interpret incoming queries as an encoding different than the database’s character set, and different than the encoding of the returned query results.

Since the error message you reported looks like a Python rather than a SQL error message, I’ll speculate that something in SQLAlchemy or mysql-python is attempting to convert the query to a default connection encoding of latin-1 before sending it. This is what triggers the error. However, the query string ?charset=utf8 in your connect() call changes the connection encoding, and the U+2026 HORIZONTAL ELLIPSIS is able to get through.

Update: you also ask, “if I remove the charset option and then encode the description using .encode(‘cp1252’) it will go through just fine. How is an ellipsis able to get through with cp1252 but not unicode?”

The encoding cp1252 has a horizontal ellipsis character at byte value \x85. Thus it is possible to encode a Unicode string containing U+2026 HORIZONTAL ELLIPSIS into cp1252 without error.

Remember also that in Python, Unicode strings and byte strings are two different data types. It’s reasonable to speculate that MySQLdb might have a policy of sending only byte strings over a SQL connection. Thus it would encode a query received as a Unicode string into a byte string, but would leave a query received as a byte string alone. (This is speculation, I haven’t looked at the source code.)

In the traceback you posted, the last two lines (closest to where the error occur) show the method names literal, followed by unicode_literal. That tends to support the theory that MySQLdb is encoding the query it receives as a Unicode string into a byte string.

When you encode the query string yourself, you bypass the part of MySQLdb that does this encoding differently. Note, however, that if you encode the query string differently than the MySQL connection charset calls for, then you’ll have an encoding mismatch, and your text will likely be stored wrong.

No Comments »