I wanted to be able to query 17 years worth of data living in an MSSQL server. It has been there since MS-SQL 6.5. the plan was to use the lovely SqlSoup extension from SQLAlchemy. I don't use windows so I needed another way to connect. I found little useful documentation online to help me on my way, so here are my notes.
- FreeBSD 8.0 (Should work on 6.x and 7.x too)
- building FreeBSD is beyond the scop of this document. Please see the FreeBSD Handbook for more help.
- FreeTDS 0.64 (freetds-0.64_4,1 form ports as of this writing)
- iODBC 3.52 (libiodbc-3.52.7 form ports as of this writing)
- Distribute (or setuptools)
- virtualenv (via easy_install virtualenv)
- pyodbc 2.1 (pyodbc-2.1.6 from source as of this writing)
- SQLAchemy 0.6 (the required patch hasn't landed on trunk yet.)
The FreeTDS port will work with either iODBC or unixODBC. I chose to go with iODBC as it ships on OSX 10.6 by default. This should (and does for me) work on OSX if you compile FreeTDS via ./configure && make && make install (CMMI) and then easy_install pyodbc.
Let's get started.
FreeTDS and iODBC
Installing FreeTDS and iODBC from ports.
That was easy wasn't it?
We need to setup two files.
Several documents I found state you should be able to use tsql to connect now:
But I get nothing when I connect. So let's ignore it for now and configure ODBC.
OK that was easy, too. Now let's install pyodbc and SQLAlchemy.
virtualenv, pyodbc and virtualenv
You can install pyodbc from ports but I like to work in virtualenvs so I will download and compile it myself. easy_install doesn't work because for whatever reason it doesn't look in /usr/local for libs or includes.
Done with distribute...
Done with virtualenv
- Setup a virtualenv to work in
virtualenv now set up.
- Setup pyodbc
pyodbc now installed
- Install SQLAlchemy
When 0.6 lands with the right patches we can easy_install it. For now it is a manual install...
Now we should be ready to give it a whirl...
Try It Out
Let's fire up and interpreter and go
>>> import pyodbc >>> pyodbc.connect("Driver=/usr/local/lib/libtdsodbc.so;Server=10.10.10.x;UID=USER;PWD=SECRETPASSWORD;PORT=1433;DATABASE=YOUR_DB;TDS_VERSION=8.0;") <pyodbc.Connection object at 0x284353b8> >>> ## That is great it gave me a connection... >>> ## let's try a query >>> from sqlalchemy import create_engine >>> from sqlalchemy.ext.sqlsoup import SqlSoup >>> engine = create_engine('mssql:///', creator=conn_factory, encoding='windows-1252') >>> soup = SqlSoup(engine) >>> [(o.NameLast, o.NameFirst) for o in soup.Authors.filter(soup.Authors.NameLast.startswith('obri'))].pop(0) ('Obrien', 'Reed')
This wasn't really that hard, but it took many permutations to get it going for me. I hope it helps someone else out there.