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
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 =
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
isql -v DB_SOURCE
If you can successfully log in and run some simple queries, you know unixODBC is working properly.
4. Try to connect with Python
import pyodbc
dsn = 'DB_SOURCE'
user = 'username'
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
