Adam's Awesome Anecdotes

Text

Connecting to MS-SQL Server 2000 from Python on OS X

This document explains how to get the pyodbc module in python (version 2.6) on Mac OS X Snow Leopard to connect to a SQL Server 2000 instance using FreeTDS and unixODBC.  Although much of the info is contained in the sources at the bottom of this article (and I highly recommend reading through them), I found I had to tweak a few of their steps in each tutorial.  I suspect this is mostly due to version differences since they posted their solution, but much thanks goes to those people for figuring it out and posting their results.  While pyodbc is a very nice module and works great on Windows, this was far more difficult on the Mac that it should have been probably.  I figured just in case the resources I used to figure this out ever vanished, I had better document how I made it work for my setup.  Here are the steps I used.

NOTE: all other commands occur in terminal from here on out.

  • Install freetds (the sql server driver) — sudo port install freetds +mssql +odbc (note the spaces for the variant names — singletoned’s blog had this as all one word, which threw me a bit due to my unfamiliarity with MacPorts
  • Install unixODBC (the odbc / dsn connection setup) — sudo port install unixODBC
  • cd into /opt/local/etc/freetds
  • Create a Driver Template for unixODBC — sudo touch tds.driver.template  (NOTE: these files didn’t already exist for me, but they may for you.  I suggest you check first)
  • Edit the file — sudo vi tds.driver.template

[TDS]

Description     = FreeTDS Driver for Linux & MSSQL on Win32

Driver          = /opt/local/lib/libtdsodbc.so

Setup           = /opt/local/lib/libtdsS.so 

NOTE: The file libtdsS.so doesn’t exist on my system, so I’m not sure if that is actually necessary, but I left it in there anyway

  • Create a DSN Template for unixODBC — sudo touch tds.dsn.template
  • Edit the file — sudo vi tds.dsn.template

[my_dsn]

Description     = Connection to windows virtual machine

Driver          = TDS

Trace           = No

Database        = my_database_name

Server          = MY-SERVER

Port            = 1433

TDS_Version     = 8.0

  • Install the driver file — sudo odbcinst -i -d -f tds.driver.template
  • Install the DSN file — sudo odbcinst -i -s -l -f tds.dsn.template

NOTE: singletoned’s blog recommended at this point running easy_install pyodbc.  I ended up having to recompile pyodbc so I don’t recommend following this part (especially since I can’t find a way to uninstall modules yet or what exactly easy_install did).  I just have to research this more.

NOTE: Optionally, if you want to test FreeTDS you can use the following command: tsql -S <server> -U <user> (will prompt for a password.  I believe this will bypass the freetds config file, then again perhaps it uses the file — I don’t really have it clue.  Whatever I did, this seemed to work fine for me.  BTW, the FreeTDS config file can be found at /opt/local/etc/freetds/freetds.config (I never had to edit this file)

  • Test your DSN setup — isql -v <my_dsn> <username> <password> (make sure to include your username and password — you won’t be prompted, it will just return error messages about not being able to connect to the datasource
  • Unzip pyodbc that you downloaded earlier
  • cd into the pyodbc source directory
  • Edit setup.py (make a backup first!)
  • Find the following two lines

extra_compile_args = None

extra_link_args    = None

  • and edit them to look like this (path is different from easysoft instructions):

extra_compile_args = None

extra_link_args    = [‘-L/opt/local/lib’]

  • Find this line:

extra_compile_args = [‘-Wno-write-strings’]

  • and edit it to look like this (again, path is different from easysoft instructions):

extra_compile_args = [‘-Wno-write-strings’, ‘-I/opt/local/include’]

  • Also, the setup.py attempted to accommodate OS X now (not documented in the EasySoft instructions).  So find the section that looks like this:

    elif sys.platform == ‘darwin’:

        # OS/X now ships with iODBC.

        libraries.append(‘iodbc’)

  • and make it look like this (just comment it out — perhaps it would be better to use iODBC than unixODBC, but the docs I found were pushing the latter, so I went with it and then was committed by this point!):

    #elif sys.platform == ‘darwin’:

        # OS/X now ships with iODBC.

    #    libraries.append(‘iodbc’)

  • Now build pyodbc with: sudo python setup.py build (you may want to rm -rf the build directory in the python source folder to ensure a clean build.  I get a ton of warnings / error here, but apparently they’re OK — they even mention them in the setup.py comments.
  • Now install pyodbc with: sudo python setup.py install
  • Test it out!  Fire up the python interpreter:

$ python

»>import pyodbc (I get an error message here about pyodbc already being in the classpath — I think it’s from prior attempts.  I’ll figure it out another day.)

»>conn = pyodbc.connect(“DSN=MyDSN;UID=user;PWD=pwd”)

»>cursor = conn.cursor()

»>data = cursor.execute(“select count(*) from …”).fetchone()

»>print data

Hopefully it works at this point!  Good luck!

None of this would have been possible without the helpful advice from the following people:

http://blog.singletoned.net/2009/07/connecting-to-ms-sql-server-from-python-on-mac-os-x-leopard/

http://www.easysoft.com/developer/languages/python/pyodbc.html (for info on recompiling pyodbc with unixODBC support)

http://kipb7.wordpress.com/2008/06/12/pyodbc-unixodbc-freetds-config/


To Tumblr, Love PixelUnion