3

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)")
samthaman
  • 83
  • 2
  • 11
  • 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

3 Answers3

1

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
cimentadaj
  • 1,414
  • 10
  • 23
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']
>>>
caot
  • 3,066
  • 35
  • 37
  • 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
0

Here how we can manage to work with conda without any changes in the linux system.(Maybe same instructions will work for Windows and MAC: Not testet in this case)

Let's start with create an environment with conda:

# conda create -n myodbc python=3.9
# conda activate myodbc
# conda install pyodbc psycopg psqlodbc

check that dirvers are available:

ls $CONDA_PREFIX/lib/psql*
outputs should have one ansi binary and one unicode binary
$CONDA_PREFIX/lib/psqlodbca.so  $CONDA_PREFIX/lib/psqlodbcw.so

If that the case installation is correct now we need to set some env variables into conda env to make sure odbcinst tool links to correct drivers. We set two env variables ODBCINI and ODBCSYSINI:

conda env config vars set ODBCSYSINI="$CONDA_PREFIX/etc" ODBCINI="$CONDA_PREFIX/etc/pgsql_odbc.ini"

For activate the variables deactivate conda and activate again:

conda deactivate
conda activate myodbc

Create the data source file for your specified drivers in this case PostgreSQL ODBC dirver(unicode version) will be created:

echo -e "[PostgreSQL]\ndescription = PostgreSQL ODBC driver (Unicode version)\ndriver = $CONDA_PREFIX/lib/psqlodbcw.so\ndebug = 0\nusagecount = 1" > $CONDA_PREFIX/etc/pgsql_odbc.ini

Show the configs for odbc drivers and data sources before installation of odbc drivers file:

Install the pgsql_odbc.ini:

odbcinst -i -d -f $CONDA_PREFIX/etc/pgsql_odbc.ini

Then check again that drivers have been linked to correct path of ini and binaries

odbcinst -j
Aydina
  • 1
  • 1