Setup Python to work with MS SQL Server in CentOS 6

I spent quite sometime and today finally I was able to use Python scripts to connect to database and run queries using pandas. Here are the steps:

BTW, these instructions are for CentOS 6.4, Python-2.7.6 with pip already installed.

1. Install the required packages.

yum install gcc gcc-c++ python-devel freetds unixODBC unixODBC-devel

Then, you can go ahead and install pyodbc with:

pip install pyodbc

2. Configure FreeTDS

Edit the file /etc/freetds.conf or ~/.freetds.conf if you do not have root privilege.

For each database you will be working with, add the section:

[DB_SERVER]

host = URL

port = 1433

tds version = 7.0

Then, copy the configure file to your home folder:

cp /etc/freetds.conf ~/.freetds.conf

To see if everything is working as of now, try:
tsql -S DB_SERVER -U username -P password

If you can successfully log in and run some simple queries, you know FreeTDS is working properly.

3. Configure unixODBC to work with FreeTDS

Add the following section to /etc/odbcinst.ini:

[FreeTDS]

Description     = MS SQL database access with Free TDS

Driver          = /usr/local/lib/libtdsodbc.so

Setup           = /usr/lib64/libtdsS.so
CPTimeout = 

CPReuse = 

FileUsage = 1

The path might be different for different machines.

Then, for each database you want to work with, add the following section to /etc/odbc.ini or ~/.odbc.ini if you do not have root privilege:

[DB_SOURCE]

Driver = FreeTDS

Description = ODBC connection via FreeTDS

Trace = No

Servername = DB_SERVER

Database = DB_NAME

To see if the configuration is working, try:
isql -v DB_SOURCE  username password

If you can successfully log in and run some simple queries, you know unixODBC is working properly.

Note: to make sure ODBC look for your local config file, do this:
export ODBCINI=/HOMEDIR/.odbc.ini

4. Try to connect with Python

Finally, in Python, your script should look like this:

import pyodbc

dsn = 'DB_SOURCE'
user = 'username'
password = 'password'
database = 'DB_NAME'

con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)

Now you can embed your SQL queries in Python XD