如何在Python中同时用SQL访问数据库与DataFrame？

当然可以实现这种跨数据源的SQL查询需求，下面是几种实用的实现方式：

方法1：用DuckDB直接关联 #

DuckDB支持直接读取Pandas DataFrame，同时能连接远程SQL数据库，无需额外导入导出操作，写法最贴近你给出的示例：

import duckdb
import pandas as pd

# 假设已有本地DataFrame
local_df = pd.DataFrame({'key': [1,2,3], 'value': ['a','b','c']})

# 初始化DuckDB连接
conn = duckdb.connect()

# 加载SQL Server扩展并连接远程数据库（其他数据库可对应调整扩展）
conn.execute("INSTALL sqlserver;")
conn.execute("LOAD sqlserver;")

# 将远程数据库的表注册为视图
conn.execute("""
CREATE VIEW remote_table AS
SELECT * FROM sqlserver://用户名:密码@服务器地址/数据库名/架构名/表名
""")

# 将本地DataFrame注册为可查询的表
conn.register('local_df', local_df)

# 执行你需要的JOIN查询
result_df = conn.execute("""
SELECT a.*, b.* 
FROM remote_table AS a 
LEFT JOIN local_df AS b 
ON a.key = b.key
""").fetchdf()

print(result_df)

方法2：将DataFrame上传到SQL服务器临时表后查询 #

如果数据量较大，直接在服务器端执行JOIN效率更高，可把DataFrame上传到SQL服务器的临时表，再和原有表关联：

import pandas as pd
import pyodbc

# 连接SQL Server数据库
conn_str = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=服务器地址;DATABASE=数据库名;UID=用户名;PWD=密码'
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# 创建临时表
cursor.execute("""
CREATE TABLE #temp_local_df (
    key INT,
    value VARCHAR(50)
)
""")

# 批量插入DataFrame数据
for _, row in local_df.iterrows():
    cursor.execute("INSERT INTO #temp_local_df (key, value) VALUES (?, ?)", row['key'], row['value'])
conn.commit()

# 执行JOIN查询
cursor.execute("""
SELECT a.*, b.* 
FROM 原有表名 AS a 
LEFT JOIN #temp_local_df AS b 
ON a.key = b.key
""")

result_df = pd.DataFrame(cursor.fetchall(), columns=[desc[0] for desc in cursor.description])
print(result_df)

# 清理并关闭连接
cursor.close()
conn.close()

方法3：用SQLAlchemy + 内存SQLite关联 #

如果你的项目已经在用SQLAlchemy，可以将DataFrame写入内存SQLite数据库，再结合远程数据库数据执行查询：

from sqlalchemy import create_engine, text
import pandas as pd

# 将本地DataFrame写入内存SQLite
sqlite_engine = create_engine('sqlite:///:memory:')
local_df.to_sql('local_df', sqlite_engine, index=False)

# 连接远程SQL Server数据库
remote_engine = create_engine('mssql+pyodbc://用户名:密码@服务器地址/数据库名?driver=ODBC+Driver+17+for+SQL+Server')

# 把远程表数据导入内存SQLite，再执行JOIN
with remote_engine.connect() as remote_conn, sqlite_engine.connect() as local_conn:
    # 读取远程表数据
    remote_data = remote_conn.execute(text("SELECT * FROM 原有表名")).fetchall()
    # 写入内存SQLite作为临时表
    pd.DataFrame(remote_data, columns=['key', '字段1', '字段2']).to_sql('remote_table', local_conn, index=False)
    
    # 执行JOIN查询
    result = local_conn.execute(text("""
        SELECT a.*, b.* 
        FROM remote_table AS a 
        LEFT JOIN local_df AS b 
        ON a.key = b.key
    """)).fetchall()

result_df = pd.DataFrame(result)
print(result_df)

内容的提问来源于stack exchange，提问作者lithic