Django Encoding Issues with MySQL
- by Jordan Reiter
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?
