Python Pandas执行Oracle SQL脚本时重复变量传参优化问询

嘿，这事儿好办！你可以利用Python字符串格式化的索引占位符或者关键字占位符来实现只传一次变量就填充所有对应位置的需求，不用重复传同一变量。下面给你两种具体实现方式：

方法1：使用索引占位符 #

把SQL里需要替换的内容分别用{0}（对应IO_ID）、{1}（对应所有'2018-05-01'）、{2}（对应所有'2018-05-30'）标记，这样调用format()时只需要按顺序传一次变量即可：

修改后的SQL脚本 #

SELECT * FROM (
    SELECT 
        SUBSTR(PLACEMENT_DESC,1,INSTR(PLACEMENT_DESC, '.', 1)-1) AS PLACEMENT#,
        TO_CHAR(SDATE, 'YYYY-MM-DD') AS START_DATE,
        TO_CHAR(EDATE, 'YYYY-MM-DD') AS END_DATE,
        INITCAP(CREATIVE_DESC) AS PLACEMENT_NAME,
        COST_TYPE_DESC AS COST_TYPE,
        UNIT_COST AS UNIT_COST,
        BUDGET AS PLANNED_COST,
        BOOKED_QTY AS BOOKED_IMP#BOOKED_ENG
    FROM TFR_REP.SUMMARY_MV 
    WHERE (IO_ID = {0}) 
    AND (DATA_SOURCE = 'KM') 
    AND (TO_CHAR(SDATE, 'YYYY-MM-DD') BETWEEN 
        (CASE WHEN '{1}' BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(SDATE, 'YYYY-MM-DD') 
        ELSE (CASE WHEN '{1}' < TO_CHAR(SDATE, 'YYYY-MM-DD') AND '{1}' <= TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(SDATE, 'YYYY-MM-DD') 
        ELSE TO_CHAR(SDATE, 'YYYY-MM-DD') END) END) 
    AND (CASE WHEN '{2}' BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN '{2}' 
        ELSE (CASE WHEN '{2}' > TO_CHAR(EDATE, 'YYYY-MM-DD') AND '{1}' <= TO_CHAR(SDATE, 'YYYY-MM-DD') THEN TO_CHAR(EDATE, 'YYYY-MM-DD') 
        ELSE TO_CHAR(EDATE, 'YYYY-MM-DD') END) END)) 
    AND (TO_CHAR(EDATE, 'YYYY-MM-DD') BETWEEN 
        (CASE WHEN '{1}' BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN '{1}' 
        ELSE (CASE WHEN '{1}' < TO_CHAR(SDATE, 'YYYY-MM-DD') AND '{1}' <= TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(SDATE, 'YYYY-MM-DD') 
        ELSE TO_CHAR(SDATE, 'YYYY-MM-DD') END) END) 
    AND (CASE WHEN '{2}' BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(EDATE, 'YYYY-MM-DD') 
        ELSE (CASE WHEN '{2}' > TO_CHAR(EDATE, 'YYYY-MM-DD') AND '{1}' <= TO_CHAR(SDATE, 'YYYY-MM-DD') THEN TO_CHAR(EDATE, 'YYYY-MM-DD') 
        ELSE TO_CHAR(EDATE, 'YYYY-MM-DD') END) END)) 
    AND CREATIVE_DESC IN(SELECT DISTINCT CREATIVE_DESC FROM TFR_REP.SUMMARY_MV)
) 
WHERE Placement_Name Not LIKE '%Pre-Roll%' and Placement_Name Not LIKE '%Pre鈥揜oll%'

Python调用示例 #

import pandas as pd
import cx_Oracle  # 或者你使用的其他Oracle连接库

# 定义需要传入的变量
io_id = 12345  # 替换为你的实际IO_ID值
start_date = '2018-05-01'
end_date = '2018-05-30'

# 格式化SQL语句
sql_template = """上面的完整SQL代码"""
formatted_sql = sql_template.format(io_id, start_date, end_date)

# 连接数据库并执行查询
conn = cx_Oracle.connect('你的用户名/密码@主机:端口/服务名')
df = pd.read_sql(formatted_sql, conn)
conn.close()

方法2：使用关键字占位符（可读性更强） #

如果你觉得索引数字容易搞混，可以用自定义的关键字来标记占位符，这样代码更清晰，调用时按关键字传参就行：

修改后的SQL脚本（关键部分示例） #

WHERE (IO_ID = {io_id}) 
AND (DATA_SOURCE = 'KM') 
AND (TO_CHAR(SDATE, 'YYYY-MM-DD') BETWEEN 
    (CASE WHEN '{start_date}' BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(SDATE, 'YYYY-MM-DD') 
    ELSE (CASE WHEN '{start_date}' < TO_CHAR(SDATE, 'YYYY-MM-DD') AND '{start_date}' <= TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(SDATE, 'YYYY-MM-DD') 
    ELSE TO_CHAR(SDATE, 'YYYY-MM-DD') END) END) 
AND (CASE WHEN '{end_date}' BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN '{end_date}' 
-- 其余部分和原SQL结构一致，只替换对应的日期占位符

Python调用示例 #

formatted_sql = sql_template.format(io_id=12345, start_date='2018-05-01', end_date='2018-05-30')

安全提醒 #

虽然str.format()能快速解决你的需求，但要注意SQL注入风险！如果变量来自用户输入或不可信来源，更安全的做法是使用pandas的参数化查询，结合read_sql的params参数（Oracle通常使用:参数名作为占位符）：

# 参数化查询示例（更安全）
param_sql = """
SELECT * FROM (...) 
WHERE IO_ID = :io_id 
AND (TO_CHAR(SDATE, 'YYYY-MM-DD') BETWEEN 
    (CASE WHEN :start_date BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(SDATE, 'YYYY-MM-DD') 
    ELSE (CASE WHEN :start_date < TO_CHAR(SDATE, 'YYYY-MM-DD') AND :start_date <= TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(SDATE, 'YYYY-MM-DD') 
    ELSE TO_CHAR(SDATE, 'YYYY-MM-DD') END) END) 
-- 其余部分替换所有日期占位符为:start_date和:end_date
"""
df = pd.read_sql(param_sql, conn, params={'io_id': io_id, 'start_date': start_date, 'end_date': end_date})

这样既避免了重复传参，又能有效防范SQL注入，推荐在生产环境中使用这种方式！

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