Search code examples
pythonunicodefreetdspymssql

UnicodeDecodeError when using pymssql with freetds

I want to get a field in SQL server 2008 from python 2.6. Here is my freeTDS .conf file:

[ARGSERVER03]
    host = 192.168.1.3
    port = 1433
    tds version = 7.0

Here is the code:

conn = pymssql.connect(host='192.168.1.3', user='****', password='****', database='TrafficMonitor', as_dict=True, charset='UTF-8')
i = 0
cur.execute('SELECT * FROM dbo.tblTrafficCounterData')
while i < 10:
    car = cur.fetchone_asdict()
    if car is None:
        break
    c = car['Class']
    print c
    i = i + 1

But it gives:

UnicodeDecodeError: 'utf8' codec can't decode byte 0xd3 in position 0: invalid continuation byte

The Unicode field is in Persian. The trace back is for line car = cur.fetchone_asdict()

[Edit]

I have checked for database collation in database properties from sql server management studio and it is:

Arabic_CI_AS

But when I use that in charset it gives:

LookupError: unknown encoding: Arabic_CI_AS
Solution

  • Are you extremely sure that SQL Server is using UTF-8 (indicated by your charset='UTF-8')? Typically most SQL Server instances I have run into use a Microsoft encoding (not UTF-8), such as cp1252 (in the U.S.).

    A few things that may help you discover the correct encoding:

    SELECT DATABASEPROPERTYEX('dbname', 'Collation') SQLCollation