Can't set character_set_results to latin1

Question

I've decided to use Django 1.7 for the first time with Python 3. I need to be able to work with legacy latin1 database which contains utf8 data. I know it sucks, but the database is so huge that it's really impossible to change this. So I tried following:

DATABASES = {
    'ENGINE' : 'django.db.backends.mysql', // using MySQL-python fork with support for py3
    ...
    'OPTIONS' : {
        'init_command': "SET character_set_results = 'latin1'",
        #'read_default_file': '/etc/my.cnf.d/client.cnf', // I've also tried this one
    }
}

I've also tried python-mysql-connector from Oracle with following setup

DATABASES = {
    'ENGINE' : 'mysql.connector.django', // using MySQL-python fork with support for py3
    'OPTIONS' : {
        'option_files': ['/etc/my.cnf.d/client.cnf'],
    }
}

/etc/my.cnf.d/client.cnf

[client]
init-command='SET character_set_results = "latin1"'
# password, host, username

In both cases I can connect to database, but it seems like Django sets character_set_results back to utf8.

I've tried following

from django.db import connection

with connection.cursor() as c:
   // I expect variable to be 'latin1'
   c.execute("show variables like 'character_set_results%'")
   c.fetchone() // returns ('character_set_results', 'utf8')

   // here I try to set it manually
   c.execute("SET character_set_results = 'latin1'")
   c.execute("show variables like 'character_set_results%'")
   c.fetchone() // returns ('character_set_results', 'latin1') // now it's OK

• I'm sure django uses client.cfg file and correct [section], because it contains username/password and it successfully connects to the database
• When I use mysql command in the linux terminal which uses the same configuration file, everything works as expected

So I guess Django somehow enforces character_set_results variable to be utf8. Is it possible? Is there any way how can I solve this issue?

Thank you very much

Answer 1

Not really a full-blown answer but a bit too long for a comment so...

Django's MySQL wrapper sets kwargs['charset']='utf8' as default in DatabaseWrapper.get_connection_params(). This dict is then passed to MySQLdb's Connection.__init__, which documents that:

charset
If supplied, the connection character set will be changed
to this character set (MySQL-4.1 and newer). This implies
use_unicode=True.

So a starting point might be to just add "charset":"latin1" in your OPTIONS dict ?

WARNING : I'm not sure it will solve your problem, and it might even create other problems but well, having utf8 encoded data in a latin1 database is certainly not the best starting point :-/ (been here, done that, and I can feel your pain).

Answer 2

I finally figured it out (I don't know why I always find a solution a while after posting it to SO)

from django.db.backends.signals import connection_created

def connection_setup(**kwargs):
    conn = kwargs['connection']
    with conn.cursor() as cursor:
        cursor.execute("SET character_set_results = 'latin1'")
        cursor.close()

I've tried it before with Oracle's python-mysql-connector and it threw

RuntimeError: maximum recursion depth exceeded in comparison

but it works with MySQL-driver py3 branch. I guess it can be a bug in python-mysql-connector or Django which I'll report. Maybe this will help somebody.

Answer 3

with mysql connector python in the client.cfg that you are using as an option file, in place of the init-command option(which is ignored by the connector) use write, charset=latin1, this will work.

[client]
charset=latin1
# password, host, username