Django Encoding Issues with MySQL

Question

Okay, so I have a MySQL database set up. Most of the tables are latin1 and Django handles them fine. But, some of them are UTF-8 and Django does not handle them.

Here's a sample table (these tables are all from django-geonames):

DROP TABLE IF EXISTS `geoname`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `geoname` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `ascii_name` varchar(200) NOT NULL,
  `latitude` decimal(20,17) NOT NULL,
  `longitude` decimal(20,17) NOT NULL,
  `point` point default NULL,
  `fclass` varchar(1) NOT NULL,
  `fcode` varchar(7) NOT NULL,
  `country_id` varchar(2) NOT NULL,
  `cc2` varchar(60) NOT NULL,
  `admin1_id` int(11) default NULL,
  `admin2_id` int(11) default NULL,
  `admin3_id` int(11) default NULL,
  `admin4_id` int(11) default NULL,
  `population` int(11) NOT NULL,
  `elevation` int(11) NOT NULL,
  `gtopo30` int(11) NOT NULL,
  `timezone_id` int(11) default NULL,
  `moddate` date NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `country_id_refs_iso_alpha2_e2614807` (`country_id`),
  KEY `admin1_id_refs_id_a28cd057` (`admin1_id`),
  KEY `admin2_id_refs_id_4f9a0f7e` (`admin2_id`),
  KEY `admin3_id_refs_id_f8a5e181` (`admin3_id`),
  KEY `admin4_id_refs_id_9cc00ec8` (`admin4_id`),
  KEY `fcode_refs_code_977fe2ec` (`fcode`),
  KEY `timezone_id_refs_id_5b46c585` (`timezone_id`),
  KEY `geoname_52094d6e` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

Now, if I try to get data from the table directly using MySQLdb and a cursor, I get the text with the proper encoding:

>>> import MySQLdb
>>> from django.conf import settings
>>> 
>>> conn = MySQLdb.connect (host = "localhost",
... user = settings.DATABASES['default']['USER'],
... passwd = settings.DATABASES['default']['PASSWORD'],
... db = settings.DATABASES['default']['NAME'])
>>> cursor = conn.cursor ()
>>> cursor.execute("select name from geoname where name like 'Uni%Hidalgo'");
1L
>>> g = cursor.fetchone()
>>> g[0]
'Uni\xc3\xb3n Hidalgo'
>>> print g[0]
Unión Hidalgo

However, if I try to use the Geoname model (which is actually a django.contrib.gis.db.models.Model), it fails:

>>> from geonames.models import Geoname
>>> g = Geoname.objects.get(name__istartswith='Uni',name__icontains='Hidalgo')
>>> g.name
u'Uni\xc3\xb3n Hidalgo'
>>> print g.name
Unión Hidalgo

There's pretty clearly an encoding error here. In both cases the database is returning 'Uni\xc3\xb3n Hidalgo' but Django is (incorrectly?) translating the '\xc3\xb3n' to ó.

What can I do to fix this?

Update

Okay, so this is weird:

>>> c = unicode('Uni\xc3\xb3n Hidalgo','utf-8')
>>> c
u'Uni\xf3n Hidalgo'
>>> print c
Unión Hidalgo

If I force python to encode the string into Unicode from utf-8, it works. However, this recreates the mistake:

>>> c = unicode('Unión Hidalgo','latin1')
>>> c
u'Uni\xc3\xb3n Hidalgo'
>>> print c
Unión Hidalgo

So, my guess MySQL is sending utf-8 but telling Python it is latin1?

Answer 1

you could use like this

>>> print g.name.encode('latin1')
Unión Hidalgo

Answer 2

Looks like the problem was in MySQL after all. I dropped the tables, recreated them with charset and collate set to UTF, and re-imported all of the data.

It's working now.