如何在psycopg2中同时传入可迭代与非可迭代参数执行SQL语句

报错原因 #

你遇到的报错是因为executemany的入参格式不符合要求：该方法的第二个参数必须是参数序列组成的可迭代对象，每一个子序列对应一次SQL执行的入参。你当前传入的(person_id, role_id_list)会被psycopg2解析为第一次执行的参数是person_id（整数类型），尝试对其索引取参数值就触发了int不支持索引的报错。

解决方案 #

方案1：正确使用executemany（无需手动写for循环执行SQL）

只需要提前把固定值person_id和每个角色ID拼接成单次执行的参数元组，构造符合要求的参数序列即可：

def delete_person_roles(person_id, role_id_list):
    sql = "DELETE FROM person_roles WHERE person_id = %s AND role_id = %s;"
    conn = None
    try:
        conn = psycopg2.connect(
            host="localhost",
            database="mydatabase",
            user="myuser",
            password="mypassword")
        cur = conn.cursor()
        # 构造executemany需要的参数序列：每个元素对应一次执行的参数元组
        params = [(person_id, role[0]) for role in role_id_list]
        cur.executemany(sql, params)
        conn.commit()
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

方案2：单条SQL执行（性能更优，推荐）

不需要用executemany，直接用PostgreSQL的ANY语法适配列表参数，只需要一次数据库请求就能完成所有匹配记录的删除，性能比多次执行更好：

def delete_person_roles(person_id, role_id_list):
    # 用ANY语法匹配列表中的所有role_id
    sql = "DELETE FROM person_roles WHERE person_id = %s AND role_id = ANY(%s);"
    conn = None
    try:
        conn = psycopg2.connect(
            host="localhost",
            database="mydatabase",
            user="myuser",
            password="mypassword")
        cur = conn.cursor()
        # 把元组列表转为整数列表作为入参
        role_ids = [item[0] for item in role_id_list]
        cur.execute(sql, (person_id, role_ids))
        conn.commit()
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

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