使用pd.read_sql读取MSSQL含OFFSET FETCH的查询返回全NaN问题

环境配置 #

• Windows 系统
• SQL Server 2022
• Python 3.11
• Pandas 2.0.2
• SQLAlchemy 1.4.41

执行的联表查询 #

使用包含OFFSET FETCH的分页联表查询语句：

query = f"select * from A01ASSMF as MF LEFT OUTER JOIN A01AEXT as A01A ON MF.A01_ASSETID = A01A.A01A_ASSETID LEFT OUTER JOIN A04INVIT as A04 ON MF.A01_ASSETID = A04.A04_ASSETID ORDER BY MF.A01_ASSETID OFFSET {row_from} ROWS FETCH NEXT {row_count} ROWS ONLY"

问题现象 #

• 用pd.read_sql(query, conn)执行查询，返回的DataFrame行数符合预期（5000行），但所有单元格数据均为NaN
• 改用SQLAlchemy的fetchall()方法获取数据后手动构造DataFrame，能正常读取到有效数据

原因分析 #

1. 重复列名冲突：三张表联查时存在同名列（比如不同表中有名称相同的字段），Pandas解析结果时无法正确映射这些重复列，导致数据加载失败全为NaN。而SQLAlchemy的fetchall()返回元组列表，不处理列名冲突，直接保留原始数据，手动构造DataFrame时可正常识别。
2. 版本兼容性bug：当前使用的Pandas 2.0.2与SQLAlchemy 1.4.41组合，在处理SQL Server的OFFSET FETCH查询结果时，可能存在底层数据类型解析或结果集处理的兼容性问题，导致Pandas无法正确读取数据。
3. 类型推断逻辑出错：LEFT JOIN可能产生大量NULL值，Pandas的自动类型推断逻辑误将所有字段识别为NULL类型，最终填充为NaN。而fetchall()直接读取原始结果，不受Pandas类型推断的影响。

解决办法 #

方法1：显式指定字段并处理重复列名 #

避免使用select *，手动列出需要的字段，给重复列名添加别名：

query = f"""
SELECT 
    MF.A01_ASSETID AS MF_ASSETID,
    MF.字段名1,
    A01A.A01A_ASSETID AS A01A_ASSETID,
    A01A.字段名2,
    A04.A04_ASSETID AS A04_ASSETID,
    A04.字段名3
FROM A01ASSMF as MF 
LEFT OUTER JOIN A01AEXT as A01A ON MF.A01_ASSETID = A01A.A01A_ASSETID 
LEFT OUTER JOIN A04INVIT as A04 ON MF.A01_ASSETID = A04.A04_ASSETID 
ORDER BY MF.A01_ASSETID 
OFFSET {row_from} ROWS FETCH NEXT {row_count} ROWS ONLY
"""

让Pandas能识别唯一列名，避免映射冲突。

方法2：升级依赖版本 #

尝试升级Pandas到2.1.x及以上，或SQLAlchemy到2.x版本，修复潜在兼容性bug：

pip install --upgrade pandas sqlalchemy

方法3：通过SQLAlchemy结果集构造DataFrame #

保留fetchall()的可靠读取方式，手动构造DataFrame：

from sqlalchemy import text

with conn.begin():
    result = conn.execute(text(query))
    rows = result.fetchall()
    columns = [col.name for col in result.keys()]
df = pd.DataFrame(rows, columns=columns)

绕过Pandasread_sql的内部处理逻辑，直接使用原始结果集。

方法4：调整Pandas类型推断参数 #

在pd.read_sql中指定类型推断参数，避免识别错误：

df = pd.read_sql(query, conn, dtype_backend='numpy_nullable')

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