Installing pyodbc fails on OSX 10.9 (Mavericks)

Question

When running pip install pyodbc, I get

In file included from .../build/pyodbc/src/buffer.cpp:12:
    .../build/pyodbc/src/pyodbc.h:52:10: fatal error: 'sql.h' file not found
    #include <sql.h>
             ^
    1 error generated.
    error: command 'cc' failed with exit status 1

It seems that Mavericks has no sql.h under /usr/include

Did anyone manage to install pyodbc? Is there a known workaround?

As a relevant bit of information - unless something has changed between then and now, pypyodbc may have a problem with not reading the full column headers on 64-bit systems. I [fixed that here](https://github.com/waynew/pypyodbc) — Wayne Werner, Apr 28 '15 at 15:27
Answered the question in following post for M1 Users: https://stackoverflow.com/a/72693640/8291933 — Dugini Vijay, Jun 20 '22 at 22:41

score 63 · Answer 1 · answered Feb 24 '14 at 23:36

63

You can use Homebrew to install unixodbc, then pyodbc via pip in the usual fashion.

brew install unixodbc && pip install pyodbc

This works for me on Mavericks.

answered Feb 24 '14 at 23:36

Ryan Balfanz

681
5
4

3

Upvoted at first, because it appeared to work. However it installed correctly, I couldn't get it to connect. – Bouke Apr 11 '14 at 07:37
Does `pip` still work for `pyodbc`? I'm having the same problem as [this question](http://stackoverflow.com/questions/21054656/python-pip-unable-to-locate-pyodbc), and I'm surprised to see yours work. – Michael Apr 30 '14 at 18:12
1

`pip` does not work for `pyodbc`, but after unixodbc is installed building from source works. – Castrona Jun 04 '14 at 22:58
if work if you use the link to the direct source (ex https://pyodbc.googlecode.com/files/pyodbc-3.0.7.zip). thanks for this as the original answer didn't work for me! – Alessandro Mariani Aug 12 '14 at 14:43
1

This went through for me with: sudp pip install pyodbc --allow-external pyodbc --allow-unverified pyodbc – tol4trob Oct 07 '14 at 15:49
It will install, however you might notice issues such as this when running queries programmatically: `ProgrammingError: ('42622', '[42622] ERROR 3507: Identifier "\xf2\xa0\x80\x8d\xef\xbf\xbd\xef\xbf\xbd\xef\xbf\xbd\xf2\xa0\x81\x94\x...`. Vitaly's answer correctly binds the include dirs to pyodbc for the build step – Jared Scott Oct 08 '14 at 16:04
unixodbc does not work with pyodbc on OS X 10.9 (Mavericks) https://code.google.com/p/pyodbc/issues/detail?id=356 – elgehelge Jan 30 '15 at 10:42
This worked for me, but also had to install FreeTDS with `brew install freetds --with-unixodbc`. This was with Mavericks, Python 2.7.10, pyodbc 3.0.10. – aldel Oct 05 '15 at 22:14

score 33 · Accepted Answer · answered Nov 19 '13 at 14:51

33

As you noticed OSX Mavericks dropped sql headers that are required for PyODBC compilation. Following these steps allowed me to install PyODBC:

Make sure you have iODBC library installed (http://www.iodbc.org/)
Download and extract iODBC sources
Run pip install --no-install pyodbc
cd [VIRTUAL_ENV]/build/pyodbc
Run python setup.py build_ext --include-dirs=[LIBIODBC_SOURCES]/include/

Run pip install --no-download pyodbc:

Installing collected packages: pyodbc
  Running setup.py install for pyodbc

    warning: no files found matching 'tests/*'
Successfully installed pyodbc
Cleaning up...

I could as well copy the files under [libiodbc_sources]/include/ to my /usr/include and just run pip install pyodbc, but I didn't want to add files manually to system folders.

answered Nov 19 '13 at 14:51

m_vitaly

11,856
5
47
63

2

Well documented instructions for this process. I ended up needing to use the ``-I`` flag for the include instead of ``--include-dirs`` for some reason. But it worked! – Thomas Farvour Jan 27 '14 at 22:45
1

Hey for someone new to the Mac environment, what do the folders [libiodbc_sources] and [VIRTUAL_ENV] refer to? I tried searching for them but couldn't find them... Thanks for any help! – Sim Feb 27 '14 at 21:08
1

[LIBIODBC_SOURCES] is where you extract iODBC [VIRTUAL_ENV] is python virtual env path (not special for Macs), if you are not using virtualenv, you will have the relevant directory in the python installation you use – m_vitaly Mar 02 '14 at 21:32
6

With the latest version of pip, step 3 becomes `pip install --allow-external pyodbc --allow-unverified pyodbc --no-install pyodbc==3.0.7`. Note that --no-install is deprecated. – Jared Scott Oct 08 '14 at 16:00
do you know how to make pyodbc work with iodbc? I don't know how to configure "Driver" in odbcinst.ini. – Vincent Wen Oct 11 '14 at 09:57
6

With a recent version of pip, `--no-download` and `--no-install` are gone. I downloaded in a scratch directory using `pip install --download . pyodbc --no-binary pyodbc`, then untarred and cd-ed into the untarred directory; edited `setup.py` to have the right ODBC link library referenced ('iodbc' in my case), then Step 5. Finally, `pip install .` in place of Step 6 (with `--upgrade ` as necessary). – B98 Jul 02 '15 at 17:14
1

As @B98 mentioned, pip has removed `--no-download`. The easiest way I found was to grab the tarball directly from [github](https://github.com/mkleehammer/pyodbc/releases) and then continue on with the steps. Also, replace step #6 with `python setup.py install`. – Jared Scott Oct 23 '15 at 15:28
Can anyone post an example of what [LIBIODBC_SOURCES] typically looks like? We have tried a variety of paths without success. – Praxiteles Oct 28 '15 at 19:54
You can easily work around the missing --no-option by telling pip install where to find the includes, once you've installed iODBC. `pip install pyodbc --global-option=build_ext --global-option="-I/opt/local/include/"` – JL Peyret Aug 25 '16 at 06:39

dennisobrien · Answer 3 · 2015-04-28T02:52:48.360

31

After many dead ends, this worked for me:

$ brew unlink unixodbc
$ brew install unixodbc  --universal
$ sudo pip install --upgrade --global-option=build_ext --global-option="-I/usr/local/include" --global-option="-L/usr/local/lib" --allow-external pyodbc --allow-unverified pyodbc pyodbc

edited Apr 28 '15 at 02:52

answered Sep 23 '14 at 02:24

dennisobrien

1,148
13
7

This blog post was helpful in bringing this up to date for pyodbc-3.0.9 on OSX 10.9: http://stefanoapostolico.com/2015/04/20/django_mssql_osx.html – dennisobrien Apr 28 '15 at 02:53
Thanks! Macports works too by substituting 'usr' with 'opt'. – TurtleIzzy Jul 29 '16 at 12:35
This worked for me when the accepted answer did not! Seems like it's more up-to-date. – alitheg Sep 21 '16 at 15:25
1

For the second command, I am getting this `Error: invalid option: --universal` – yardstick17 Jan 04 '22 at 14:58
Options `-allow-external` and `--allow-unverified` are gone. This answer isn't valid anymore. – Jari Turkia Feb 15 '22 at 15:25

Bouke · Answer 4 · 2014-07-22T04:41:18.690

See my installation instructions which I've written after some futile attempts of the other answers provided:

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. The current version of writing (3.0.7) doesn't link with unixODBC on OS X, so a change has to be made to setup.py. 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 .

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]')

Upvoted for showing the FreeTDS installation step, which most other answers ignore. I didn't need to edit any of the files you mentioned, though. Just install unixodbc, freetds, and pyodbc. This may have been due to improvements in more recent versions of pyodbc (current is 3.0.10). — aldel, Oct 05 '15 at 22:19
I'm getting `Error: invalid option: --with-unixodbc`. Any suggestions? — Jari Turkia, Feb 15 '22 at 15:23

score 10 · Answer 5 · answered Jan 10 '14 at 05:09

10

I had no joy with @Vitaly's answer because there appears to be an issue building packages on Mavericks to do with the lack of support for hard-linking. I couldn't get the package to build.

So I opted for @Vitaly's second suggestion which was to copy the necessary files from the [libiodbc_sources]/include/ directory to /usr/include and the install worked find. Here is a list of the files you will need to copy:

sql.h
sqltypes.h
iodbcunix.h
sqlext.h
sqlucode.h

answered Jan 10 '14 at 05:09

MFB

19,017
27
72
118

Where is /usr/include and where is [libiodbc_sources]? Libiodbc didn't indicate where it is installing when we unpackaged it. – Praxiteles Oct 28 '15 at 19:59
@Praxiteles hope you found an answer by now, but if not for version 3.52 of iODBC you can find the [libiodbc_sources] at `/Library/Frameworks/iODBC.framework/Versions/3.52/Headers`. – alexhb Mar 05 '16 at 00:33

score 10 · Answer 6 · answered Nov 13 '15 at 14:29

10

This worked for me after trying just about everything else suggested.

brew install unixodbc
sudo pip install --upgrade --global-option=build_ext --global-option="-I/usr/local/include" --global-option="-L/usr/local/lib" --allow-external pyodbc --allow-unverified pyodbc pyodbc

Running Mac OS 10.11.1,Homebrew 0.9.5, and pip 7.1.2

answered Nov 13 '15 at 14:29

Lothilius

178
1
5

allow-unverified threw a "no such option" error. Suggestions? – Praxiteles Dec 04 '16 at 21:22
@Praxiteles Did you copy paste as is? – Lothilius Dec 13 '16 at 17:11
Works on Homebrew 1.3.4, pip 9.0.1, MacOS 10.12 – Istvan Sep 29 '17 at 10:19
Even simpler, this worked for me: `brew install unixodbc; pip install pyodbc` – DS. Jan 18 '19 at 18:54
It worked for me: `brew install unixodbc; pip3 install --upgrade --global-option=build_ext --global-option="-I/usr/local/include" --global-option="-L/usr/local/lib" pyodbc` – Timur Nurlygayanov Jun 04 '20 at 07:22

score 2 · Answer 7 · edited May 23 '17 at 12:02

If you see errors like

clang: error: unknown argument: '-mno-fused-madd' [-Wunused-command-line-argument-hard-error-in-future]

The problem is that with Mavericks Apple has removed gcc from the command line developer tools; it is now clang just symlinked to gcc. The --mno-fused-madd flag is not supported by clang (same goes for lots of other flags).

One solution might be to install gcc using homebrew or another method and symlink /usr/bin/gcc to a proper gcc.

A simpler workaround that worked for me is suppressing these error by turning them into warnings:

export CFLAGS=-Qunused-arguments

After settings that I was able to pip install pyodbc without errors.

PS! In future versions of clang this might not be possible. At least it works on:

$> gcc -v
Configured with: --prefix=/Applications/Xcode.app/Contents/Developer/usr --with-gxx-include-dir=/usr/include/c++/4.2.1
Apple LLVM version 5.1 (clang-503.0.38) (based on LLVM 3.4svn)
Target: x86_64-apple-darwin13.1.0
Thread model: posix

Refs:
https://bitbucket.org/cffi/cffi/issue/46/unused-mno-fused-madd-clang-warnings-on-os https://coderwall.com/p/lqpp8w clang: error: unsupported option '-static-libgcc' on Mac OSX Mavericks

score 1 · Answer 8 · edited May 15 '15 at 20:05

I just went through the whole process on Mac OS X; connecting to pyodbc to MS SQL Server 2014. The whole process is as follows:

Connection pipeline:

pyodbc ----> iodbc ----> freetds ----> MS SQL Server 2014

Build freetds (the SQL Server driver/connector):

./configure --prefix=/usr/local --with-tdsver=8.0

make

sudo make install

// you should see /usr/local/lib/libtdsodbc.so was generated

//test method 1:

TDSVER=8.0 tsql -H hostname -p 1433 -U username -P XXX -D databasename

//test method 2:

//config /usr/local/etc/freetds.conf
[mssqlserver]
    host = XXX
    port = 1433
    tds version = 8.0

//run

tsql -S mssqlserver -U username -P XXX -D databasename  

//if you can run sql, good to go!

Build iodbc (ODBC manager):

//download from github, go to the folder

cd mac

./configure 

./make

sudo ./make install

//config /usr/local/etc/odbc.ini
[mssqlserver]
Driver=/usr/local/lib/libtdsodbc.so
TDS_Version=8.0
Server=xxxx
Port = 1433
Trace = Yes
Description=XXX

//test

which iodbctest

iodbctest 

DSN=masqlserver;UID=xxx;PWD=xxx

//if you can run sql, good to go!

Connect pyodbc (Python ODBC wrapper) to iodbc:

pip install pyodbc 

//in python,

conn = pyodbc.connect("DSN=mssqlserver;UID=xxx;PWD=xxxx")

If using Python, consider using a virtualenv. Toying with the system installed Python can create problems at the OS level. https://virtualenv.pypa.io/en/latest/ — FlipperPA, May 25 '15 at 14:20

score 1 · Answer 9 · answered Oct 12 '15 at 18:19

1

I was successful with sudo port install py-pyodbc

answered Oct 12 '15 at 18:19

Brian

36
2

score 0 · Answer 10 · edited May 23 '17 at 12:32

I'll add my $0.02 to this. Vitaly's answer was the main inspiration.

OSX 10.9.5, MacPorts 2.3.4, pip 8.1.2 (which did not have an --no-install option), virtualenv 14.0.6

Also helped: https://stackoverflow.com/a/22942120/1394353

Anyway, install iODBC via MacPorts

sudo port install libiodbc

The missing sql.h is deposited by MacPorts @ /opt/local/include

Now, tell pip where it can find the includes (that's where the linked answer came in handy):

pip install pyodbc --global-option=build_ext --global-option="-I/opt/local/include/"

score 0 · Answer 11 · answered Sep 19 '17 at 10:10

As pip doesn't support --no-install option anymore and the --download option is deprecated. I had to follow the following steps.

pip download pyodbc
tar -zxvf pyodbc-4.0.17.tar.gz
python setup.py build_ext --include-dirs=[DIRECTORY CONTAINING THE HEADERS]
pip install pyodbc

score -1 · Answer 12 · answered Nov 19 '15 at 08:34

-1

I met the the same problem today on ubuntu 14.04. I found some guy in below link said should install unixodbc-dev.

https://code.google.com/p/pyodbc/issues/detail?id=55

I did it, and then the pip install success.

Hope this helpful.

answered Nov 19 '15 at 08:34

WanderInCode

29
5

score -1 · Answer 13 · answered Feb 23 '17 at 20:59

OS Version: El Capitan 10.11.6 Python Version: 2.7.11 Pip Version: pip 9.0.1

1. Install iodbc for Mac (my installation is in [iODB_loc]=/usr/local/iODBC)
2. pip install --download [download_location] pyodbc==3.0.10
3. cd [download_location]
4. tar -xvzf pyodbc-3.0.10.tar.gz
5. cd pyodbc-3.0.10
6. vim setup.py: 
settings['libraries'].append('odbc')
->
settings['libraries'].append('iodbc')
7. python setup.py build_ext --include-dirs=[iODB_loc]/include/
8. pip install --upgrade .

Installing pyodbc fails on OSX 10.9 (Mavericks)

13 Answers13

Linked