MySQLdb & client encoding

[zobacz: wersję polską]

MySQLdb it the main pythonic library for MySQL. This database (version >=4.1) has convenient feature for translating character set displayed to a client (eg. browser). Of course, for multilangual texts you have to choose native UTF-8 encoding for your databases. You can display data for client in character encoding of your choice using this simple query: SET NAMES xxx (where xxx can be utf8, latin2 etc.)

Recently, during working with Pylons, I found very interested another project: SQLAlchemy. Everything shows that old SQLObject will be replaced. SQLAlchemy is much more powerfull ORM, and it has much better documentation as well (117 pages of documentation).

The only problem is, and this is very common for English developers, lacking of clear option for translating non-ASCII characters. The same problem I have found in Django (I have send them my patch and they fixed core code by hardcoding “SET NAMES utf8”) In case of SQLAlchemy there is no option which could allow for define client encoding. The documentation writes only about database encoding, which is not the same.

But, fortunately, there is solution! You have to add few additional parameters during connection to the database. There is no need for any queries like “SET NAMES xxx”. The only you need is using following parameters: charset and/or use_unicode:

import MySQLdb
conn = MySQLdb.connect(
    user='root', 
    passwd='', 
    db='test', 
    use_unicode=False, 
    charset='cp1250')

In my example, MySQL database will translate all characters from its internal utf-8 to cp1250. If you choose “use_unicode=True” you will have nice pythonic Unicode object returned!

Example of config file (config/init.py) for Pylons and SQLAlchemy:

from sqlalchemy import *
import sqlalchemy.pool as pool
import MySQLdb

def getconn():
    return MySQLdb.connect(
        user='root', 
        passwd='', 
        db='test', 
        use_unicode=False, 
        charset='utf8')

db = create_engine(
    'mysql://root:@localhost/test',
    pool=pool.QueuePool(getconn, pool_size=20, max_overflow=40), 
    strategy='threadlocal')
metadata = BoundMetaData(db)

test_table = Table('test', metadata, autoload=True)

class Test(object):  
    def __str__(self):  
        return self.title  

test_mapper = mapper(Test, test_table)

In this code I’ve made database using UTF-8 encoding for client with connection using 20-40 threads in a pool. This is what I could not find in Django: multithreading (e.g. because it uses less RAM). The code mentioned above can be utilized in Pylons controller (controllers/home.py) in the following way:

from myproject.lib.base import *
from myproject.models import *

class HomeController(BaseController):
  def index(self):        
      c.rows = select([test_table.c.id, test_table.c.name]).execute()
      return render_response('/home.myt')

[goto comments]