Add py database stuff

12-Dec-2010 Need something to talk python to mssql from osx.

see: http://wiki.python.org/moin/SQL%20Server

also see: http://meantheory.wordpress.com/2009/10/01/mssql-freetds-pyodbc-snow-leopard

INSTALL pyodbc

• download and unzip pyodbc-2.1.8.zip
• unzip to ~/Downloads/pyodbc-2.1.8
• follow readme:    python setup.py build install

INSTALL freetds:

• follow instructions from:  http://meantheory.wordpress.com/2009/10/01/mssql-freetds-pyodbc-snow-leopard/
• download and unzip  freetds-stable.tgz
• unzip to ~/Downloads/freetds-0.82

• ./configure -prefix=/usr/local/freetds -with-iodbc=/usr -with-tdsver=8.0
• make
• sudo make install

 Test freetds:

1) sudo /usr/bin/lipo -info /usr/local/freetds/lib/libtdsodbc.so
should see:
Non-fat file: /usr/local/freetds/lib/libtdsodbc.so is architecture: x86_64

2) /usr/bin/iodbctest "Driver=/usr/local/freetds/lib/libtdsodbc.so;Server=134.89.12.29;Port=1433;TDS_Version=8.0;UID=expddba;pwd=******;" 

3) test with python

import pyodbc
driver='/usr/local/freetds/lib/libtdsodbc.so'
uid='expddba'
pwd='expdpasswordhere'
server='134.89.12.29'
port='1433'
cn = "Driver=%s;Server=%s;Port=%s;TDS_Version=8.0;UID=%s;pwd=%s;" % ( driver,server,port,uid,pwd)
cnxn = pyodbc.connect(cn)
c1 = cnxn.cursor()
c1.execute("exec sp_who")
for row in c1:
        print row

Note the code above does not require using an ODBC DSN such as you might create with the the iODBC.app....

Though that can be done, but the  iODBC.app  downloaded from and install it from xcode doesnt work... complains about wrong architecure when trying to test the connection

But you can manually create/use the 3 config files that define the DNS that iODBC.app uses.

see: http://lists.ibiblio.org/pipermail/freetds/2009q2/024684.html

The connection string becomes: cn = "Driver=%s;DSN=expd_ftds;TDS_Version=8.0;UID=%s;pwd=%s;" % ( driver,uid,pwd)

Below are extracts from the three files that will create the "DSN=expd_ftd" used above.:

Ignore the "Actual" drivers, those were install from ActualTechnologies for my RealBasic ODBC projects.....

 **IN**  /usr/local/freetds/etc/freetds.conf

[expd_ftds]
        host = solstice.shore.mbari.org
        port = 1433
        tds version = 8.0

If it exists: **IN**  /opt/local/etc/freetds/freetds.conf

[expd_ftds]
        host = solstice.shore.mbari.org
        port = 1433
        tds version = 8.0

**IN** /Library/ODBC/odbcinst.ini

[ODBC Drivers]
Actual SQL Server            = Installed
Actual Open Source Databases = Installed
Actual Oracle                = Installed
Actual Access                = Installed
FreeTDS = Installed

[Actual SQL Server]
Driver = /Library/ODBC/Actual SQL Server.bundle/Contents/MacOS/atsqlsrv.so
Setup  =

[Actual Open Source Databases]
Driver = /Library/ODBC/Actual Open Source Databases.bundle/Contents/MacOS/atopnsrc.so
Setup  =

[Actual Oracle]
Driver = /Library/ODBC/Actual Oracle.bundle/Contents/MacOS/atoradb.so
Setup  =

[Actual Access]
Driver = /Library/ODBC/Actual Access.bundle/Contents/MacOS/ataccess.so
Setup  =

[FreeTDS]
Driver = /opt/local/lib/libtdsodbc.0.s0
Setup  =

[ODBC Connection Pooling]
PerfMon    = 0
Retry Wait =

**IN** /Library/ODBC/odbc.ini
[ODBC Data Sources]
Solstice  = Actual SQL Server
equinox   = Actual SQL Server
expd_ftds = FreeTDS

[equinox]
Driver         = /Library/ODBC/Actual SQL Server.bundle/Contents/MacOS/atsqlsrv.so
Description    = equinox expddba
Server         = 134.89.12.101
UserID         = expddba
UseKeychain    = Yes
ServerName     = equinox
host           = 134.89.12.101
client charset = UTF-8
Port           = 51001

[solstice]
Driver         = /Library/ODBC/Actual SQL Server.bundle/Contents/MacOS/atsqlsrv.so
Description    = solstice flyerstaging
Server         = 134.89.12.29
Database       = expd
UserID         = expddba
UseKeychain    = Yes
ServerName     = Solstice
host           = 134.89.12.29
client charset = UTF-8

[expd_ftds]
Driver      = /opt/local/lib/libtdsodbc.0.so
Description = macports version
ServerName = expd_ftds

Screen shot 2010-12-15 at 8.39.43 AM.png (image/png)
Screen shot 2010-12-15 at 8.39.59 AM.png (image/png)