English and the internet.

Remember, you don't need to be authentificated in order to use the English language.

Preventing collation errors while using MySQLdb and Python

MySQLdb is a great module to access MySQL databases. Nasty surprises sometimes do crop up though. Upon performing a simple query where two SELECT statements were UNIONed, the following error occurred:

_mysql_exceptions.OperationalError: (1267, "Illegal mix of collations (latin2_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION'")

A bit of googling brought me to this excellent write-up of how someone with the same problems fixed them from the (PHP) client side.

I translated it to Python, and all UNIONs (up till now) work without a flaw.

Here's the code:

import MySQLdb
import MySQLdb.cursors # Import this so that DictCursors can be used.

# host, user, passwd and db to be defined by user ofcourse.
db = MySQLdb.connect(host = host, user = user, passwd = passwd, db = db, cursorclass=MySQLdb.cursors.DictCursor)

show = db.cursor()
q = "SHOW VARIABLES LIKE 'character_set_database'"
show.execute(q)
value = show.fetchone()['Value']

q = """SET NAMES '%s' """ % value
show.execute(q)

And that's all there is to it. Thanks to Juliette at adviesenzo.nl!