postgresql 将python列表插入Postgres数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7760052/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert python list into Postgres database
提问by Aditya
I am having trouble in formatting the list for insertion using psycopg. Here is a sample of code i am trying to do. Basically I am just reading data from one table and trying to insert it into another table.
我在使用 psycopg 格式化要插入的列表时遇到问题。这是我正在尝试执行的代码示例。基本上我只是从一个表中读取数据并尝试将其插入到另一个表中。
Code:
代码：
cur.execute("""select data from \"Table1\" where lat=-20.004189 and lon=-63.848004""")
rows = cur.fetchall()
print rows
cur.execute("""INSERT INTO \"%s\" (data) VALUES (ARRAY%s)""" % (args.tableName,rows)))
The result returned by first select query is like this:
第一个select查询返回的结果是这样的：
[([6193, 3975, 4960, 5286, 3380, 970, 3328, 3173, 2897, 2457, 2443, 2674, 2172, 2740, 3738, 4907, 3691, 4234, 3651, 3215],)]
When I try to insert this into another table I get the following format error.
当我尝试将其插入另一个表时，出现以下格式错误。
cur.execute(cur.mogrify("""INSERT INTO \"%s\" (data) VALUES (%s)""" % (args.tableName,rows)))
psycopg2.ProgrammingError: syntax error at or near "["
LINE 1: INSERT INTO "DUMMY1km" (data) VALUES ([([6193, 3975, 4960, 5...
I tried cur.mogrify, but it does not seem to help.
我试过 cur.mogrify，但它似乎没有帮助。
Please let me know if anyone has a work around for this issue.
请让我知道是否有人可以解决此问题。
Thanks Adi
谢谢阿迪
回答by unutbu
I don't think mogrifyis needed here. Use executemanyand pass rowsas the second argument.
我认为mogrify这里不需要。使用executemany并rows作为第二个参数传递。
cur.executemany(
"""INSERT INTO "%s" (data) VALUES (%%s)""" % (args.tableName),rows)
Using parametrized arguments helps prevent SQL injection.
使用参数化参数有助于防止SQL 注入。
The table name can not be parametrized, so we do have to use string interpolation to place the table name in the SQL query. %%sgets escapes the percent sign and becomes %safter string interpolation.
表名不能被参数化，所以我们必须使用字符串插值将表名放在 SQL 查询中。%%sget 转义百分号并%s在字符串插值后变为。
By the way, (as a_horse_with_no_name has already pointed out) you can use the INSERT INTO ... SELECT formof INSERTto perform both SQL queries as one:
顺便说一句，（如a_horse_with_no_name已经指出的），你可以使用INSERT INTO ... SELECT形式的INSERT为一个执行两个SQL查询：
cur.execute(
"""INSERT INTO %s (data)
SELECT data FROM Table1
WHERE lat=-20.004189 AND lon=-63.848004""" % (args.tableName))
Per the question in the comments, if there are multiple fields, then the SQL becomes:
根据评论中的问题，如果有多个字段，则 SQL 变为：
cur.executemany(
"""INSERT INTO {t} (lat,lon,data1,data2)
VALUES (%s,%s,%s,%s)""".format(t=args.tableName),rows)
(If you use the formatmethod, then you don't have to escape all the other %ss.)
（如果使用该format方法，则不必转义所有其他%ss。）
