将Python列表作为输入传递给SQL中的红移

Question

我想提供一个Python列表

sql_foramt= ['HP7CXNGSUFEPZCO4GS5RQPY6XY', '9475EFWZCNARPEJEZEMXDFHIBI', 
             '6ECWZUQGEJCR5EZXDH9URCN53M', 'XZ9P6KTDGREM5KIXUO9IHCTKAQ']

作为Python中上述SQL查询的输入

cur.execute("""CREATE TEMP TABLE ##Validation_Marc  (
Codes varchar(250) );


INSERT INTO ##Validation_Marc
VALUES

{}.format(','.join(list_name);

我正在尝试使用psycopg2库通过Python运行SQL查询。我尝试过上述方法，但每次尝试都会出现错误：

Traceback (most recent call last): File "", line 48, in psycopg2.errors.SyntaxError: syntax error at or near "{" LINE 8: {}.format(','.join(list_name);

我无法前进，请帮忙

Answer 1

不太清楚您希望如何插入列表，但让psycopg2/execute为您格式化查询：

import psycopg2
import json

conn = psycopg2.connect("...")
cur = conn.cursor()

sql = "CREATE TEMP TABLE __Validation_Marc ( Codes varchar(250) )"
cur.execute(sql)

mylist = ['HP7CXNGSUFEPZCO4GS5RQPY6XY', '9475EFWZCNARPEJEZEMXDFHIBI', '6ECWZUQGEJCR5EZXDH9URCN53M', 'XZ9P6KTDGREM5KIXUO9IHCTKAQ']
sql = "INSERT INTO __Validation_Marc VALUES (%s)"

# Option1: Insert each code as record set
for value in mylist:
    cur.execute(sql, (value, ))
conn.commit()

# Option2: Insert codes as one recordset comma separated
cur.execute(sql, (','.join(mylist),) )
conn.commit()

# Option3: insert as string as it is:
cur.execute(sql, (json.dumps(mylist),) )
conn.commit()

cur.execute("select * from __Validation_Marc")

for row in cur.fetchall():
    print(row)

输出：

('HP7CXNGSUFEPZCO4GS5RQPY6XY',)
('9475EFWZCNARPEJEZEMXDFHIBI',)
('6ECWZUQGEJCR5EZXDH9URCN53M',)
('XZ9P6KTDGREM5KIXUO9IHCTKAQ',)
('HP7CXNGSUFEPZCO4GS5RQPY6XY,9475EFWZCNARPEJEZEMXDFHIBI,6ECWZUQGEJCR5EZXDH9URCN53M,XZ9P6KTDGREM5KIXUO9IHCTKAQ',)
('["HP7CXNGSUFEPZCO4GS5RQPY6XY", "9475EFWZCNARPEJEZEMXDFHIBI", "6ECWZUQGEJCR5EZXDH9URCN53M", "XZ9P6KTDGREM5KIXUO9IHCTKAQ"]',)

注意：
我必须更改表名，我的postgres db不允许该名称：