Since I spent essentially two full days figuring out how to access a corporate MS SQL database pythonicly, I figured I should leave some notes, for future reference and to aid other souls looking to do the same.
These instructions and the commands that follow, were executed on a MAC OS 10.8.3 system. Additionally, I found this blog post especially helpful during the debugging process.
On mac os, there is a default ODBC manager, iODBC. Other Unix based systems tend to use unixODBC. Look elsewhere for a discussion about the differences between these driver managers. The only feature we care about is being able to connect to SQL databases through pyodbc, and at the time of this writing pyodbc requires iODBC as its manager.
Start by installing freeTDS libraries. FreeTDS allows unix programs to talk natively with MS SQL and SyBase databases.
brew intsall freetds
FreeTDS needs to be configured, so edit your freetds.conf in /usr/local/etc/ or ~/.freetds.conf
nano ~/.freetds.conf
Under # A typical Microsoft server you'll see host, port, and tds version variables. These and these only should be set.
host = myserver.company.com
port = 1433
tds version = 7.2 (for MS SQL 2008)
Create ~/Library/ODBC/odbc.ini and fill out the file like so:
[MyDB]
Description = Company MS SQL
TDS_Version = 7.2
Driver = /usr/local/lib/libtdsodbc.so
Server = *********
Port = 1433
The driver is setup, so now install pyodbc:
sudo pip install pyodbc
Now try to hit your DB:
import pyodbc as p
conn = p.connect("DSN=mysql01;UID=username;PWD=password")
Should resolve without a problem. See pyodbc docs for help with executing queries, etc.
I am in the same boat - this is driving me up a wall. I worked through this but still no joy. I am at a loss.