PostgreSQL ANSI,Python SQL, utf-8' codec can't decode byte 0xa0

Question

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.

I get error UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa0 in position 1: invalid start byte

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:

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)

Also tried:

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

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

Also tried:

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

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

Also tried:

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' codec can't decode byte 0xd5 in position 1: invalid continuation byte

Also tried:

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' codec can't decode byte 0xd5 in position 1: invalid continuation byte

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

[my_dsn]
Driver=C:\Program Files (x86)\psqlODBC0903円\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

Error msg:

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

can anyone help me ?

• Bram Vanroy: Possible duplicate of 'utf-8' codec can't decode byte 0xa0 in position 4276: invalid start byte
• excelguy: i looked at this, not sure wehre to put in the encoding. I cant put it in conn.execute as execute wont take this.
• TastySlowCooker: See also stackoverflow.com/questions/40712310/…
• Gord Thompson: Please edit your question to show the complete stack trace.
• Tarun Lalwani: Can you try with conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-16') and rest on utf-8

Answer 1

When using PostgreSQL's Unicode driver you need to call setencoding and setdecoding as explained here.

# 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 PostgreSQL's "ANSI" driver then you may still need to call those methods to ensure that the correct single-byte character set (a.k.a. "code page", e.g., windows-1252) is used for SQL_CHAR.

• excelguy: thank you, in this case I guess conn would replace cncx?
• Gord Thompson: Yes, it's the name of the Connection object created by pyodbc.connect(...)
• excelguy: Seems like i am getting the same error, check my updated code.
• Gord Thompson: @excelguy - What do you see if you print(conn.getinfo(pyodbc.SQL_DRIVER_NAME) + ' version ' + conn.getinfo(pyodbc.SQL_DRIVER_VER)) ...?
• excelguy: PSQLODBC.DLL version 09.03.0400

Answer 2

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)