Using FreeTDS to connect to MSSQL

In today’s post, I’ll outline the steps required to connect to a Microsoft Sql Server database from within an Ubuntu Linux environment using FreeTDS and ODBC.

Get the software

Using apt-get, we can satisfy all of the system-level requirements (libraries):

sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc

FreeTDS now needs to be defined as a driver in the /etc/odbcinst.ini file.

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/odbc/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so

Hitting the can

Now that we’ve got a driver up and running, we can use a library like sqlalchemy to run some queries. Before we can do that though, we need to install python’s odbc bindings pyodbc.

pip install pyodbc sqlalchemy

We can now start running some queries.

import urllib
import sqlalchemy as sa

cstr = urllib.quote_plus('DRIVER=FreeTDS;SERVER=host;PORT=1433;DATABASE=db;UID=user;PWD=password;TDS_Version=8.0;')

engine = sa.create_engine('mssql+pyodbc:///?odbc_connect=' + cstr)
    
for row in engine.execute('SELECT 1 AS Test;'):
    print row.Test