Can not get correct result when query with pyodbc

Question

I have a ms sql server, and I need to access its database in ubuntu pc. so I use freetds/pyodbc to query data.

OS: ubuntu 14.04
pyodbc: pyodbc-3.0.10

When ms sql server has a special character (ß), I can not get correct result.

cnxn = pyodbc.connect(
        'DRIVER=FreeTDS;'
        'SERVER=10.1.1.1;'
        'PORT=1433;'
        'DATABASE=db;'
        'UID=sa;'
        'PWD=111;'
        'CHARSET=UTF-8;'
        'unicode_results=True'
        'TDS_Version=8.0')
cursor.execute("select * from test")
for row in cursor.fetchall():
    print row[0]

row[0] will contain character ß, but when I use "".join("{:02x}".format(ord(c)) for c in row[0]).
I found that row is use 0xdf for ß, from http://lwp.interglacial.com/appf_01.htm, 0xdf is raw encoding, as UTF8, it should be 0xC3,0x9F.

Why pyodbc do not return UTF8 encoding data, and how can I decode it in python 2.7, Thanks~

SQL Server may not have a `charset` property. Try encoding the string in Python: `row.encode('utf-8')`. And see this related [SO post](http://stackoverflow.com/questions/947077/using-pyodbc-on-linux-to-insert-unicode-or-utf-8-chars-in-a-nvarchar-mssql-field). — Parfait, Dec 15 '15 at 15:07

score 1 · Accepted Answer · answered Dec 15 '15 at 12:24

A few things to check:

Make sure the column you're selecting from on SQL Server is NVARCHAR, NCHAR or NTEXT. (it would seem to be from your example)
TDS_VERSION 8.0 doesn't exist; FreeTDS falls back to 7.1 when 8.0 is provided. TDS Version 7.1 doesn't support NVARCHAR or NTEXT, as it was introduced in SQL Server 2008. Use TDS_VERSION 7.2 or 7.3.
- See https://msdn.microsoft.com/en-us/library/ms186939.aspx
- See http://www.freetds.org/userguide/choosingtdsprotocol.htm
Use the command tsql -C to see what FreeTDS version you're using. If version 0.95, you can use 7.3, if 0.91, use 7.2.

You'll also have to handle Unicode in Python 2.7 yourself. Python 3.4 or 3.5 would make your life a lot easier when working with Unicode!

Can not get correct result when query with pyodbc

1 Answers1