python - 如何使用 sqlalchemy+pyodbc 和 MS SQL Server 中的多个数据库为 pandas read_sql 创建 sql alchemy 连接？

Question

我正在尝试使用“pandas.read_sql_query”将数据从 MS SQL Server 复制到 pandas DataFrame 中。我需要在我的 SQL 查询中进行多次连接。正在连接的表在同一台服务器上，但在不同的数据库中。我传递给 pandas 的查询在 MS SQL Server Management Studio 中运行良好。在 Jupyter Notebook 中，我尝试像这样查询数据(为了使事情可读，查询本身被简化为仅 2 个连接并使用了通用名称):

import pandas as pd
import sqlalchemy as sql
import pyodbc

server = '100.10.10.10'
driver = 'SQL+Server+Native+Client+11.0'
myQuery = '''SELECT first.Field1, second.Field2
           FROM db1.schema.Table1 AS first
           JOIN db2.schema.Table2 AS second
           ON first.Id = second.FirstId
           '''
engine = sql.create_engine('mssql+pyodbc://{}?driver={}'.format(server, driver))
df = pd.read_sql_query(myQuery, engine)

这不起作用并返回错误:

DBAPIError: (pyodbc.Error) ('IM010', '[IM010] [Microsoft][��������� ��������� ODBC] ������� ������� ��� ��������� ������ (0) (SQLDriverConnect)')

似乎问题出在引擎中，它不包含有关数据库的信息，因为在下一种代码中一切正常，我在引擎中包含数据库:

myQuery = 'select Field1 from schema.Table1'
db = 'db1'
engine = sql.create_engine('mssql+pyodbc://{}/{}?driver={}'.format(server, db, driver))
df = pd.read_sql_query(myQuery, engine)

但是如果我不在引擎中包含数据库，但像上面的连接代码一样中断，而是像这样将它添加到查询中:

myQuery = 'select Field1 from db1.schema.Table1'
engine = sql.create_engine('mssql+pyodbc://{}?driver={}'.format(server, 
driver))
df = pd.read_sql_query(myQuery, engine)

那么我应该如何在 pandas.read_sql_query 中指定 'sql' 和 'con' 参数在这种情况下，当我需要连接来自不同数据库但同一服务器的表时？

附言我只有对我正在连接的这台服务器的读取权限。我无法创建新表或 View 或类似的东西。

更新:MS SQL Server版本为2008 R2。

更新 2:我正在使用 Python 3.6 和 Windows 10。

Answer 1

所以我找到了一个解决方法:使用 pymssql 而不是 pyodbc(在导入语句和引擎中)。它允许您使用数据库名称构建连接，而无需在引擎中指定它们。并且在这种情况下无需指定驱动程序。

如果您使用 Python 3.6 可能会出现问题，Python 3.6 还不受 pymssql 官方支持，但您可以找到适用于您的 Python 3.6 的非官方 wheels here .它按我的查询预期的那样工作。

这里是带有连接的原始代码，重建后可以与 pymssql 一起使用:

import pandas as pd
import sqlalchemy as sql
import pymssql

server = '100.10.10.10'
myQuery = '''SELECT first.Field1, second.Field2
           FROM db1.schema.Table1 AS first
           JOIN db2.schema.Table2 AS second
           ON first.Id = second.FirstId'''
engine = sql.create_engine('mssql+pymssql://{}'.format(server))
df = pd.read_sql_query(myQuery, engine)

至于非官方的 wheels，你需要从我上面给出的链接下载 Python 3.6 的文件，然后 cd 到下载文件夹并运行 pip install wheels 其中 'wheels' 是名称车轮文件。

更新:

其实pyodbc也是可以的。我不确定这是否适用于任何 SQL Server 设置，但在我将“master”设置为引擎中的数据库后，一切都对我有用。生成的代码如下所示:

import pandas as pd
import sqlalchemy as sql
import pyodbc

server = '100.10.10.10'
driver = 'SQL+Server'
db = 'master'
myQuery = '''SELECT first.Field1, second.Field2
           FROM db1.schema.Table1 AS first
           JOIN db2.schema.Table2 AS second
           ON first.Id = second.FirstId'''
engine = sql.create_engine('mssql+pyodbc://{}/{}?driver={}'.format(server, db, driver))
df = pd.read_sql_query(myQuery, engine)