Cannot import pyodbc on Mac

Question

I am unable to import pyodbc on my Macbook Pro (running Mac OS X 10.10.5) and python version 2.7.10. I used pip to get it, and I have the latest version (3.0.10). It gives me the following error:

$ python
Python 2.7.10 (default, Jul 14 2015, 19:46:27) 
[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.39)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: dlopen(/Library/Python/2.7/site-packages/pyodbc.so, 2): Symbol not found: _SQLAllocHandle
  Referenced from: /Library/Python/2.7/site-packages/pyodbc.so
  Expected in: flat namespace
 in /Library/Python/2.7/site-packages/pyodbc.so

I have tried several things over the past few months to no avail, including building it myself (and (re-)installing iodbc and unixodbc along the way).

One of the weird things is that none of the other python database packages I've tried to use (e.g., sqlalchemy, pypyodbc, etc.) work either for various and similar reasons either. This has led me to suspect some underlying problem with my ODBC driver or library, but I don't know how to diagnose it.

I am working in a shared code environment where the rest of the team is using pyodbc via Windows and I really need this to work now. Any help or suggestions would be much appreciated!

---

**Added more details in reply to mauro's answer. Note, second update below changes things. **

Here are some more details that I should have included in the original question.

First, here are the results of the commands mauro asked about on my machine.

$ odbc_config --version
2.3.2
$ odbc_config --libs
-L/usr/local/Cellar/unixodbc/2.3.2_1/lib -lodbc
$ odbc_config --odbcini
/usr/local/Cellar/unixodbc/2.3.2_1/etc/odbc.ini
$ odbc_config --odbcinstini
/usr/local/Cellar/unixodbc/2.3.2_1/etc/odbcinst.ini

I was suspicious about the "Cellar" part, so I looked at the paths in mauro's answer and they all seem to point to Cellar (homebrew?) anyway:

