pypyODBC on Linux with FreeTDS

Tested and working on CentOS 7 x64 with a Windows Server 2003 serving SQL Server 2000.

First, install CentOS packages:

yum install freetds tdsodbc unixODBC

Edit /etc/odbcinst.ini and add the following section:

# SQL Server ODBC
[FreeTDS]
Description=FreeTDS MSSQL Driver
Driver = /usr/lib64/libtdsodbc.so.0

* Note that the driver path may be different depending on the distribution version or architecture.


Edit /etc/freetds.conf and add the folling section:

[DATASOURCENAME]
        host = HOSTNAME or IP
        port = 1433
        tds version = 7.0

And modify the following section by adding this line:

 [global]
        client charset = UTF-8

* Note that the section name, is this case DATASOURCENAME, should be any name that you will call from your program and the tds version may be different depending on the SQL Server version that you want to connect with. And don't forget to set host and port according to your server instances.


You can test the conection with the following command line tool:

tsql -S DATASOURCENAME -U 'DOMAIN\user' -P password

* In this case above, I used domain authentication, can be different depending on SQL Server setup.


And here is a simple Python program to test the connection using pypyODBC:

import pypyodbc

c = pypyodbc.connect(ur"DRIVER={FreeTDS};Servername=DATASOURCENAME;DATABASE=DATABASENAME;UID=DOMAIN\user;PWD=password")

cursor = c.cursor()

cursor.execute( 'SELECT row FROM table' )

for row in cursor:
    print row[0],

c.close()

References:

• https://code.google.com/p/pypyodbc/wiki/Linux_ODBC_in_3_steps
• http://www.freetds.org/userguide/choosingtdsprotocol.htm
• https://code.google.com/p/pypyodbc/