python - Pyodbc 在非 conda python 中工作，但无法在 conda 中加载驱动程序

Question

我有一个简单的连接字符串，用于连接到本地 Docker 容器中运行的 MSSQL 服务器。该代码在 python 3.7(不是 conda)中工作正常，但在任何 conda 环境中都会失败。我特别感兴趣的是让它在 Jupyter 笔记本上运行。

我最近重新安装了 Anaconda，但这似乎无关，因为它仍然无法工作。我的 .bash_profile 似乎有点乱，但在确定自己在做什么之前，我一直不愿意在那里进行更改。

据我所知，我的 conda 版本的 python 找不到其他版本的 python 正在使用的 pyodbc 驱动程序。

这段代码对我来说可以在任何非 conda 版本的 Python 中工作，但在其他地方会失败。

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)

预期结果(缩短):

/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)

这是来自 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

编辑以添加此代码也可以在 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)

但在笔记本中产生此错误:

---------------------------------------------------------------------------
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)")

Answer 1

对我来说，这是系统链接损坏的问题。我通过以下 this 修复了它.

运行此修复它:

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