Friday, December 29, 2006

English and the internet.

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

Wednesday, December 06, 2006

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'"
value = show.fetchone()['Value']

q = """SET NAMES '%s' """ % value

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