Choosing a Python module for accessing Microsoft SQL Server Unicode data

One day I found myself in need of Python code, which retrieves Unicode data from Microsoft SQL Server tables. The code needs to run on a PC with MS-Windows XP.

The dbi and odbc modules, which I used in the past, failed miserably in this task, by forcing the Unicode data to be converted into string data, using the ascii encoder.

So, I had to look for other Python modules. My findings from evaluating the relevant Python modules are summarized below.

dbi,odbc from pywin32
  • Package: pywin32-210.win32-py2.5.exe, available from Python for Windows Extensions.
  • Textual data is passed as strings, rather than as Unicode.
  • Parameters in SQL queries are marked by ‘?’.
  • Dates/times are retrieved as instances of the dbi.dbiDate class (essentially, a wrapped long int).
win32com
I was not successful in using the win32com based code, which worked for
Arik Baratz. According to him, this code uses the Microsoft ActiveX Data Objects 2.8 Library. It requires the modified version 209.1 of pywin32, which comes with version 2.5.1.1 of the ActiveState Python distribution. This modified version adds to the win32com class an extra member – client.
You need to add the following line sometime after the import win32com:

win32com.client.gencache.EnsureModule('{DA9159C1-C9D5-4223-81AC-2B1080701D12}',0,1,0)

To actually start working, use win32com.client.Dispatch() to establish a connection to the SQL Server.

pymssql
pyodbc
  • Package: pyodbc-2.0.39.win32-py2.5.exe, available from pyodbc – A Python DB API module for ODBC
  • Textual data is passed as Unicode.
  • Parameters in SQL queries are marked by ‘?’.
  • Dates/times are retrieved as instances of the datetime.datetime class.

The Python module chosen is pyodbc.

Author: Omer Zak

I am deaf since birth. I played with big computers which eat punched cards and spew out printouts since age 12. Ever since they became available, I work and play with desktop size computers which eat keyboard keypresses and spew out display pixels. Among other things, I developed software which helped the deaf in Israel use the telephone network, by means of home computers equipped with modems. Several years later, I developed Hebrew localizations for some cellular phones, which helped the deaf in Israel utilize the cellular phone networks. I am interested in entrepreneurship, Science Fiction and making the world more accessible to people with disabilities.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: