Pyodbc works in non-conda python, but fails to load drivers in conda

Question

I have a simple connection string for connection to a MSSQL server running in a local docker container. The code works fine in python 3.7 (not conda), but fails in any conda environment. I'm specifically interested in getting it to work in a Jupyter notebook.

I've recently re-installed Anaconda, but that seems unrelated as it still isn't working. My .bash_profile seems to be a bit of a mess, but I've been reluctant to make changes there before I'm sure about what I'm doing.

As best I can tell, my conda version of python can't find the pyodbc drivers that the other version of python is using.

this code works in any non-conda version of Python for me but fails elsewhere.

import pyodbc
drivers = [item for item in pyodbc.drivers()]
driver = drivers[-1]
print("driver:{}".format(driver))
server = '192.168.0.4'
database = 'XXXXXXX'
uid = 'sa'
pwd = 'XXXXXXXXXX'
con_string = f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={uid};PWD={pwd}'
print(con_string)
cnxn = pyodbc.connect(con_string)


cursor = cnxn.cursor()
print('connected')

cursor.execute('SELECT * FROM TB_STYLE_AUDIT')

for row in cursor:
    print(row)

expected results (shortened):

/usr/local/bin/python3.7 /Users/mycomputer/Documents/Pythonprojects/BuildingOldHistory/getHistoricaldata.py
driver:ODBC Driver 17 for SQL Server
DRIVER=ODBC Driver 17 for SQL Server;SERVER=192.168.0.4;PORT=1433;DATABASE=XXXXXXX;UID=sa;PWD=XXXXXXX
connected
(37962, 107, 555255, 662895, 689233, datetime.datetime(2016, 6, 8, 13, 22, 38), 1, '', '', '', -1.0, -10.0, -20.0, 'Sale', '1086504', '1088527', None, 18, None)

here is the error from conda:

IndexError                                Traceback (most recent call last)
<ipython-input-1-ea3728340faa> in <module>
      2 
      3 drivers = [item for item in pyodbc.drivers()]
----> 4 driver = drivers[-1]
      5 print("driver:{}".format(driver))
      6 server = '192.168.0.4'

IndexError: list index out of range

Editing to add that this code also works outside of conda:

import pyodbc

#drivers = [item for item in pyodbc.drivers()]
#driver = drivers[-1]
#print("driver:{}".format(driver))
driver = 'ODBC Driver 17 for SQL Server'
server = '192.168.0.4'
database = 'XXXXXXX'
uid = 'sa'
pwd = 'XXXXXXX'
con_string = f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={uid};PWD={pwd}'
print(con_string)
cnxn = pyodbc.connect(con_string)


cursor = cnxn.cursor()
print('connected')

cursor.execute('SELECT * FROM TB_STYLE_AUDIT')

for row in cursor:
    print(row)

but produces this error in a notebook:

---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
<ipython-input-2-3f7d96055440> in <module>
     11 con_string = f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={uid};PWD={pwd}'
     12 print(con_string)
---> 13 cnxn = pyodbc.connect(con_string)
     14 
     15 

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

have you configured your `.ini` files? https://support.microfocus.com/kb/doc.php?id=7021177 — gold_cy, Jun 11 '19 at 22:39
I didn't have to to get it to work outside of conda. I followed your link to the Microsoft site, is it worth attempting this code, or would that risk corrupting my working driver setup? `code` brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release brew update brew install msodbcsql mssql-tools — samthaman, Jun 11 '19 at 22:54
won’t know unless you try right? you have to find where brew installed the drivers and then point your ini files at that location — gold_cy, Jun 11 '19 at 23:11
I tried reinstalling them as suggested above but the driver file was still not found. — samthaman, Jun 11 '19 at 23:12
so I ran odbcinst -j and it produced a list of locations for file. `code` unixODBC 2.3.7 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /Users/mycomputer/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8 `code` — samthaman, Jun 11 '19 at 23:19
but if I try to view or edit the .odbc.ini file, it's not there — samthaman, Jun 11 '19 at 23:21

caot · Answer 1 · 2019-06-12T00:19:02.623

0

If it's OSX, please follow instruction at Drivers and Driver Managers. It is also helpful Install FreeTDS, unixODBC and pyodbc on OS X

The key issue is the configure file as the follows:

On drivers and instance, edit /usr/local/etc/odbcinst.ini:

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1

[unixodbc]
Description=unixodbc
ODBCInstLib=/usr/local/Cellar/unixodbc/2.3.1/lib/libodbcinst.dylib
DriverManagerEncoding=UTF-16

Test log:

$ python
Python 3.6.8 |Anaconda, Inc.| (default, Dec 29 2018, 19:04:46) 
[GCC 4.2.1 Compatible Clang 4.0.1 (tags/RELEASE_401/final)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.drivers()
['FreeTDS', 'unixodbc']
>>>

edited Jun 12 '19 at 00:19

answered Jun 11 '19 at 22:28

caot

2,706
30
34

Agreed, yet after banging my head against it all morning I found it either here or on github and it somehow does work in my non-conda pythons. I'll update the question to reflect that I've tried directly naming the driver in the connection string as well – samthaman Jun 11 '19 at 22:30
do you know how list indexing works? `-1` is a valid index, it grabs the last item in the list, so a list with one item will have both `drivers[0]` and `drivers[-1]` return the same result @caot. `drivers` is probably empty is actually what is happening – gold_cy Jun 11 '19 at 22:31
to clarify, I wasn't clear on why the list indexing was needed in this case, but I do understand that it was looking for the last list entry. Please see the additional error message I added to the question – samthaman Jun 11 '19 at 22:36
@samthaman my comment was directed at the person who posted the answer – gold_cy Jun 11 '19 at 22:37
then you need to install odbc drivers – caot Jun 11 '19 at 22:47
So I've spent several more hours digging into this. When i edit /usr/local/etc/odbcinst.ini outside of my conda envrionment, I can see drivers in the file, however when I "conda activate" and then run the test code, I see this >>> import pyodbc >>> pyodbc.drivers() [] I'm assuming that my conda version of pydobc is installing drivers in another location. If that seems valid, how do I go about figuring out where that is and how to fix it? – samthaman Jun 12 '19 at 20:48
What's your os? – caot Jun 12 '19 at 21:13
Mac OSX 10.14.5 – samthaman Jun 13 '19 at 05:00
My test is in OSX, and it worked. You can try it this way. Get the `$ env` of the working and the failed one, and compare the difference between both of them. You will find the solution. – caot Jun 13 '19 at 17:46

score 0 · Answer 2 · answered Jun 23 '21 at 13:35

0

For me it was a problem of system links being broken. I fixed it by following this.

Running this fixed it:

sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini

answered Jun 23 '21 at 13:35

cimentadaj

1,264
9
20

Pyodbc works in non-conda python, but fails to load drivers in conda

2 Answers2