Create a gist now

Instantly share code, notes, and snippets.

Embed
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.

Show comment
Hide comment
Owner

Bouke commented Apr 11, 2014

@jawache

This comment has been minimized.

Show comment
Hide comment
@jawache

jawache May 16, 2014

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

jawache commented May 16, 2014

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

@mirandes

This comment has been minimized.

Show comment
Hide comment
@mirandes

mirandes Jun 19, 2014

Thanks, big help.

Thanks, big help.

@isofer

This comment has been minimized.

Show comment
Hide comment
@isofer

isofer Jul 31, 2014

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

isofer 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.

Show comment
Hide comment
@waitsfornone

waitsfornone 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

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.

Show comment
Hide comment
@joeb1415

joeb1415 Mar 20, 2015

8 hours in I finally found this post. THANK YOU

8 hours in I finally found this post. THANK YOU

@dbkaplun

This comment has been minimized.

Show comment
Hide comment
@dbkaplun

dbkaplun Jun 2, 2015

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

dbkaplun 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.

Show comment
Hide comment
@biancamihai

biancamihai Jul 28, 2015

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

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

@Bounder

This comment has been minimized.

Show comment
Hide comment
@Bounder

Bounder 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!

Bounder 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.

Show comment
Hide comment
@Bouke

Bouke Sep 11, 2015

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

Owner

Bouke commented Sep 11, 2015

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

@soobrosa

This comment has been minimized.

Show comment
Hide comment
@soobrosa

soobrosa Oct 12, 2015

👍

a brew rm unixodbc and a brew rm freetds helps beforehand

👍

a brew rm unixodbc and a brew rm freetds helps beforehand

@meet-bhagdev

This comment has been minimized.

Show comment
Hide comment
@meet-bhagdev

meet-bhagdev Oct 13, 2015

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

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

@soobrosa

This comment has been minimized.

Show comment
Hide comment
@soobrosa

soobrosa 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.

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.

Show comment
Hide comment
@jonneff

jonneff Mar 28, 2016

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

jonneff commented Mar 28, 2016

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

@benflips

This comment has been minimized.

Show comment
Hide comment
@benflips

benflips Jun 3, 2016

I too sing your praises. Thanks!

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

benflips 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.

Show comment
Hide comment
@shanmdphd

shanmdphd Jun 22, 2016

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

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

@thekoc

This comment has been minimized.

Show comment
Hide comment
@thekoc

thekoc Jun 30, 2016

Thanks, helped me a lot

thekoc commented Jun 30, 2016

Thanks, helped me a lot

@arq101

This comment has been minimized.

Show comment
Hide comment
@arq101

arq101 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.

arq101 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.

Show comment
Hide comment
@rstackhouse

rstackhouse Oct 9, 2016

What about freetds.conf?

What about freetds.conf?

@mcescalante

This comment has been minimized.

Show comment
Hide comment
@mcescalante

mcescalante 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

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.

Show comment
Hide comment
@agu3rra

agu3rra Jan 5, 2017

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

agu3rra 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.

Show comment
Hide comment
@spinningarrow

spinningarrow Jul 17, 2017

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

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.

Show comment
Hide comment
@nathanegraham

nathanegraham Oct 5, 2017

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

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

@a-pagano

This comment has been minimized.

Show comment
Hide comment
@a-pagano

a-pagano Dec 5, 2017

Works like a charm thanks a lot 👌

a-pagano commented Dec 5, 2017

Works like a charm thanks a lot 👌

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment