CODEWITHSUNDEEP
pythonpyodbc
Jerry YY Rain
Jerry YY Rain

Reputation: 4382

Can not get correct result when query with pyodbc

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~

Upvotes: 1

Views: 689

Answers (1)

FlipperPA
FlipperPA

Reputation: 14311

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.
  • 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!

Upvotes: 1

Related Questions