Monthly Archives: July 2016

Python 3.5 connects to MSSQL via SQLAlchemy

We may need to connect to DB for some handy tasks, like simulating response, DB house keeping and some other routine tasks.

In Python, there are several ways to connect to DB. An well known approach is using ORM, similar to JPA in Java.

My task is to connect Python to MSSQL, the technology stack is as followed.

– Python
– SQLAlchemy
– UnixODBC
– tdsodbc
– FreeTDS

First of all, we need to install all the relevant linux library via apt-get

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

And then install the following packages via pip3

pip3 install sqlalchemy
pip3 install pyodbc

After that, we have to configure the TDS driver, modify /etc/freetds/freetds.conf , add the following section

        host =
        port = 1433
        tds version = 8.0
        client charset = UTF-8

And then configure the FreeTDS driver in ODBC driver, /etc/odbcinst.ini

Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/
Setup = /usr/lib/x86_64-linux-gnu/odbc/
FileUsage = 1
CPTimeout =
CPResuse  =
client charset = utf-8

Finally, we need to configure the ODBC instance in /etc/odbc.ini

Description = "test"
Driver = FreeTDS
Servername = MSSQL
Port = 1433
Database = my_mssql_db
Trace = No

Create a new python script file to test the connectivity

import sys
import sqlalchemy

def main(argv):
    eng = sqlalchemy.create_engine("mssql+pyodbc://my_mssql_account:hello123@MSSQL")
    with eng.connect() as con:
        rs = con.execute('''
            select * from xxxx
        data = rs.fetchone()

if __name__ == "__main__":