$ ls -al /usr/local/etc/*odbc*
lrwxr-xr-x  1 *****  admin  39 17 Aug 16:57 /usr/local/etc/odbc.ini@ -> ../Cellar/unixodbc/2.3.2_1/etc/odbc.ini
lrwxr-xr-x  1 *****  admin  43 17 Aug 16:57 /usr/local/etc/odbcinst.ini@ -> ../Cellar/unixodbc/2.3.2_1/etc/odbcinst.ini

$ ls -al /usr/local/etc/odbc*
lrwxr-xr-x  1 *****  admin  39 17 Aug 16:57 /usr/local/etc/odbc.ini@ -> ../Cellar/unixodbc/2.3.2_1/etc/odbc.ini
lrwxr-xr-x  1 *****  admin  43 17 Aug 16:57 /usr/local/etc/odbcinst.ini@ -> ../Cellar/unixodbc/2.3.2_1/etc/odbcinst.ini
1395:Stephens-BlueDot-MacBook-Pro:~/BlueDot/Code/Data Processing Tools} ls -al /usr/local/etc/*odbc*
lrwxr-xr-x  1 *****  admin  39 17 Aug 16:57 /usr/local/etc/odbc.ini@ -> ../Cellar/unixodbc/2.3.2_1/etc/odbc.ini
lrwxr-xr-x  1 *****  admin  43 17 Aug 16:57 /usr/local/etc/odbcinst.ini@ -> ../Cellar/unixodbc/2.3.2_1/etc/odbcinst.ini
1396:Stephens-BlueDot-MacBook-Pro:~/BlueDot/Code/Data Processing Tools} ls -al /usr/local/lib/*odbc*
lrwxr-xr-x  1 *****  admin  46 17 Aug 16:57 /usr/local/lib/libodbc.2.dylib@ -> ../Cellar/unixodbc/2.3.2_1/lib/libodbc.2.dylib
lrwxr-xr-x  1 *****  admin  44 17 Aug 16:57 /usr/local/lib/libodbc.dylib@ -> ../Cellar/unixodbc/2.3.2_1/lib/libodbc.dylib
lrwxr-xr-x  1 *****  admin  48 17 Aug 16:57 /usr/local/lib/libodbccr.2.dylib@ -> ../Cellar/unixodbc/2.3.2_1/lib/libodbccr.2.dylib
lrwxr-xr-x  1 *****  admin  46 17 Aug 16:57 /usr/local/lib/libodbccr.dylib@ -> ../Cellar/unixodbc/2.3.2_1/lib/libodbccr.dylib
lrwxr-xr-x  1 *****  admin  50 17 Aug 16:57 /usr/local/lib/libodbcinst.2.dylib@ -> ../Cellar/unixodbc/2.3.2_1/lib/libodbcinst.2.dylib
lrwxr-xr-x  1 *****  admin  48 17 Aug 16:57 /usr/local/lib/libodbcinst.dylib@ -> ../Cellar/unixodbc/2.3.2_1/lib/libodbcinst.dylib
lrwxr-xr-x  1 *****  admin  45 17 Aug 16:59 /usr/local/lib/libtdsodbc.0.so@ -> ../Cellar/freetds/0.95.18/lib/libtdsodbc.0.so
lrwxr-xr-x  1 *****  admin  42 17 Aug 16:59 /usr/local/lib/libtdsodbc.a@ -> ../Cellar/freetds/0.95.18/lib/libtdsodbc.a
lrwxr-xr-x  1 *****  admin  43 17 Aug 16:59 /usr/local/lib/libtdsodbc.so@ -> ../Cellar/freetds/0.95.18/lib/libtdsodbc.so

/usr/local/lib/tdbcodbc1.0.0:
total 144
drwxr-xr-x   5 root  wheel    170 29 Mar  2013 ./
drwxrwxr-x  44 root  admin   1496 17 Aug 16:59 ../
-rwxr-xr-x   1 root  wheel  49796 29 Mar  2013 libtdbcodbc1.0.0.dylib*
-r--r--r--   1 root  wheel    245 29 Mar  2013 pkgIndex.tcl
-r--r--r--   1 root  wheel  15624 29 Mar  2013 tdbcodbc.tcl

I can connect fine to the DNS via tsql (specifics anonymized):

$ tsql -S servername.myserver.com -U me -P mypw -D testdb
locale is "en_CA.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Setting testdb as default database in login packet
1> 

But osql and isql both give problems:

$ isql -v MyDSN me mypw
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
[ISQL]ERROR: Could not SQLConnect

This one gives the most info. It's finding the DSN entry in my ~/.odbc.ini file at least.

$ osql -S MyDSN -U ***** -P ***** 
checking shared odbc libraries linked to isql for default directories...
/usr/local/bin/osql: line 53: ldd: command not found
error: /Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/strings: can't open file:  (No such file or directory)
osql: problem: no potential directory strings in "/usr/local/bin/isql"
osql: advice: use "osql -I DIR" where DIR unixODBC\'s install prefix e.g. /usr/local
isql strings are:
checking odbc.ini files
    reading /Users/*****/.odbc.ini
[MyDSN] found in /Users/*****/.odbc.ini
found this section:
    [MyDSN]                                                                                                                
    Description         = testdb SQLServer DB
    Driver              = FreeTDS
    Trace               = Yes
    TraceFile           = /tmp/sql.log
    Database            = Places
    ServerName          = *****
    UserName            = *****
    Password            = *****
    Port                = 1433
    Protocol            = 7.2
    ReadOnly            = No
    RowVersioning       = No
    ShowSystemTables    = No
    ShowOidColumn       = No
    FakeOidIndex        = No
looking for driver for DSN [*****] in /Users/*****/.odbc.ini
  found driver line: "  Driver              = FreeTDS"
  driver "FreeTDS" found for [*****] in .odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /odbcinst.ini
grep: /odbcinst.ini: No such file or directory

I'm not sure how to fix the problems that isql is reporting, but it seems to indicate that I have something misconfigured to me with odbc. Unfortunately, I'm sorry that I don't know / remember precisely what I did to get it this way -- I've seriously been trying various things related to this on and off for weeks.

---

Second update after mauro's comments.

I've made a bit of progress. I reinstalled both unixODBC and freeTDS (directly from http://www.unixodbc.org/ and http://www.freetds.org/ instead of using homebrew), and after doing so the output of my odbc_config commands matched mauro's output.

