pyodbc doesn't correctly deal with unicode data

Question

I did successfully connected MySQL database with pyodbc, and it works well with ascii encoded data, but when I print data encoded with unicode(utf8), it raised error:

UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-8: ordinal not in range(128)

So I checked the string in the row:

>>>row[3]
'\xe7\xae\xa1\xe7\x90\u2020\xe5\u2018\u02dc'

I found instructions about unicode in pyodbc github wiki

These databases tend to use a single encoding and do not differentiate between "SQL_CHAR" and "SQL_WCHAR". Therefore you must configure them to encode Unicode data as UTF-8 and to decode both C buffer types using UTF-8.

# Python 3.x
cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(encoding='utf-8')

If you are using MySQL, you can add the character set to the connection string, but I'm not sure if this is necessary.

# MySQL
cstring = 'DSN=mydsn;CharSet=utf8'
cnxn = pyodbc.connect(cstring)

I did as above, but nothing different. Flowing is my code

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import pyodbc
import configparser
class ServerDBDAO():
 def __init__(self):
 ''' Establish connection to SQL'''
 # Read config
 self.cf = configparser.ConfigParser()
 self.cf.read("./Config/server.ini")
 driver = self.cf.get('Database', 'Driver')
 server = self.cf.get('Database', 'Server')
 database = self.cf.get('Database', 'Database')
 uid = self.cf.get('Database', 'UID')
 pwd = self.cf.get('Database', 'PWD')
 # Connect database
 connString = 'DRIVER=%s;SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;CharSet=utf8'%(driver, server, database, uid, pwd)
 '''Successfully connected database with this
 self.conn = pyodbc.connect('DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so;SERVER=localhost;DATABASE=xxx;UID=root;PWD=xxxxxx'))
 '''
 self.conn = pyodbc.connect(connString,unicode_results=True)
 self.conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
 self.conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
 self.conn.setencoding(encoding='utf-8')
 self.cursor = self.conn.cursor()
 def __del__(self):
 self.conn.commit()
 self.conn.close()

test code:

from ServerDBDAO import ServerDBDAO
dbdao = ServerDBDAO()
row_employee = cursor.execute('select id, name, email from Employee;').fetchone()
print(row_employee.name)

• Gord Thompson: What version of MySQL Connector/ODBC are you trying to use? How did you install it? Your question mentions libmyodbc.so, but current versions of the MySQL ODBC driver actually come in two flavours: libmyodbc5a.so for "ANSI" and libmyodbc5w.so for Unicode. Are you sure that the version you are using supports Unicode?
• Rick James: Don't do any encoding/decoding in your code. Just let it all be UTF-8.
• J.Z: @GordThompson I simply installed odbc with 'apt-get install libmyodbc', it only provides this libmyodbc.so file. I will try libmyodbc5w.so and see how it works. Appreciate your help very much!
• J.Z: @RickJames I didn't use the encoding an decoding stuff in the beginning, it didn't work, so i added the code according to pyodbc wiki, it didn't work ether way. Thanks for replying anyway.
• J.Z: @GordThompson I installed MySQL Connector/ODBC 5.3 from mysql official sitelink. Tried libmyodbc5w.so, met another error of connection link, and solved it with a soft link, but it raised the UnicodeEncodeError like before.

Answer 1

I faced the same issue. In addition to using these:

cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(encoding='utf-8')

Adding this resolved the issue for me:

cnxn.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le')

• J.Z: That's interesting... I'v heard MySQL 'utf-8' encoding is not a real utf-8 standard encoding, it is a MySQL-invented encoding. Instead, 'utf8mb4' in MySQL is the real utf-8. Not sure if that is the root cause, I have abandoned the environment long ago, and probably not able to reproduce it.