You need to enable JavaScript to run this app. 如何实现pandas.read_sql方法的参数自动传入
优惠活动
大模型
产品
解决方案
定价
更多

如何实现pandas.read_sql方法的参数自动传入

我想创建一套类似f-string工作原理的参数自动传递方案,可以直接通过locals()这类方法自动传递参数。

当前实现方式

import pandas as pd

def my_func(conn, string_id, date, integer_ids):
    sql = f"""    
    select * from TABLE a
    where STRING_ID = '{string_id}'
    and DATE = {date}
    and INTEGER_ID in ({','.join(map(str, integer_ids))})"""
    df = pd.read_sql(sql, conn)
    return df

这种方案的问题在于,无法直接将SQL复制粘贴到SQL Developer等同类工具中直接运行,因此想改用参数化查询的实现方式。

目前这种实现思路存在两个问题:

  • 参数必须是字面量,无法直接传入列表类型参数
  • 需要手动构建参数字典,无法直接传入locals()这类动态变量集合

理想实现方式(当前无法正常运行)

import pandas as pd

def my_func(conn, string_id, date, integer_ids):
    sql = """    
    select * from TABLE
    where STRING_ID = :string_id
    and DATE = :date
    and INTEGER_ID in :integer_ids"""
    df = pd.read_sql(sql, conn, params=locals())
    return df

可行解决方案

def read_sql(sql, conn, params):
    # Finds all words following a ":" sign in the sql
    for p in re.findall(':(\w+)', sql):
        if isinstance(params.get(p), (tuple, list)):
            ext_params = {f'{p}_{i:03d}': p_i for i, p_i in enumerate(params.get(p))}
            sql = sql.replace(f':{p}', f"(:{', :'.join(ext_params)})")
            params.update(ext_params)

    sql_text = sqlalchemy.text(sql)
    return pd.read_sql(sql_text, conn, params=params)


def my_func(conn, string_id, date, integer_ids):
    sql = """    
    select * from TABLE
    where STRING_ID = :string_id
    and DATE = :date
    and INTEGER_ID in :integer_ids"""
    df = read_sql(sql, conn, locals())
    return df

优化扩展方案(适配列表元素超过1000个的场景)

def generate_sql(sql: str, params: dict = None, param_key: str = ':') -> List[Tuple[sqlalchemy.text, dict]]:
    if params is None:
        params = dict()
    max_sql_params = 1000

    out = []
    # Finds all words following a ":" sign in the query
    for p in set(re.findall(f"{param_key}(\w+)", sql)):
        if isinstance(params.get(p), (tuple, list, np.ndarray)):
            # Recursively call function for variables with more than 1000 elements
            if len(params[p]) > max_sql_params:
                new_params = params.copy()  # NB: Shallow copy sufficient as param keys are tuples, lists or arrays
                new_params[p] = params[p][max_sql_params:]
                out.extend(generate_sql(sql=sql, params=new_params, param_key=param_key))
            extra_params = {f"{p}_{i:03d}": p_i for i, p_i in enumerate(params[p][:max_sql_params])}
            sql = sql.replace(f":{p}", f"(:{', :'.join(extra_params)})")
            params.update(extra_params)

    sql_text = sqlalchemy.text(sql)
    out.append((sql_text, params))
    return out


def read_sql(sql: str, conn: sqlalchemy.engine, params: dict = None) -> pd.DataFrame:
    sql_tuples = generate_sql(sql=sql, params=params)
    df = pd.concat(pd.read_sql(sql=s, con=conn, params=p) for s, p in sql_tuples)

    return df

内容的提问来源于stack exchange,提问作者oskros

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.19 01:36:06