After some playing with my paths, I then was able to get both osql and isql to successfully connect to my SQL Server instance. (I discovered one reason this was failing before was because the IT dept. in my organization was blocking all traffic to port 1433 when on the LAN. When I switched to Wifi, like my Windows co-worker, it worked.) I consider this great progress!

However, when I tried to import pyodbc from within python again, I got the exact same error message as I started out with. Sigh. So any other ideas would still be appreciated!

Answer 1

First, thank you to @mauro for his/her helpful and persistent suggestions.

After months of banging my head against walls, last night I was finally able to get this to work!

Here, I outline some of the key things (for me) in the hopes that these may be useful to others in the same predicament as I was.

BG. Sometime a while ago (over a month), I read and attempted to do the things suggested here. Although I don't clearly remember everyting I tried then, this led me to get tsql working and also, I think, to my having Homebrew versions of unixODBC and freeTDS installed.

Starting with my asking the question above, here are some of the things I tried that seemed to have had an impact. (I'm not sure which things in particular were the most important, so I'm incuding everything.) Points 1 and 2 are described above, so I won't dwell on them.

I. I re-installed unixODBC and freeTDS from their project web sites.

II. I discovered that port 1433 was being blocked on my LAN, so I switched to WiFi where it wasn't blocked.

These two things led to me being able to get isql and osql to work.

III. Reasoning that the pyodbc was failing to import in python due to something akin to a dynamic linking error. I tried loading /usr/local/lib/libodbc.dylib directly using both dl.open() and ctype.cdll.LoadLibrary(). In both cases, I got an error telling me:

dl.error: dlopen(libodbc.dylib, 6): no suitable image found.  Did find:
    /usr/local/lib/libodbc.dylib: mach-o, but wrong architecture

After some digging, thie led me to recompile unixODBC for 32- instead of 64-bits as follows:

sudo ./configure CFLAGS="-m32 -arch i386 -O2" LDFLAGS="-m32 -arch i386" CXXFLAGS="-m32 -arch i386"
sudo make 
sudo make install

At that point, I was able to explicitliy load libodbc.dylib using dl.open() and finally get pyodbc to import!!

IV. Unfortunately, without the explicit loading via dl.open(), the import still failed. This led me to play with my LD_LIBRARY_PATH (as suggested here), but nothing seems to have worked yet. So I'm still stuck using the dl.open() hack.

Furthermore, it still didn't work yet, failing related to the freeTDS driver when I tried to connect to a data source. This finally led me to the following "working" hack-around:

import sys
if (sys.platform == 'darwin'):
    import dl
    _lib1 = dl.open("libodbc.dylib")                      # Found in /usr/local/lib
    _lib2 = dl.open("/opt/local/lib/libtdsodbc.so")
import pyodbc

Note that it was necessary to use the global variables _lib1 and _lib2 to get it to work (I think in order to keep the stuff loaded).

At this point, things finally seem to be working well enough that I can use pyodbc!!

---

There were a few more things I tried along the way, but it's unclear to me if they helped or not.

• I also tried to compile freeTDS in 32-bit mode, similar to what I did for unixODBC, but I'm not sure if that worked.

• I downloaded, built and installed pyodbc from the github repo as opposed to using pip. (It was the same version -- 3.0.10 -- as pip supplies though.)

• Per comment #10 here, I added two lines to the darwin case of setup.py in the pyodbc source download (below) and re-ran python.py setup.py build install.

Like:

elif sys.platform == 'darwin':                                                                                        
    # The latest versions of OS X no longer ship with iodbc.  Assume
    # unixODBC for now.
    settings['libraries'].append('odbc')
    settings['include_dirs'] = ['/opt/local/include']              # Added this line
    settings['library_dirs'] = ['/opt/local/lib']                  # Added this line

    # Python functions take a lot of 'char *' that really should be const.  gcc complains about this *a lot*
    settings['extra_compile_args'].extend([
        '-Wno-write-strings',
        '-Wno-deprecated-declarations'
    ])

    # Apple has decided they won't maintain the iODBC system in OS/X and has added deprecation warnings in 10.8.
    # For now target 10.7 to eliminate the warnings.
    settings['define_macros'].append( ('MAC_OS_X_VERSION_10_7',) )

    settings['include_dirs'] = ['/opt/local/include']
    settings['library_dirs'] = ['/opt/local/lib']

