Connect to MSSQL using FreeTDS / ODBC in Python.

README.md

Goal: Connect to MSSQL using FreeTDS / ODBC in Python.

Host: Ubuntu 11.10 x86_64

Install:

sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc
pip install pyodbc sqlalchemy

In /etc/odbcinst.ini:

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/odbc/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so

hello_pyodbc.py

"Proof connection at pyodbc level."
# Test pyodbc connection. Result is 42.
# Note parameters in connection string, <PARAMETER>.

import pyodbc


conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=<IP_OR_HOSTNAME>;PORT=1433;DATABASE=<DATABASE_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;TDS_Version=8.0;')
cursor = conn.cursor()
for row in cursor.execute('select 6 * 7 as [Result];'):
    print row.Result

hello_sqlalchemy.py

"Proof connection at SQLAlchemy level, on top of pyodbc."
# Test SQLAlchemy connection. Result is 42.
# Note parameters in connection string, <PARAMETER>.

import urllib

from sqlalchemy import create_engine


engine = create_engine('mssql+pyodbc:///?odbc_connect=' +
    urllib.quote_plus('DRIVER=FreeTDS;SERVER=<IP_OR_HOSTNAME>;PORT=1433;DATABASE=<DATABASE_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;TDS_Version=8.0;')
)
for row in engine.execute('select 6 * 7 as [Result];'):
    print row.Result

I config file odbc.ini, odbcinst.ini and freetds.conf as above.

Following are the docs I have kept:

* /etc/odbcinst.ini: # Define where to find the driver for the Free TDS connections.
* /etc/odbc.ini: # Define a connection to the MSSQL server.(ServerName points to freetds.conf section)
* /etc/freetds/freetds.conf: # Define a connection to the MSSQL server.

So You should change Servername in odbc.ini's SQL section to SQL(which is the section name in freedts.conf)?
I have no sql servers to connect at the moment, sorry about this.

And freedts docs say:

In general, the servername is arbitrary and local; it's used only by your client programs to tell FreeTDS which server to connect to. You can choose any name you like.

So please set value for ServerName in odbc.ini to section name in freedts.conf?