Run To The Future

Update: It has been pointed out to me that I should set the ODBCINI and ODBCSYSINI to point to somewhere more consistent (eg. /usr/local/etc) from wherever is running the python. So in my shell .zshrc, or in the Apache conf when it's run from the web server.

The other solution I came up with (from actually reading the documentation instead of skimming it), is to use this:

import urllib
import sqlalchemy
from sqlalchemy import create_engine
url = urllib.quote_plus('DSN=STAGEDB;UID=username;PWD=password;DATABASE=MY_DB')
engine = create_engine('mssql+pyodbc:///?odbc_connect=' + url)
engine.connect()

Works a treat.
End update

What a combination...

Had a fair bit of trouble with this one. I'm using the iODBC that ships with Mac OSX and FreeTDS as the driver (that's already up and running for PHP to connect to MSSQL).

Here and here suggest methods to get it installed, none of which worked. Turns out the odbc.ini and odbcinst.ini files have no place in /etc/ or /usr/local/etc on Mac OSX, instead they need to be placed in /Library/ODBC. (Can probably set system wide ODBCINI and ODBCSYSINI env vars but it's a bridge too far at this point...)

The odbcinst.ini is:

[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/local/freetds/lib/libtdsodbc.so

and the odbc.ini (which defines the DSNs) is:

[STAGEDB]
Description = stage
Driver = FreeTDS
Servername = STAGEDB

Ok, so that means this script (just using pyodbc) works fine:

import pyodbc
url = 'DSN=STAGEDB;UID=username;PWD=password;PORT=1433;DATABASE=MY_DB;'
conn = pyodbc.connect(url)

But this doesn't:

import sqlalchemy
from sqlalchemy import create_engine
url = 'mssql://usernamer:password@STAGEDB'
engine = create_engine(url)
engine.connect()

Well, it connects to the database server using the DSN called "STAGEDB" but because the database isn't set, then it's not connecting to a specific database, and you can't simply tell it to connect to a database after this connection has been established (I had a read of the API which didn't help and maybe that's not how db connections are even able to operate – beyond my ken).

So the first solution is to try this in the script instead:

url = 'mssql://username:password@STAGEDB/MY_DB'

But this doesn't work – because we are using pyodbc for the connection, it can only connect by using a DSN (and I can't see how to alter this), and in this case the DSN it's looking for is "STAGEDB/MY_DB" and that's not defined.

So it means I have to define a DSN in the odbc.ini file for every combination of database and database server I want to connect to, for example:

[STAGEDB_MYDB]
Description = stage
Driver = FreeTDS
Servername = STAGEDB
Database = MY_DB

Seems like I'm doing something wrong, but I'm not getting it to work any other way.