如何实现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
相关产品推荐
相关产品推荐

