本文介绍了PostgreSQL ANSI,Python SQL,utf-8'编解码器无法解码字节0xa0的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试在python中运行sql查询。在python 2中,这曾经可以工作,但是现在我在使用python 3,这不再起作用。

I am trying to run a sql query in python. In python 2 this used to work but now that I am using python 3 this no longer is working.

我收到错误 UnicodeDecodeError:'utf- 8'编解码器无法解码位置1的字节0xa0:无效的起始字节

更新,中间添加了3行尝试在此处使用'windows-1252'。相同的错误:

Update, added in the 3 lines in the middle,also tried using 'windows-1252' here. same error:

conn_str = 'DSN=PostgreSQL30'
conn = pyodbc.connect('DSN=STACK_PROD')


###newly added
    conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
    conn.setencoding(encoding='utf-8')

sql = "select * from stackoverflow where p_date = " + business_date
print("Query: " + sql)
crsr = conn.execute(sql)

回溯:

Traceback (most recent call last):

  File "<ipython-input-2-b6db3f5e859e>", line 1, in <module>
    runfile('//stack/overflow/create_extract_db_new.py', wdir='//stack/overflow')

  File "C:\Users\stack\AppData\Local\Continuum\anaconda3\anaconda3_32bit\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 827, in runfile
    execfile(filename, namespace)

  File "C:\Users\stack\AppData\Local\Continuum\anaconda3\anaconda3_32bit\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 110, in execfile
    exec(compile(f.read(), filename, 'exec'), namespace)

  File "//stack/overflow/create_extract_db_new.py", line 37, in <module>
    crsr = conn.execute(sql)

也尝试过:

conn.setdecoding(pyodbc.SQL_CHAR, encoding='windows-1252')
conn.setdecoding(pyodbc.SQL_WCHAR, encoding='windows-1252')
conn.setencoding(encoding='windows-1252')

UnicodeDecodeError:'utf-8'编解码器无法解码位置1的字节0xd5

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xd5 in position 1: invalid continuation byte

也尝试过:

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

UnicodeDecodeError:'utf-8'编解码器无法解码位置1的字节0xd5:无效的连续字节

也尝试过:

conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
conn.setencoding(encoding='utf-8')
conn.setdecoding(pyodbc.SQL_WMETADATA, encoding='windows-1252')

UnicodeDecodeError:'utf-8'编解码器无法解码位置1的字节0xd5:无效的连续字节

也尝试过:

conn.setdecoding(pyodbc.SQL_CHAR, encoding='windows-1252')
conn.setdecoding(pyodbc.SQL_WCHAR, encoding='windows-1252')
conn.setencoding(encoding='windows-1252')
conn.setdecoding(pyodbc.SQL_WMETADATA, encoding='windows-1252')

UnicodeDecodeError:'utf-8'编解码器无法解码位置1的字节0xd5连续字节

转储Dsn结果,省略我的用户名,uid,密码和服务器。

Dump Dsn Results, omitting my username, uid, password and server.:

[my_dsn]
Driver=C:\Program Files (x86)\psqlODBC\0903\bin\psqlodbc30a.dll
CommLog=0
Debug=0
Fetch=100
Optimizer=0
Ksqo=1
UniqueIndex=1
UseDeclareFetch=0
UnknownSizes=0
TextAsLongVarchar=1
UnknownsAsLongVarchar=0
BoolsAsChar=1
Parse=0
CancelAsFreeStmt=0
MaxVarcharSize=255
MaxLongVarcharSize=8190
ExtraSysTablePrefixes=dd_;
Description=my_dsn
Database=db_name
Port=9996
ReadOnly=0
ShowOidColumn=0
FakeOidIndex=0
RowVersioning=0
ShowSystemTables=0
Protocol=7.4
ConnSettings=
DisallowPremature=0
UpdatableCursors=1
LFConversion=1
TrueIsMinus1=0
BI=0
AB=0
ByteaAsLongVarBinary=0
UseServerSidePrepare=1
LowerCaseIdentifier=0
GssAuthUseGSS=0
SSLmode=disable
KeepaliveTime=-1
KeepaliveInterval=-1
PreferLibpq=-1
XaOpt=1

错误消息:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe4 in position 3: unexpected end of data

有人可以帮助我吗?

推荐答案

对我有用的是改为通过odbc将此行用于连接g,然后我进行了编码和解码。

What worked for me was using this line for connectiong via odbc instead, and I took out the encoding and decoding.

con = pyodbc.connect(r'DSN ='+'STACK_PROD',autocommit = True)

这篇关于PostgreSQL ANSI,Python SQL,utf-8'编解码器无法解码字节0xa0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-27 20:32