python - 如何使用 [Insert into...select] 语句而不是 [Insert into...values] 方法在 postgres 中插入元组

Question

我知道这一点，并尝试在 python 中将数据插入到这个 postgres 表中，如下所示:

cur.execute('''INSERT INTO trademarks_image_mark (trademarks_id, image_mark_id)
               SELECT currval('trademarks_id_seq'), id FROM image_mark WHERE 
               image_file_name LIKE %s''', data['ImageFileName'])

..但是我一直收到这个错误:

TypeError: 并非所有参数都在字符串格式化期间转换

有谁知道为什么会这样？我知道如何使用常规 Insert into...values 语句插入像 (%s) 这样的元组，但不是这种格式。非常感谢和感谢!

Answer 1

如 psycopg2 docs 中所述，您必须将插入到查询中的参数作为一个序列传递，即使只有一个:

cur.execute('''INSERT INTO trademarks_image_mark (trademarks_id, image_mark_id)
               SELECT currval('trademarks_id_seq'), id FROM image_mark WHERE 
               image_file_name LIKE %s''', (data['ImageFileName'],))

这是文档中的相关部分:

For positional variables binding, the second argument must always be a sequence, even if it contains a single variable. And remember that Python requires a comma to create a single element tuple:

>>> cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # WRONG
>>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # WRONG
>>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct
>>> cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # correct