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
-
- Package: pymssql-0.8.0.win32-py2.5.exe, available from pymssql – Simple MSSQL Python extension module
- Textual data is passed as strings, rather than as Unicode.
- Parameters in SQL queries are marked by ‘%(name)s’.
- 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.
