Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Install FreeTDS, unixODBC and pyodbc on OS X

First, install the following libraries:

$ brew install unixodbc
$ brew install freetds --with-unixodbc

FreeTDS should already work now, without configuration:

$ tsql -S [IP or hostname] -U [username] -P [password]
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> ^D

Onto unixODBC, we need to link to the driver, edit /usr/local/etc/odbcinst.ini:

[FreeTDS]
Description = TD Driver (MSSQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
FileUsage = 1

The test command we're using requires configuring a DSN, so edit /usr/local/etc/odbc.ini:

[MYDSN]
Driver = FreeTDS
Server = [IP address]
Port = 1433

The configuration for your DNS might vary, you might need the TDS_Version or Servername directives. The above worked for me for SQL Server 2008 R2. Now, run the test command:

$ isql MYDSN [username] [password] -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> ^D

If the test succeeded, you can continue onto installing the Python library pyodbc. Version 3.0.7 and before didn't link with unixODBC, so a change had to be made to setup.py, follow the steps at the bottom instead. The current version (3.0.10) has resolve this issue, so you can go ahead and install from pypi:

pip install pyodbc

Now pyodbc should work:

import pyodbc
pyodbc.connect('DSN=MYDSN;UID=[username];PWD=[password]')

You don't need to have your DSN configured in odbc.ini, so clear that file. You probably want to select a database on connect, so change your connect line to read:

pyodbc.connect('DRIVER=FreeTDS;SERVER=[IP address];PORT=1433;DATABASE=[database];UID=[username];PWD=[password]')

Note that you could also link to the library file of FreeTDS instead of using odbcinst.ini, like this:

pyodbc.connect('DRIVER=/usr/local/lib/libtdsodbc.so;SERVER=[IP address];PORT=1433;DATABASE=[database];UID=[username];PWD=[password]')

Reference -- Installing pyodbc version 3.0.7 and before

Version 3.0.7 didn't link with unixODBC on OS X, so a change had to be made to setup.py. Note that the current version of writing (3.0.10) doesn't have this issue. Follow these steps only to install the old version. Download the source package and extract it somewhere. Find the following lines (146-147):

    elif sys.platform == 'darwin':
        # OS/X now ships with iODBC.

And change this line:

        settings['libraries'].append('iodbc')

into:

        settings['libraries'].append('odbc')

Then run the following command to install:

> python install .
@Bouke

This comment has been minimized.

Copy link
Owner Author

commented Apr 11, 2014

@jawache

This comment has been minimized.

Copy link

commented May 16, 2014

I searched the internet for about 3 hours, this solved it for me - cheers!

@mirandes

This comment has been minimized.

Copy link

commented Jun 19, 2014

Thanks, big help.

@isofer

This comment has been minimized.

Copy link

commented Jul 31, 2014

I waisted soooo much time installing and debugging...
you solved my problems. thank you so much!!

@waitsfornone

This comment has been minimized.

Copy link

commented Sep 4, 2014

This works up until I install pyodbc from the source. Then my homebrewed system Python shell cannot import pyodbc. Any tips?

python setup.py install put my pyodbc here:

/usr/local/lib/python2.7/site-packages/pyodbc-3.0.7-py2.7-macosx-10.9-x86_64.egg

@joeb1415

This comment has been minimized.

Copy link

commented Mar 20, 2015

8 hours in I finally found this post. THANK YOU

@dbkaplun

This comment has been minimized.

Copy link

commented Jun 2, 2015

FYI, looks like the newest version of pyodbc (3.0.10) now uses unixodbc instead of iodbc for OSX.

@biancamihai

This comment has been minimized.

Copy link

commented Jul 28, 2015

thanks for this! I have searched for hours ...

@Bounder

This comment has been minimized.

Copy link

commented Sep 11, 2015

Thank you so much! I had to reinstall this odbc crap after some cleaning up on my and this really helped. You're the best!

@Bouke

This comment has been minimized.

Copy link
Owner Author

commented Sep 11, 2015

@dbkaplun thanks, I have updated the instructions so pyodbc is installed from pypi instead.

@soobrosa

This comment has been minimized.

Copy link

commented Oct 12, 2015

👍

a brew rm unixodbc and a brew rm freetds helps beforehand

@meet-bhagdev

This comment has been minimized.

Copy link

commented Oct 13, 2015

this worked! also +1 to the tip by @soobrosa

@soobrosa

This comment has been minimized.

Copy link

commented Oct 13, 2015

Anybody has any idea on how to get bsqldb or freebcp try not to connect to master in Azure? Seems like the -D now works with TSQL.

@jonneff

This comment has been minimized.

Copy link

commented Mar 28, 2016

You Rock!!! Thanks also to soobrosa for brew rm advice.

@benflips

This comment has been minimized.

Copy link

commented Jun 3, 2016

I too sing your praises. Thanks!

(@soobrosa: also an honourable mention for the brew rm)

@shanmdphd

This comment has been minimized.

Copy link

commented Jun 22, 2016

This is awesome. I had a problem in installing RODBC package and this helped me out. Thanks.

@thekoc

This comment has been minimized.

Copy link

commented Jun 30, 2016

Thanks, helped me a lot

@arq101

This comment has been minimized.

Copy link

commented Sep 16, 2016

A big thank you. After spending almost a whole day tinkering around with other solutions and not succeeding, these instructions finally worked.

@rstackhouse

This comment has been minimized.

Copy link

commented Oct 9, 2016

What about freetds.conf?

@mcescalante

This comment has been minimized.

Copy link

commented Nov 3, 2016

I found that pymssql worked a lot better and with less fuss than trying to manually setup FreeTDS, unixODBC, and pyodbc together.

Assuming you're here for OS X/macOS instructions and are using TDS_Version 8.0 (SQL Server 2014), you'll need to install version 2.2.0 or above of pymssql which hasn't made it to PyPI yet (see this issue). Run the following command to install:

pip install git+https://github.com/pymssql/pymssql.git

After that, the simple example here should work great assuming your connection string works :) http://pymssql.org/en/stable/pymssql_examples.html

