CODEWITHSUNDEEP
pythonmysqldjangocharacter-encodinglatin1
giaosudau
giaosudau

Reputation: 2251

How to set character_set_results latin1 mysql in Django?

I don't know how to connect to mysql db when db use latin1 encode. What I am try and follow document and I am using mysqlclient==1.3.5 here is my setting for database connection.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'test_api',
        'USER': 'admin',
        'PASSWORD': '123456',
        'HOST': '192.168.59.103',
        'PORT': '3306',
        'OPTIONS': {
            'charset': 'latin1', 'use_unicode': False

        },
    }
}

37 Phẩm Bồ tát hạnh
Am mây ngủ
An cứ kiết hạ 
An lạc từ tâm
An lạc từng bước chân
Ãnh Sáng Từ Bi

But it not success so next I try to use pymysql to write a simple script below and it work fine.

# -*- coding: utf-8 -*-
import pymysql
connection = pymysql.connect(host='192.168.59.103', port=3306, user='admin', passwd='123456', db='test_api', use_unicode=False)

customers = connection.cursor(pymysql.cursors.DictCursor)
customers.execute('SET character_set_results=\'latin1\'')
customers.execute('SELECT *  FROM bz_media_album ORDER BY name_vn')
for customer in customers:
    print customer['name_vn']

37 Phẩm Bồ tát hạnh
Am mây ngủ
An cứ kiết hạ 
An lạc từ tâm
An lạc từng bước chân
Ánh Sáng Từ Bi

I also find https://stackoverflow.com/a/25685134/523075 But don't know how to implement it.

Because all my table use latin1 and my question is how to character_set_results for django cursor because I use django and tastypie to develop mobile api. I also has model and resource.

Upvotes: 0

Views: 1113

Answers (2)

Jonathan
Jonathan

Reputation: 1089

I solved a similar problem using the following signal:

# apps.my_app.signals.py
from django.db.backends.signals import connection_created
from django.dispatch import receiver


@receiver(connection_created)
def set_character_set_results(*args, connection, **kwargs):
    with connection.cursor() as cursor:
        cursor.execute('SET CHARACTER_SET_RESULTS = \'latin1\'')

This then needs wiring up in the usual way.

This should work with Mysql 4.1 or higher.

Our use case was reading from a mysql database of which we also didn't want to alter the encoding as it was being read and written to from a legacy system. We also had another (postgres) database, so the actual code was more like:

from django.db.backends.signals import connection_created
from django.dispatch import receiver


@receiver(connection_created)
def set_character_set_results(*args, connection, **kwargs):
    if connection.alias == 'legacy':
        with connection.cursor() as cursor:
            cursor.execute('SET CHARACTER_SET_RESULTS = \'latin1\'')

Upvotes: 1

Rick James
Rick James

Reputation: 142298

Was the first line supposed to be Phẩm Bồ tát hạnh? If so, the probably this is what happened:

  • You had utf8-encoded bytes in the client
  • You executed SET NAMES latin1 (or the equivalent in python/django)
  • The column in the table was declared CHARACTER SET latin1

Everything needs to be ut8. But to fix the data, you need the 2-step ALTER, which effectively says to do

  • ALTER TABLE Tbl MODIFY COLUMN col VARBINARY(...) ...;
  • ALTER TABLE Tbl MODIFY COLUMN col VARCHAR(...) ... CHARACTER SET utf8 ...;

where the lengths are big enough and the other "..." have whatever else (NOT NULL, etc) was already on the column.

Recommend you test this outside production.

Edit

You have garbage in the database. Any attempt to shortcut the fixing of the table(s) will probably lead to a worse mess.

This might make the output "look" right:

CONVERT(BINARY(column_name) USING utf8)

Upvotes: 1

Related Questions