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
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
FreeTDSshared 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
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')