I am using pyodbc in a python script under linux (debian 8) to connect to a MS-SQL Database. Here is my configuration for the database access.

I can’t remember exactly which packages from the packagemanger I’ve installed 🙁 but maybe I have choosen the following: freetds-bin, freetds-common, freetds-dev, odbcinst


Configuration

/etc/odbc.ini

[mcc]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = mcc
Port = 1433
Database = mcc
Charset = UTF-8

/etc/odbcinst.ini

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

/etc/freetds/freetds.conf

[mcc]
 host = server.test.com
 port = 1433
 tds version = 7.1
 client charset = UTF-8

The pyodbc python library was installed with:

 pip3 install pyodbc

Example Python Script

import pyodbc


dsn = 'mcc'
user = 'username'
password = 'password'
database = 'mcc'
con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s' % (dsn, user, password, database)

with pyodbc.connect(con_string) as cnxn:
 with cnxn.cursor() as c:
   for i,x in enumerate(c.execute("exec ....").fetchall()):
     print(i)
     print(x)

Problems

When I started with the configuration I copied it from one of our CentOS servers. In CentOS the freetds.conf file is directly in /etc/ but in Debian this file has to be in /etc/freetds/.

Here are the error messages I received with the wrong location of freetds.conf (see red text).

$ tsql -S mcc -U username -P password
Error 20012 (severity 2):
 Server name not found in configuration files.
locale is "en_US.utf8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20013 (severity 2):
 Unknown host machine name.
There was a problem connecting to the server

If the freetds.conf file is in the right place:

$ tsql -S mcc -U username -P password
locale is "en_US.utf8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>