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
– PyODBC
– UnixODBC
– tdsodbc
– FreeTDS
– MSSQL

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

[MSSQL]
        host = 10.168.10.160
        port = 1433
        tds version = 8.0
        client charset = UTF-8

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

[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout =
CPResuse  =
client charset = utf-8

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

[MSSQL]
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):
    print(sqlalchemy.__version__)
    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()
        print(data)

if __name__ == "__main__":
    main(sys.argv)

DONE!