Connect to MSSQL from Python with ODBC and FreeTDS

This is another of those posts that I wrote because I always forget how to do things…

This time, I don't want to forget how to connect from Python to MSSQL server anymore. In order to do it, you need the following system packages installed:

$> sudo apt-get install unixodbc-dev tdsodbc sqsh

sqsh installs all the required freetds-related libraries; tdsodbc is the FreeTDS driver for ODBC and unixodbc-dev is needed to install pyodbc.

Then install pyodbc using pip or whatever else you like:

$> pip install pyodbc

Finally, the tricky part. You need 3 config files:

• /etc/freetds/freetds.conf: this stores the configuration for the endpoints to MSSQL servers
• /etc/odbcinst.ini: this defines the driver that ODBC uses. In fact, it mentiones the FreeTDS shared libraries installed with the system package tdsodbc. Edit to match your system.
• $HOME/.odbc.ini: finally, this file defines the database, username and password to connect to and the driver to use.

Files are available on gist.

Now you can connect using pyodbc using something like this:

CONNECTION = pyodbc.connect(
    'DRIVER=FreeTDS;'
    'SERVER=mssql-host;'
    'PORT=1433;'
    'DATABASE=dbname;'
    'UID=username;'
    'PWD=password;'
    'CHARSET=UTF-8;'
    'TDS_Version=8.0')