@agu3rra

This comment has been minimized.

Copy link

commented Jan 5, 2017

FreeTDS no longer generates .so objects to be used as drivers on the odbc.ini file. Are there any workarounds? Thanks!

@spinningarrow

This comment has been minimized.

Copy link

commented Jul 17, 2017

The first line brew install unixodbc is unnecessary, since the second line brew install freetds --with-unixodbc will install unixodbc.

@nathanegraham

This comment has been minimized.

Copy link

commented Oct 5, 2017

Thanks for this. Well written and works great for me.

@a-pagano

This comment has been minimized.

Copy link

commented Dec 5, 2017

Works like a charm thanks a lot 👌

@VMerlin

This comment has been minimized.

Copy link

commented Aug 16, 2018

How can i connect to Veritca with the same ? Im getting errors with [] due to:SQLDriverConnect: {01000} [unixODBC][Driver Manager]Can't open lib 'Vertica' : file not found

@CerebralMastication

This comment has been minimized.

Copy link

commented Aug 31, 2018

Hey, JD Long here... I was googling for this and found it helpful... then I realized you wrote it based on some notes I made years ago... I can't stop laughing.

@raagha-b

This comment has been minimized.

Copy link

commented Feb 14, 2019

Latest brew stopped supporting --with option. Anyone have a work around for this ?

@cwade

This comment has been minimized.

Copy link

commented May 15, 2019

There are many instructions on the internet about how to do this, but yours were both the simplest and the only ones that actually worked for me.

@raagha-b You can just leave off the --with-unixodbc option - it's installed by default now.

The only adjustment I had to make, which I'm mentioning here in case it helps anyone else, is that my username is an active directory login with format MY_COMPANY\USERNAME, and the above instructions only worked with quotes around the whole thing ("MY_COMPANY\USERNAME").

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.