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>