Python使用prestodb传数字列表作为查询参数报类型不匹配错误如何解决

• 报错是因为你直接将元组/列表对象用format拼接进SQL时，生成的IN条件语法不符合Presto要求：你写的ID in ({list_id})拼接后会变成ID in ((1, 2, 3, 4))，多了一层嵌套括号，Presto会把括号内的内容识别为一个ROW类型值，和ID字段的bigint类型不匹配，所以触发类型错误。
• 直接拼接SQL还存在SQL注入的安全隐患，更推荐使用参数化查询的方式传参。

方案1：修改字符串拼接逻辑（仅适用非用户输入的可信参数） #

将数字列表处理为逗号分隔的字符串，直接放到IN的括号内，代码修改如下：

import prestodb 
from prestodb import dbapi 
import os 

conn=prestodb.dbapi.connect(
    host=os.environ['aa'],
    port=os.environ['bb'],
    user=os.environ['cc'],
    password=os.environ['dd'],
    catalog='hive'
)

date_start = '2021-10-10'
date_end = '2021-10-15'
list_id = (1,2,3,4)
# 把数字列表转成逗号分隔的字符串
list_id_str = ','.join(map(str, list_id))

sql = '''
SELECT
   * 
FROM
   `table`
WHERE
   DATE BETWEEN '{date_start}'
   AND '{date_end}'
   AND ID in ({list_id})
   '''.format(date_start=date_start,date_end=date_end,list_id=list_id_str)

cur = conn.cursor()
cur.execute(sql)
query_result = cur.fetchall()

拼接后生成的IN条件为ID in (1,2,3,4)，符合Presto语法要求。

方案2：使用参数化查询（更安全，推荐） #

prestodb的dbapi支持参数化传参，不需要手动拼接字符串，既避免语法错误，也能防止SQL注入：

import prestodb 
from prestodb import dbapi 
import os 

conn=prestodb.dbapi.connect(
    host=os.environ['aa'],
    port=os.environ['bb'],
    user=os.environ['cc'],
    password=os.environ['dd'],
    catalog='hive'
)

date_start = '2021-10-10'
date_end = '2021-10-15'
list_id = (1,2,3,4)

# SQL里用%s作为占位符
sql = '''
SELECT
   * 
FROM
   `table`
WHERE
   DATE BETWEEN %s
   AND %s
   AND ID in %s
'''

cur = conn.cursor()
# 参数按顺序放到execute的第二个参数里
cur.execute(sql, (date_start, date_end, list_id))
query_result = cur.fetchall()

这种方式驱动会自动处理参数类型和格式转换，不需要手动处理括号和引号。

内容的提问来源于stack exchange，提问作者Daniel Vargas Shimamoto