python - Pyodbc 在非 conda python 中工作,但无法在 conda 中加载驱动程序
问题描述
我有一个简单的连接字符串,用于连接到在本地 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)")
解决方案
如果是 OSX,请按照Drivers and Driver Managers的说明进行操作。在 OS X 上安装 FreeTDS、unixODBC 和 pyodbc也很有帮助
关键问题是配置文件如下:
在驱动程序和实例上,编辑 /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
测试日志:
$ 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']
>>>
推荐阅读
- python - ball animation is really junky while running pong game
- c++ - 在自定义哈希表头文件中包含自定义链接列表
- sql - 严格按顺序将两个表连接在一起
- excel - 旋转图片然后设置旋转后的属性
- android - 约束布局下的ListView在预览中消失
- python - 列表和嵌套列表的简单点积以单独列表中的索引为条件
- winapi - 消息和通知有什么区别?
- powerbi - 使用 SelectedValue 时的慢速测量/视觉
- amazon-web-services - AWS Step Function:如何在 step 函数中调用相同 lambda 的多个实例
- doctrine - Doctrine EntityManager 使用 Swoole 关闭