python - Python 3.5 执行 SQL 查询时出现 UnicodeDecodeError

Question

我使用 Python 3.5 + pyodbc(在 Windows 10 上)在服务器上应用一些 SQL 查询，一切都运行良好，如下所示:

In [1]: import pyodbc
   ...:
In [2]: server = 'tcp:192.168.100.76'
   ...: database = 'database'
   ...: username = 'username'
   ...: password = 'password'
   ...:
In [3]: cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+use
   ...: rname+';PWD='+ password)
   ...: cursor = cnxn.cursor()

In [29]: sqlQuery = "SELECT TOP 50 * " + \
    ...: "FROM dbo.DS10_CIBBASE " + \
    ...: "LEFT JOIN dbo.DS10_CIBCITY AS O_CITY ON dbo.DS10_CIBBASE.O_CITY=O_CITY.CITYCODE " + \
    ...: "LEFT JOIN dbo.DS10_CIBCITY AS B_CITY ON dbo.DS10_CIBBASE.B_CITY=B_CITY.CITYCODE " + \
    ...: "LEFT JOIN dbo.DS10_CIBMETH AS METHOD1 ON dbo.DS10_CIBBASE.METHOD1=METHOD1.METHCODE " + \
    ...: "LEFT JOIN dbo.DS10_CIBMETH AS METHOD2 ON dbo.DS10_CIBBASE.METHOD2=METHOD2.METHCODE " + \
    ...: "LEFT JOIN dbo.DS10_CIBMETH AS METHOD3 ON dbo.DS10_CIBBASE.METHOD3=METHOD3.METHCODE " + \
    ...: "LEFT JOIN dbo.DS10_CIBPERS ON dbo.DS10_CIBBASE.BASENO=dbo.DS10_CIBPERS.BASENO " + \
    ...: "LEFT JOIN dbo.DS10_CIBFEAT ON dbo.DS10_CIBPERS.FEATURE=dbo.DS10_CIBFEAT.FEATCODE " + \
    ...: "LEFT JOIN dbo.DS10_CIBOCCU ON dbo.DS10_CIBPERS.OCCUPATION= dbo.DS10_CIBOCCU.OCCUCODE " + \
    ...: "LEFT JOIN dbo.DS10_CIBEDUC ON dbo.DS10_CIBPERS.EDU=dbo.DS10_CIBEDUC.EDUCCODE " + \
    ...: "LEFT JOIN dbo.DS10_CIBCITY AS L_CITY ON dbo.DS10_CIBPERS.CITY=L_CITY.CITYCODE ;"

In [30]: cursor.execute(sqlQuery)
Out[30]: <pyodbc.Cursor at 0x262625e5750>

到目前为止一切顺利，然后我稍微更改了查询:

In [31]: sqlQuery0 = "SELECT TOP 50 * " + \
    ...: "FROM dbo.LAB1_EXP3_CARNO1 ; "

In [32]: cursor.execute(sqlQuery0)
---------------------------------------------------------------------------
UnicodeDecodeError                        Traceback (most recent call last)
<ipython-input-32-769696011e11> in <module>()
----> 1 cursor.execute(sqlQuery0)

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa8 in position 0: invalid start byte

我不明白为什么前面的查询没问题，而后面的查询却遇到了 UnicodeDecodeError，因为所有查询都是用 ASCII 字符编写的。那么我做错了什么，我该怎么办摆脱这个错误？

Answer 1

您得到此信息是因为您的数据不是 UTF-8 编码( \U00A8 是“间距分音符”，而 \xA8 在 Latin-1 中是相同的，但 \xA8 在 Latin- 中是 š 15.该错误不是因为查询包含非 ASCII 字符(显然没有)，而是因为结果返回非 UTF-8 编码的 8 位字符字符串。

对于 SQL Server，您可以使用 cast或convert更改 varchar导致错误进入 nvarchar 的列(UTF-16 Unicode)值。