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.

Answer 4

Will's answer was really helpful to me.

Here are some notes on a couple differences I experienced along the way, in case they help others:

1. The pyodbc tarball already had the change required, so all I had to do was download it and run python setup.py install. (Note: The version I had installed with pip was still using iodbc, so that didn't work.

2. The Verify FreeTDS installed step didn't allow me to connect to the database because I don't have access to the master, and there is apparently no way to specify. This seems to be a well known issue. I wasted a bunch of time trying to solve it, but failed and in the end it didn't block other steps from working.

3. These instructions say to put the username and password in odbc.ini. Since we need to say the login credentials again when we log in, I tried removing the UserName and Password from odbc.ini, hoping they weren't really necessary. (I'd rather have my password written down in fewer places!) This worked fine.

4. I had to add the host to the userid in isql MYSERVER myname@foo.bar.com MYPASSWORD (and in the Python code).

(I hoped that this would mean I don't need the host in freetds.conf, but alas, that has to stay.)

Answer 5

Pyodbc + MS's own odbc provider, msodbcsql, rather than FreeTDS. My reasoning was simple - who is most motivated to have good SQL Server support? MS.

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-python

It really was pretty simple, the main hassle is that their installer only works with Homebrew, rather than macports which is what I usually use. I first tried to install Homebrew to my home directory, but the actual drivers aren't "seen" by pyodbc that way, so did a standard Homebrew install, then brew install msodbcsql. This resulted in the following packages:

(venv) jluc@sandbox$ brew list
 msodbcsql  openssl     unixodbc

Connect string that worked for me:

Driver={ODBC Driver 13 for SQL Server};Server=192.168.1.xxx;Database=mydb;Uid=myuser;Pwd=mypassword;

And, for SQL Alchemy:

"mssql+pyodbc://%(user)s:%(password)s@%(hostname)s:%(port)s/%(dbname)s?driver=ODBC+Driver+13+for+SQL+Server"

If you're also installing MS SQL Server (I got the 2016 Developer Edition), remember to : 1) use SQL Server Configuration Manager (SQLServerManager13.msc) to enable TCPIP on port 1433, for your IP. 2) open up port 1433 in Windows Firewall (wf.msc). MS config instructions

Versions: Sierra, Python 2.7, SQL Server 2016 Dev edition, Win 10 Pro.

Note: be careful around MS’s brew install. I think it used to take the initiative to install Homebrew. Not sure if it would’ve been an issue in practice.

Answer 6

I am on macOS Sierra 10.12.3. pymssql did the job perfectly. If nothing works from other upvoted answers, follow this:

brew unlink freetds
brew install homebrew/versions/freetds091
pip install pymssql

and here is a sample snippet to establish connection:

conn = pymssql.connect(serverhostname, username, password, dbname)
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')

Answer 7

There are a lot of hoops to jump through. Will's answer outlines a good number of them.

After much struggle, I managed to get this working with Docker (so this should work anywhere that runs docker).

I have tested the setup with Python 3.6 and Python 2.7: with pyodbc==3.0.10, django-pyodbc-azure and Django 1.10.4 (this setup is for Django, but works for vanilla python as well).

I've created a public image which you can use: https://hub.docker.com/r/toast38coza/python-mssql/

Here is a simple working docker setup:

version: "3"
services:
  db:
    restart: on-failure:10
    image: microsoft/mssql-server-linux:latest
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=SuperSecret(!)100
    ports:
      - "1433:1433"
  py:
    image: toast38coza/python-mssql
    links:
      - db
    environment:
      - SA_PASSWORD=SuperSecret(!)100
      - DB_NAME=mydb

Now you can run:

docker-compose run --rm py python

Which will run the python cli inside the py service above

Then try create a database:

>>> import pyodbc, os
>>> db_name = os.environ.get('DB_NAME')
>>> pwd = os.environ.get('SA_PASSWORD')
>>> connection_string = "driver=FreeTDS;server=db;PORT=1433 database=master;UID=sa;PWD={};TDS_Version=8.0;".format(pwd)
>>> conn = pyodbc.connect(connection_string, autocommit=True)
>>> conn.execute('create database {}'.format(db_name))
<pyodbc.Cursor object at 0x7fb3067f0e70>

That should create a database called mydb (the DB_NAME from the docker-compose file environment variable). Note: because we've created the link to the db service (running MS SQL), we can use the hostname db. If you are connecting to an external MS SQL setup, you obviously don't need the db service (and edit your connection string accordingly)

If you're using Django, there is a more complete example in the repo, but, just a heads up, you will need your settings to look something like this:

DATABASES = {
    'default': {
        'ENGINE': "sql_server.pyodbc",
        'HOST': "db",
        'PORT':'1433',
        'USER': "sa",
        'PASSWORD': os.environ.get('SA_PASSWORD'),
        'NAME': os.environ.get('DB_NAME'),
        'OPTIONS': {
            "driver": "FreeTDS",
            "host_is_server": True,
            "unicode_results": True,
            "extra_params": "tds_version=8.0",
        }
    }
}