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.
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:[email protected]") with eng.connect() as con: rs = con.execute(''' select * from xxxx ''') data = rs.fetchone() print(data) if __name__ == "__main__": main(sys.argv)