Microsoft SQL server (MSSQL)¶
Microsoft SQL server (MSSQL) is part of turbodbc’s integration databases. That means that each commit in turbodbc’s repository is automatically tested against MSSQL to ensure compatibility. Here are the recommended settings for connecting to a Microsoft SQL database via ODBC using the turbodbc module for Python.
Recommended odbcinst.ini (Linux)¶
On Linux, you have the choice between two popular drivers.
Official Microsoft ODBC driver¶
Microsoft offers an official ODBC driver for selected modern Linux distributions.
[MSSQL Driver]
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.4.0
FreeTDS¶
FreeTDS is an open source ODBC driver that supports MSSQL. It is stable, has been around for well over decade and is actively maintained. However, it is not officially supported by Microsoft.
[FreeTDS Driver]
Driver = /usr/local/lib/libtdsodbc.so
Recommended odbcinst.ini (OSX)¶
FreeTDS seems to be the only available driver for OSX that can connect to MSSQL databases.
[FreeTDS Driver]
Driver = /usr/local/lib/libtdsodbc.so
Recommended data source configuration¶
Official Microsoft ODBC driver (Windows)¶
Put these values in your registry under the given key. Be sure to prefer the latest ODBC driver over any driver that may come bundled with your Windows version.
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MSSQL]
"Driver"="C:\\Windows\\system32\\msodbcsql13.dll"
"Server"="<host>"
"Database"="<database>"
Official Microsoft ODBC driver (Linux)¶
[MSSQL]
Driver = MSSQL Driver
Server = <host>,<port>
Database = <database>
Note
You cannot specify credentials for MSSQL databases in odbc.ini
.
FreeTDS data sources¶
[MSSQL]
Driver = FreeTDS Driver
Server = <host>
Port = <post>
Database = <database>
Note
You cannot specify credentials for MSSQL databases in odbc.ini
.
Recommended turbodbc configuration¶
The default turbodbc connection options have issues with Unicode strings
on MSSQL. Please make sure to set the prefer_unicode
option.
>>> from turbodbc import connect, make_options
>>> options = make_options(prefer_unicode=True)
>>> connect(dsn="MSSQL", turbodbc_options=options)
Warning
If you forget to set prefer_unicode
, you may get anything from
garbled up characters (e.g., u'\xe2\x99\xa5'
instead of the unicode
character u'\u2665'
) or even ODBC error messages such as
[FreeTDS][SQL Server]Invalid cursor state
.