Using Python to access SQL Server

Install the required packages UnixODBC and FreeTDS packages

yum install -y unixODBC unixODBC-devel freetds

Configure unixODBC

Add the content below to the file /etc/odbcinst.ini

[FreeTDS]
Description=ODBC for SQL Server
Driver=/usr/lib/libtdsodbc.so
Setup=/usr/lib/libtdsS.so
FileUsage=1

Configure FreeTDS

Add the content below to the file /etc/freetds.conf

tds version = 8
client charset = UTF-8

Install pypyodbc to Python handle ODBC connections

pypyodc is a pure python ODBC interface. For more information access https://code.google.com/p/pypyodbc/ I’m using pip to install it.

0: pip install -U pypyodbc
Downloading/unpacking pypyodbc
  Downloading pypyodbc-1.3.3.zip
  Running setup.py egg_info for package pypyodbc
    
Downloading/unpacking distribute from https://pypi.python.org/packages/source/d/distribute/distribute-0.7.3.zip#md5=c6c59594a7b180af57af8a0cc0cf5b4a (from pypyodbc)
  Downloading distribute-0.7.3.zip (145kB): 145kB downloaded
  Running setup.py egg_info for package distribute
    
Downloading/unpacking setuptools>=0.7 (from distribute->pypyodbc)
  Downloading setuptools-5.4.2.tar.gz (1.0MB): 1.0MB downloaded
  Running setup.py egg_info for package setuptools
    
Installing collected packages: pypyodbc, setuptools, distribute
  Running setup.py install for pypyodbc
    
  Found existing installation: distribute 0.6.28
    Uninstalling distribute:
      Successfully uninstalled distribute
  Running setup.py install for setuptools
    
    Installing easy_install script to /opt/rh/python27/root/usr/bin
    Installing easy_install-2.7 script to /opt/rh/python27/root/usr/bin
  Found existing installation: distribute 0.6.28
    Can't uninstall 'distribute'. No files were found to uninstall.
  Running setup.py install for distribute
    
Successfully installed pypyodbc setuptools distribute
Cleaning up...

A python script to test the connection

Lets create a simple script that connect to a database and run a query.

Create the file test.py

import pypyodbc
query = 'SELECT name Name_Database,state_desc Status_Database FROM sys.databases'
db = pypyodbc.connect('Driver=FreeTDS;Server=10.1.1.1;port=1433;uid=user;pwd=password;database=datalog')
cur = db.cursor()
print cur.execute(query).fetchone()[0]


# And lets run it
0: python /tmp/test.py
master

Now we can connect to SQL Server with Python, come in hand to me since I have to monitor a bunch of SQL Server databases. Keep in mind that these instructions I used CentOS 6.5 64bits.