如何结合SQLAlchemy引擎与pandas read_sql()正确执行psycopg2 SQL对象？

问题背景 #

运行Python 3.9代码时收到以下警告：

/usr/local/lib/python3.9/site-packages/pandas/io/sql.py:761:
  UserWarning:
    pandas only support SQLAlchemy connectable(engine/connection) or
    database string URI or sqlite3 DBAPI2 connectionother DBAPI2
    objects are not tested, please consider using SQLAlchemy

原代码片段：

import pandas as pd
from psycopg2 import sql

fields = ('object', 'category', 'number', 'mode')

query = sql.SQL("SELECT {} FROM categories;").format(
    sql.SQL(', ').join(map(sql.Identifier, fields))
)

df = pd.read_sql(
    sql=query,
    con=connector() # 自定义函数，返回psycopg2连接对象
)

切换为SQLAlchemy引擎后，抛出sqlalchemy.exc.ObjectNotExecutableError错误，临时将psycopg2 SQL对象转为字符串的方案不够优雅，需要规范方式结合SQLAlchemy引擎与Pandas构建DataFrame。

相关版本信息：

• Python: 3.9
• Pandas: '1.4.3'
• SQLAlchemy: '1.4.35'
• psycopg2: '2.9.3 (dt dec pq3 ext lo64)'

规范解决方案 #

方法1：使用SQLAlchemy Core API构建查询（推荐） #

直接用SQLAlchemy原生API安全格式化SQL标识符，替代psycopg2的SQL构造器，完美兼容Pandas的read_sql：

import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, select

# 初始化SQLAlchemy引擎（替换为你的数据库连接URL）
engine = create_engine("postgresql+psycopg2://user:password@host:port/dbname")

fields = ('object', 'category', 'number', 'mode')

# 反射categories表结构（自动获取字段信息）
metadata = MetaData()
categories_table = Table('categories', metadata, autoload_with=engine)

# 构建查询语句，指定要选择的字段
query = select([categories_table.c[field] for field in fields])

# 读取查询结果为DataFrame
df = pd.read_sql(query, con=engine)

如果不想反射表结构，也可以手动构造SQL字符串（确保字段名可信，避免注入风险）：

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg2://user:password@host:port/dbname")

fields = ('object', 'category', 'number', 'mode')
# 格式化字段名，用双引号包裹避免关键字冲突
formatted_fields = ", ".join([f'"{field}"' for field in fields])
# 用SQLAlchemy的text对象包装SQL字符串
query = text(f"SELECT {formatted_fields} FROM categories;")

df = pd.read_sql(query, con=engine)

方法2：兼容psycopg2 SQL对象的过渡方案 #

如果需要保留原psycopg2的SQL构造逻辑，可将其转换为SQLAlchemy可识别的text对象：

import pandas as pd
from psycopg2 import sql
from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg2://user:password@host:port/dbname")

fields = ('object', 'category', 'number', 'mode')

query = sql.SQL("SELECT {} FROM categories;").format(
    sql.SQL(', ').join(map(sql.Identifier, fields))
)

# 将psycopg2的SQL对象转为字符串，再用text包装
sqlalchemy_query = text(str(query))

df = pd.read_sql(sqlalchemy_query, con=engine)

关键说明 #

• Pandas的read_sql要求SQL参数为SQLAlchemy可执行对象（如select语句、text对象）或纯字符串，直接传入psycopg2的SQL对象会触发错误，因为SQLAlchemy无法识别该类型。
• 推荐使用SQLAlchemy Core API，它原生支持数据库交互，能自动处理标识符转义、参数化查询等安全问题，无需依赖psycopg2的SQL模块。

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