• After I was all finished, I checked and I was also able to import and use pypyodbc without using the dl.open() hacks. I'm not sure if all of the above steps were required for that to happen though. I suspect the main issue was the 32- vs. 64-bit versions of the libraries.

---

Finally, although this wasn't related to the reasons I couldn't get pyodbc to import, I'll add a note about something that caused me to lose nearly an hour. At some point I was attempting to follow the (very helpful) instructions at this site. However, I later discovered that the non-DSN connection string that the author showed did not work. I instead had to use the FreeTDS connection attributes shown here to get things to work. For example:

"DRIVER=FreeTDS;Server=*****;Port=1433;TDS_Version=7.2;Database=*****;UID=*****;PWD=*****"

Although things are now working for me for the most part, I should mention that I'm now also sometimes getting the following error, for some of my query cursors, but I don't think it's related to any of the above. (Instead, I suspect some sort of "timeout" issue on the connection. ...)

  ...
    for row in cursor:
  File "/Library/Python/2.7/site-packages/pypyodbc.py", line 1920, in next
    row = self.fetchone()
  File "/Library/Python/2.7/site-packages/pypyodbc.py", line 1914, in fetchone
    check_success(self, ret)
  File "/Library/Python/2.7/site-packages/pypyodbc.py", line 986, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "/Library/Python/2.7/site-packages/pypyodbc.py", line 966, in ctrl_err
    raise DatabaseError(state,err_text)
pypyodbc.DatabaseError: (u'08S01', u'[08S01] [FreeTDS][SQL Server]Bad token from the server: Datastream processing out of sync')
Exception pypyodbc.DatabaseError: DatabaseError(u'08S01', u'[08S01] [FreeTDS][SQL Server]Write to the server failed') in <bound method Connection.__del__ of <pypyodbc.Connection instance at 0x60d5a8>> ignored

---

In summary, I think there were at least 3, if not 4, reasons why I couldn't use pyodbc in python. The main two were related to 32- vs. 64-bit compilation of unixODBC and some library import path issue that I still don't really understand.

Good luck to anyone else who has to slog through all of this!

Answer 2

I agree... the very first thing to check in my opinion is the underlying ODBC layer.

You said you have installed unixODBC from sources on your Mac (like me). So check main unixODBC parameters using odbc_config (you should have it under /usr/local/bin):

$ odbc_config --version
2.3.4
$ odbc_config --libs
-L/usr/local/lib -lodbc
$ odbc_config --odbcini
/usr/local/etc/odbc.ini
$ odbc_config --odbcinstini
/usr/local/etc/odbcinst.ini

Then - I guess - you have created a Data Source for your ODBC database. Use another unixODBC utility (isql) to check you can connect to your database via ODBC:

$ sql <your_DSN> <your_DB_user> <your_DB_password> -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit

If everything is OK the unixODBC layer is ok.

UPDATE

Hmmm... you get this message:

...
checking odbc.ini files
reading /Users/*****/.odbc.ini
[MyDSN] found in /Users/*****/.odbc.ini
...

but the odic.ini location reported by unixODBC is different:

$ odbc_config --odbcini
/usr/local/Cellar/unixodbc/2.3.2_1/etc/odbc.ini

I would also check which libraries are used by pyodbc. Here you have mine ones:

$ otool -L /Library/Python/2.7/site-packages/pyodbc.so
/Library/Python/2.7/site-packages/pyodbc.so:
    /usr/local/lib/libodbc.2.dylib (compatibility version 3.0.0, current version 3.0.0)
    /usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 120.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1213.0.0)

As you can see my pyodbc uses libodbc installed under /usr/local/lib. Exactly the location reported in my case by odic_config.

SECOND UPDATE

Good progress! So, to summarize, the current status is:

1. isql DSN USER PASSWORD works as expected
2. import pyodbc returns _SQLAllocHandle symbol not found. This means it doesn't find the right lib
3. otool -L ...pyodbc.so (same path as the one in 2.?) points the right lib

If this is the case I would re-install pyodbc (I did use pip) . I've heard the old version had problems to find the right headers/libs. Now that isql works... pyodbc should work as well.

Answer 3

Or......

brew install unixodbc