SQL Server, Python, and OS X

Question

What's a good way to interface Python running on OS X with a cloud-based SQL Server database?

EDIT:

With pyodbc I'm getting this error:

>>> import pyodbc
>>> cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=adsf.com;DATABASE=asdf;UID=asdf;PWD=asdf')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('00000', '[00000] [iODBC][Driver Manager]dlopen({SQL Server}, 6): image not found (0) (SQLDriverConnect)')

Answer 1

Summary

I'm using a Mac on Yosemite Version 10.10.1 trying to connect to a MS SQL Server database. I searched and couldn't find an updated detailed answer so here's a writeup that is mostly from this amazing article here. I'm adding it on stackoverflow in case the link dies. The idea is that we'll have the following layers to setup/connect.

Layers

• PART 1 - pyodbc
• PART 2 - freeTDS (can check with tsql)
• PART 3 - unixODBC (can check with isql)
• PART 4 - MS SQL (can check with a regular python program)

Steps

1. Install Homebrew from here - this is a package manager for Mac OSX. The article shows how to use another package manager 'MacPorts'. For my instructions, they're with homebrew. Basically homebrew has a folder 'cellar' that holds different versions of packages. Instead of modifying your normal files, it instead points to these homebrew packages.

2. We need to install Pyodbc, but pyodbc uses iODBC drivers by default (which comes installed with mac), but many people have issues making it work. So, we're going to use an alternative called unixodbc, which we would install in the future. For now, we need to configure the pyodbc installation such that it works with unixodbc.

Go to PyPi and download pyodbc tarball and uncompress it. Then change these lines in setup.py:

elif sys.platform == 'darwin':
        # OS/X now ships with iODBC.
        settings['libraries'].append('iodbc')

to:

elif sys.platform == 'darwin':
        # OS/X now ships with iODBC.
        settings['libraries'].append('odbc')

and now run python setup.py install.

This makes our pyodbc installation use unixodbc drivers by default. Perfect!

3. Install FreeTDS with brew install freetds --with-unixodbc (FreeTDS is the driver that sits between the Mac ODBC and MS SQL Server, this chart here shows which version of TDS you should be using based on your specific Microsoft Server version; e.g. tds protocol 7.2 for Microsoft SQL Server 2008).

4. Configure freetds.conf file (The file should be in '/usr/local/etc/freetds.conf', which for Homebrew is a link to say '/usr/local/Cellar/freetds/0.91_2/etc', but yours might be somewhere different depending on version). I edited the global and added my database info to the end (for some reason 'tds version = 7.2' would throw an error, but still work, while 8.0 just works):

   [global]
   # TDS protocol version
   tds version = 8.0
   
   [MYSERVER]
   host = MYSERVER
   port = 1433
   tds version = 8.0
   

5. Verify FreeTDS installed correctly with: tsql -S myserver -U myuser -P mypassword (you should see a prompt like this if it worked)

   locale is "en_US.UTF-8"
   locale charset is "UTF-8"
   using default charset "UTF-8"
   1>
   

6. Install unixODBC with brew install unixodbc.

7. Setup your unixODBC config files, which includes odbcinst.ini (driver configuration), and odbc.ini (DSN configuration file). By default, my files were in: /Library/ODBC (Note: NOT my user library aka /Users/williamliu/Library). Or they could also be in your homebrew installation directory /usr/local/Cellar/unixodbc/<version>/etc.

8. Open up your 'odbcinst.ini' file and then add the following (Note: Different if you use MacPorts. For Homebrew, this file is a link to the homebrew version e.g. mine is in '/usr/local/Cellar/freetds/0.91_2/lib/libtdsodbc.so'):

   [FreeTDS]
   Description=FreeTDS Driver for Linux & MSSQL on Win32
   Driver=/usr/local/lib/libtdsodbc.so
   Setup=/usr/local/lib/libtdsodbc.so
   UsageCount=1
   

9. Open up your 'odbc.ini' and then add the following (this is usually along with odbcinst.ini:

   [MYSERVER]
   Description         = Test to SQLServer
   Driver              = FreeTDS
   Trace               = Yes
   TraceFile           = /tmp/sql.log
   Database            = MYDATABASE
   Servername          = MYSERVER
   UserName            = MYUSER
   Password            = MYPASSWORD
   Port                = 1433
   Protocol            = 8.0
   ReadOnly            = No
   RowVersioning       = No
   ShowSystemTables    = No
   ShowOidColumn       = No
   FakeOidIndex        = No
   

10. Verify unixODBC installed correctly with: isql MYSERVER MYUSER MYPASSWORD. If you get an error that you cannot connect, then add -v to check what the verbose output is and fix it. Otherwise, you should see this:

    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+ 
    

11. Now verify pyodbc works with a python program. Run python in the shell or a .py file with this and you should get your query back:

    import pyodbc
    import pandas
    import pandas.io.sql as psql
    
    cnxn = pyodbc.connect('DSN=MYSERVER;UID=MYUSER;PWD=MYPASSWORD')
    cursor = cnxn.cursor()
    sql = ("SELECT * FROM dbo.MYDATABASE")
    df = psql.frame_query(sql, cnxn)
    

You can refer to the documentation of pyodbc to get more help after this.

Answer 2

SQLAlchemy is probably your best bet. It has an ORM, but doesn't require its use. MS SQL is supported through a number of DBAPI projects.

As for lower-level interfaces, here's the three DBAPI projects listed at SQLAlchemy's site that have vanilla Python and Unix support:

• pymssql appears to be the simplest to set up; it doesn't require FreeTDS.
• pyodbc appears to be under more active development than pymssql.
• mxODBC is a commercially-licensed interface to many databases.

Answer 3

I've been able to simplify this and repeatedly have it work in my environments as of May 2016:

Install FreeTDS

brew install freetds --with-unixodbc

Install PYODBC

Extrapolated from Reference

pip install -U \
    --global-option=build_ext \
    --global-option="-I/usr/local/include" \
    --global-option="-L/usr/local/lib" \
    pyodbc

Tell UnixODBC about the FreeTDS Driver

Note: You may have a different version

cat <<'EOF' >> /usr/local/Cellar/unixodbc/2.3.4/etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL on Win32
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1
EOF

From there, I had to tell pyodbc to use the FreeTDS Driver:

dsn = 'DRIVER=FreeTDS;DATABASE=MyDb;SERVER=...'

This is fantastic as now you can use it with aioodbc if you are doing async programming in Python 3.x:

async with aioodbc.connect(dsn=dsn, loop=asyncio.get_event_loop()) as conn:
    async with conn.cursor() as cur:
        await cur.execute('SELECT 42')
        r = await cur.fetchall()
        print(r)

Alternatively: You can use pymssql flat out, but that won't work if you want to use odbc or asyncio.