[Tutor] pymssql and encoding

[Emmanuel Ruellan]

Hi tutors,

I'm trying to fetch data from an MS SQL Server database with pymssql, but
non-ascii results get garbled.

>>> import pymssql
>>> conn = pymssql.connect(user='sa', password=myPassword, host=server,
database=targetDatabase, as_dict=True)
>>> curs = conn.cursor()
>>> curs.execute("select CardName from OCRD where CardCode = 'C00056'")
>>> resultAsDict = curs.fetchall()[0]
>>> customerName = resultAsDict['CardName']
>>> print customerName
ImmobiliŠre (whatever)
>>> customerName
'Immobili\x8are (whatever)'

There should be a small E with a grave accent (è) instead of the capital S
with a caron (Š) I'm getting.

I've tried applying various encodings, but to no avail:

>>> print customerName.decode('latin-1')
Immobili?re (whatever)
>>> print customerName.decode('utf-8')
Traceback (most recent call last):
  File "<pyshell#84>", line 1, in <module>
    print customerName.decode('utf-8')
  File "D:\Python26\lib\encodings\utf_8.py", line 16, in decode
    return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode byte 0x8a in position 8:
unexpected code byte
>>> print customerName.decode('utf-7')
Traceback (most recent call last):
  File "<pyshell#85>", line 1, in <module>
    print customerName.decode('utf-7')
  File "D:\Python26\lib\encodings\utf_7.py", line 12, in decode
    return codecs.utf_7_decode(input, errors, True)
UnicodeDecodeError: 'utf7' codec can't decode byte 0x8a in position 8:
unexpected special character


When executed from MS SQL Server's Management Studio, the same query returns
"Immobilière (whatever)", with an 'è', as it should.

The field in question is of type nvarchar, with collation
SQL_Latin1_General_CP850_CI_AS.

Do you have an idea what the actual encoding of the string might be?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20101006/0ae8940a/attachment.html>