pyODBC refuses to look for unixODBC, instead looks for iODBC

Question

Is there a way to get pyODBC v3.0.10 to look for the unixODBC driver, instead of the iODBC driver it seems to want to look for?

My understanding is that pyODBC v3.0.10 is supposed to do this by default, while versions prior to v3.0.7 required a manual edit to the setup.py file (see reference here).

One more clue, I ran this code to list my ODBC sources, and it returned nothing:

sources = pyodbc.dataSources()
dsns = list(sources.keys())
dsns.sort()
sl = []
for dsn in dsns:
    sl.append('%s [%s]' % (dsn, sources[dsn]))
print('\n'.join(sl))

Further Background

I have been struggling with creating a connection to MSSQL Server using the following setup: pyODBC --> unixODBC --> FreeTDS --> MS SQL. The gory details are documented here.

I've got it narrowed to a specific issue (I think): the pyODBC package is looking for the iODBC driver instead of the unixODBC driver I've installed and configured. I believe this because when I run:

import pyodbc

pyodbc.connect(
    'DRIVER=FreeTDS;'
    'SERVER=MyServerIP;'
    'PORT=1433;'
    'DATABASE= DatabaseName;'
    'UID=MyUsername;'
    'PWD=MyPassword')

I get this error, with a reference to not finding the iODBC driver:

---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
<ipython-input-12-607f0d66e615> in <module>()
      1 pyodbc.connect(
----> 2     'DRIVER=FreeTDS;'
      3     'SERVER= MyServerIP;'
      4     'PORT=1433;'
      5     'DATABASE= DatabaseName;'

Error: ('00000', '[00000] [iODBC][Driver Manager]dlopen(FreeTDS, 6): image not found (0) (SQLDriverConnect)')

Thanks for any light you can shed.

I should add two things: 1) I've confirmed good installs of unixODBC and FreeTDS by establishing good connections through isql and tsql commands from the Terminal, and 2) I've reproduced these results on three separate Macs. — RJH2, Jun 22 '16 at 14:28
I believe I've answered this here on your other question: http://stackoverflow.com/questions/37933369/error-connecting-to-ms-sql-server-using-pyodbc-unixodbc-and-freetds-on-a-mac In your pyodbc.connect(), you need to replace "FreeTDS" with "{FreeTDS}" and add "TDS_Version=7.2" as a connection parameter. — FlipperPA, Jun 23 '16 at 00:41
Thanks, but gave that a try and same error. It doesn't even seem to be evaluating the FreeTDS configuration file (/usr/local/etc/freetds.conf), because it doesn't seem to matter what's in there. I get the same error if I clear the whole file. For some reason pyODBC won't look for unixODBC, as described above. It never even gets to FreeTDS. — RJH2, Jun 23 '16 at 03:49

score 1 · Answer 1 · answered Jun 23 '16 at 09:35

1

I believe the reason is you may have compiled pyodbc to use iODBC (or maybe that's the default). If you want tomake sure you build pyodbc against unixODBC, you can specify that just before running setup.py build, as instructed here (look at http://www.easysoft.com/developer/languages/python/pyodbc.html , headline pyodbc 3.0.x). Alternatively, try to follow the instructions here: https://code.google.com/archive/p/pyodbc/wikis/Building.wiki

answered Jun 23 '16 at 09:35

Yuval

237
1
2
7

I have that suspicion too, but can't test it. I'm installing pyodbc, through `conda install pyodbc` (Anaconda). If it was compiled wrong, I'd expect lots of people would have this problem. To test it, I need to build a conda package, which I don't know how to do. Finally, when I specify the FreeTDS driver path, it gives me a [different error](http://stackoverflow.com/questions/37933369/error-connecting-to-ms-sql-server-using-pyodbc-unixodbc-and-freetds-on-a-mac), indicating that even if it finds unixODBC, there remains a problem – RJH2 Jun 23 '16 at 12:52

score 0 · Accepted Answer · edited May 23 '17 at 11:58

This is a repost of my answer here, but this answers this, more-specific formation of the question. Hope I'm not violating any rules, but this took me several weeks to figure out, and a lot of peoples' help. So if it's a duplicate Q&A, so be it -- we earned it.

Well, we solved it -- with the help of a lot of people on this page and the original, chasing down a lot of blind alleys.

As (eventually) suspected, it was the pyodbc link in the connection. I was using pyodbc v3.0.10, by downloading from the Anaconda package repository. The solution was v.3.0.9. Once I uninstalled v3.0.10, downloaded v3.0.9 from the pypi repository and then built and installed my own conda package... it worked.

The steps I took were as follows (note these were specific to an anaconda environment):

conda uninstall pyodbc

conda skeleton pypi pyodbc --version 3.0.9

conda build pyodbc

conda install pyodbc=3.0.9 --use-local

Once I went back to my Jupyter notebook and ran the same code above, it created a good connection.

I do not know what is wrong with v.3.0.10, or if it's just the files that anaconda.org has on their repository. I've posted something on the pyodbc github page also, but it doesn't look that active.

Anyway, thank you for everyone's help. I hope this saves someone some time.

pyODBC refuses to look for unixODBC, instead looks for iODBC

2 Answers2

Linked