pyodbc 不能正确处理 unicode 数据 [英] pyodbc doesn't correctly deal with unicode data
问题描述
我确实成功地将 MySQL 数据库与 pyodbc 连接起来,它与 ascii 编码的数据配合良好,但是当我打印使用 unicode(utf8) 编码的数据时,它引发了错误:
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'
我在 pyodbc github wiki 中找到了 有关 unicode 的说明
I found instructions about unicode in pyodbc github wiki
这些数据库倾向于使用单一编码并且不区分SQL_CHAR"和SQL_WCHAR"之间.因此,您必须配置它们将 Unicode 数据编码为 UTF-8 并解码两种 C 缓冲区类型使用 UTF-8.
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')
如果您使用的是 MySQL,您可以将字符集添加到连接字符串,但我不确定这是否有必要.
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()
测试代码:
from ServerDBDAO import ServerDBDAO
dbdao = ServerDBDAO()
row_employee = cursor.execute('select id, name, email from Employee;').fetchone()
print(row_employee.name)
推荐答案
我遇到了同样的问题.除了使用这些:
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')
这篇关于pyodbc 不能正确处理 unicode 数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!