Wednesday 2 September 2020

Connecting to MS SQL server with pyodbc


Small project I'm working on at work requires connection to MS SQL server. For the task I'm using python3 with pyodbc library and I got stuck for a while trying to connect. looks like it has to do with the ODBC Driver 17 for SQL Server. After some time searching for solution, I found FreeTDS.

First install FreeTDS on your Linux: 
apt install tdsodbc freetds-bin
Next configure FreeTDS by adding this to /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
Finally activate 
odbcinst -i -d -f /etc/odbcinst.ini
And now I can run pyodbc in my code 
import pyodbc
connection = pyodbc.connect('DRIVER={FreeTDS};'
	'Server='+dbhost+';'
	'Database='+dbname+';'
	'UID='+dbuser+';'
	'PWD='+dbpass+';'
	'TDS_Version=8.0;'
	'Port=1433;')
    
cursor = connection.cursor()
cursor.execute("SELECT * FROM tablename; ")

for row in cursor:
	print(row)
Labels: