Pandas read_sql_query函数报错修复：版本兼容与索引越界问题

本地环境使用pandas 1.5.2、numpy 1.23.5时代码正常运行，但服务器环境为pandas 0.23.0、numpy 1.16.5时，pd.read_sql_query抛出索引越界错误，相关代码与报错如下：

问题代码 #

def fetchDbTable(candidate_id):
    start_time = time.time()
    # candidate_id = 793
    schema = env_config.get("DEV_DB_SCHEMA_PUBLIC")

    # Create Db engine
    engine = createEngine(
        user=env_config.get("DEV_DB_USER"),
        pwd=env_config.get("DEV_DB_PASSWORD"),
        host=env_config.get("DEV_DB_HOST"),
        db=env_config.get("DEV_DB_NAME"),
    )


    # Create query for resume
    query_resume = (
        """
        select
        a.*,
        b.*,
        c.*

        from
        """
        + schema
        + """.resume_intl_candidate_info a,
        """
        + schema
        + """.resume_intl_candidate_resume b,
        """
        + schema
        + """.resume_intl_candidate_work_experience c

        where
        a.c_id = b.c_id_fk_id
        --and a.c_id = c.c_id_fk_id
        and b.r_id = c.r_id_fk_id
        and b.active_flag = 't'
        and a.c_id = """
        + str(candidate_id)
    )

    # Fetch database
    df_resume = pd.read_sql_query(query_resume, con=engine)

报错栈 #

Traceback (most recent call last):
  File "/home/vinoth/Documents/Req_Intelligence-master/Req_Intelligence-master/req_intl/resume_intl/extractedfields.py", line 483, in save_create_document
    meta_df = fetchDbTable(candidate_info.c_id)
  File "/home/vinoth/Documents/Req_Intelligence-master/Req_Intelligence-master/req_intl/resume_intl/candidate_meta_info.py", line 132, in fetchDbTable
    df_resume = pd.read_sql_query(query_resume, con=engine)
  File "/home/vinoth/.local/lib/python3.10/site-packages/pandas/io/sql.py", line 397, in read_sql_query
    return pandas_sql.read_query(
  File "/home/vinoth/.local/lib/python3.10/site-packages/pandas/io/sql.py", line 1575, in read_query
    frame = _wrap_result(
  File "/home/vinoth/.local/lib/python3.10/site-packages/pandas/io/sql.py", line 151, in _wrap_result
    frame = _parse_date_columns(frame, parse_dates)
  File "/home/vinoth/.local/lib/python3.10/site-packages/pandas/io/sql.py", line 126, in _parse_date_columns
    for col_name, df_col in data_frame.items():
  File "/home/vinoth/.local/lib/python3.10/site-packages/pandas/core/frame.py", line 1325, in items
    yield k, self._ixs(i, axis=1)
  File "/home/vinoth/.local/lib/python3.10/site-packages/pandas/core/frame.py", line 3728, in _ixs
    col_mgr = self._mgr.iget(i)
  File "/home/vinoth/.local/lib/python3.10/site-packages/pandas/core/internals/managers.py", line 1136, in iget
    values = block.iget(self.blklocs[i])
  File "/home/vinoth/.local/lib/python3.10/site-packages/pandas/core/internals/blocks.py", line 834, in iget
    return self.values[i]  # type: ignore[index]
  File "/home/vinoth/.local/lib/python3.10/site-packages/pandas/core/arrays/datetimelike.py", line 358, in __getitem__
    "Union[DatetimeLikeArrayT, DTScalarOrNaT]", super().__getitem__(key)
  File "/home/vinoth/.local/lib/python3.10/site-packages/pandas/core/arrays/_mixins.py", line 289, in __getitem__
    result = self._ndarray[key]
IndexError: index 1 is out of bounds for axis 0 with size 1

问题根源 #

低版本pandas（0.23.x）在自动解析日期列时存在bug：当查询结果中包含单个日期类型列，且该列数据为空或内部存储结构不匹配时，解析逻辑会触发索引越界错误。

修复方案 #

1. 关闭自动日期解析（优先推荐） #

在read_sql_query中添加parse_dates=False参数，跳过自动日期解析，避免老版本的bug：

df_resume = pd.read_sql_query(query_resume, con=engine, parse_dates=False)

如果后续需要处理日期列，可手动指定列进行解析：

# 示例：手动解析指定日期列
df_resume['target_date_col'] = pd.to_datetime(df_resume['target_date_col'], errors='coerce')

2. 优化SQL查询 #

• 避免使用select *，明确指定需要的列，排除不必要的日期列（如果不需要的话）
• 或者在SQL中将日期列转为字符串，绕过pandas的日期解析逻辑：

select
    a.c_id,
    a.candidate_name,
    cast(b.resume_create_time as text) as resume_create_time,
    -- 其他需要的列
from {schema}.resume_intl_candidate_info a
join {schema}.resume_intl_candidate_resume b on a.c_id = b.c_id_fk_id
join {schema}.resume_intl_candidate_work_experience c on b.r_id = c.r_id_fk_id
where b.active_flag = 't'
  and a.c_id = %s

3. 升级依赖（若环境允许） #

如果服务器环境允许，将pandas升级到0.25及以上版本，该bug在后续版本中已被修复。

额外优化：解决SQL注入风险 #

原代码直接拼接candidate_id到SQL中存在注入风险，改用参数化查询：

query_resume = f"""
select a.*, b.*, c.*
from {schema}.resume_intl_candidate_info a
join {schema}.resume_intl_candidate_resume b on a.c_id = b.c_id_fk_id
join {schema}.resume_intl_candidate_work_experience c on b.r_id = c.r_id_fk_id
where b.active_flag = 't'
  and a.c_id = %s
"""
# 使用params传递参数，避免注入
df_resume = pd.read_sql_query(query_resume, con=engine, params=[candidate_id], parse_dates=False)

注：不同数据库的参数占位符不同，PostgreSQL/MySQL用%s，SQLite用?，请根据实际数据库调整。

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