(Note: If you want to just skip to the good parts, use this nice document for configuring unixodbc and freetds only: How to Install FreeTDS and UnixODBC on OS X)
This totally sucks, because the MSSQL database connection tools on OS X don't exist. You're going to need to do the following:
brew install unixodbc
brew install freetds --with-unixodbc
This sets up your system for usage with ODBC, the connection system which is required to communicate with MS SQL 2008. The next step is to configure and install pyodbc, which WILL require the application of a patch. Get the latest release of pyodbc from:
https://github.com/mkleehammer/pyodbc/releases (currently 3.0.7)
You will want to unzip the release and then go apply the following patch to it:
https://gist.github.com/sptramer/11198058
via
patch -p0 < gist.patch
(where it's assumed you've downloaded the gist as gist.patch, into the directory containing setup.py.)
Now you're ready to build:
python setup.py clean build install
Congratulations, you've got a working ODBC python setup! Now you can use sqlalchemy to connect to an MS SQL server... almost. You still have to configure unixODBC and freeTDS.
If you installed freetds through homebrew, you should have the file
/usr/local/etc/freetds.conf
You'll need to add a record to the end of this file so that it has a DSN that can be used by unixodbc, and by pyodbc.
# Your db
[Yourdb]
host = <host>
port = 1433
tds version = 8.0
client charset = UTF-8
If you installed unixODBC through homebrew, you should have the file
/usr/local/etc/odbcinst.ini
You should edit this file to have the following contents:
[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
UsageCount = 1
TDS_Version = 8.0
The spaces are actually significant - without them, some unixodbc tools will not parse them correctly.
You will also need to set the freetds library to be executable:
chmod a+x /usr/local/lib/libtdsodbc.so
Again, this is if you installed FreeTDS via homebrew. Otherwise the location of the driver library might have changed, but it will still need to be executable.
Now you will need to edit odbc.ini:
[aserver]
Description = You know, whatever
Driver = FreeTDS
Database = <db name>
Servername = Yourdb
Make sure that the Servername is the same as the name assigned in the freetds configuration.
You'll probably want to test FreeTDS to connect to the server and ensure that it works.
You can do this by editing the /usr/local/etc/freetds.conf file to contain the following
section:
# MS SQL db
[mssqlDB]
host = your.hostname.here
port = 1433
tds version = 7.0
client charset = UTF-8
Note that this assumes the server is running on the standard MS SQL port, 1433.
Then run the following command and make sure that it works just fine:
tsql -S mssqlDB -U [username] -P [password]
You can read man tsql if you need some more info on how this tests the connection.
You can test pyodbc using the samples from this nice gist:
https://gist.github.com/rduplain/1293636
If it works, then great! If it doesn't, there's probably a problem with the server, especially if tsql failed. If you see a message about a "missing driver" there's a chance that everything got screwed up, and you will need to:
- uninstall freetds and unixodbc
- uninstall sqlalchemy and pyodbc
- reinstall sqlalchemy
This should restore your system to a state before you started this work, and go through and just try it all again. It could be that there's an error in the TDS or unixODBC configuration. Best of luck!
Thanks Stephen, I used homebrew on my mac too. is ODBC the only way python can connect to mssql 2008? With groovy / java you can do other types of connections I believe. (just curious sounds like you have it working)