如何将字符串类型DataFrame列传入SQL的WHERE子句并实现GROUP BY、ORDER BY

• 直接触发语法错误的核心是引号嵌套冲突：你用双引号"包裹整个SQL字符串，SQL内部给字段取别名as "Shipdate"也用了双引号，Python解析时会把Shipdate左侧的双引号识别为整个SQL字符串的结束符，后续内容无法解析直接报错。
• 额外存在逻辑冲突：你提前将序列号转为了tuple，但SQL拼接时又用了参数占位符?的写法，两种用法混用会导致后续执行SQL时参数传递失败。

方案1：直接拼接IN条件（适合小数据量、序列号无特殊字符场景） #

直接将序列号格式化为SQL可识别的带单引号的字符串拼接进语句：

# 给每个序列号加单引号
sn_str = ','.join([f"'{sn}'" for sn in df['SerialNum'].tolist()])
# SQL外层用单引号包裹，避免和内部别名的双引号冲突
sqlsnr = f'SELECT DISTINCT PSHIP.SERIAL_NUMBER,PSHIP.MATERIAL_NUMBER,max(PSHIP.SHIPMENT_DATE) as "Shipdate" FROM P_SHIPMENT_SERIAL_NUMBER PSHIP LEFT OUTER JOIN P_SHIPMENT_SALES SALES ON (PSHIP.SHIPMENT_IDENTIFIER=SALES.SHIPMENT_IDENTIFIER) AND PSHIP.SALES_ORDER_NUMBER=SALES.SALES_ORDER_NUMBER WHERE SERIAL_NUMBER IN ({sn_str}) GROUP BY PSHIP.SERIAL_NUMBER,PSHIP.MATERIAL_NUMBER Order by PSHIP.SERIAL_NUMBER,PSHIP.MATERIAL_NUMBER,max(PSHIP.SHIPMENT_DATE) asc'

方案2：参数化查询（更安全，避免SQL注入，推荐） #

用占位符传参的方式执行，不需要手动处理引号：

sn_list = df['SerialNum'].tolist()
# 生成对应数量的参数占位符
placeholders = ','.join(['?'] * len(sn_list))
# 外层用单引号包裹SQL
sqlsnr = f'SELECT DISTINCT PSHIP.SERIAL_NUMBER,PSHIP.MATERIAL_NUMBER,max(PSHIP.SHIPMENT_DATE) as "Shipdate" FROM P_SHIPMENT_SERIAL_NUMBER PSHIP LEFT OUTER JOIN P_SHIPMENT_SALES SALES ON (PSHIP.SHIPMENT_IDENTIFIER=SALES.SHIPMENT_IDENTIFIER) AND PSHIP.SALES_ORDER_NUMBER=SALES.SALES_ORDER_NUMBER WHERE SERIAL_NUMBER IN ({placeholders}) GROUP BY PSHIP.SERIAL_NUMBER,PSHIP.MATERIAL_NUMBER Order by PSHIP.SERIAL_NUMBER,PSHIP.MATERIAL_NUMBER,max(PSHIP.SHIPMENT_DATE) asc'

# 执行SQL时传入参数即可，示例用pandas读数据：
# res_df = pd.read_sql(sqlsnr, con=db_connection, params=sn_list)

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