Connect to MSSQL using FreeTDS / ODBC in Python.

freetds-odbc-in-python.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