将Python列表插入Postgres数据库

Question

我在使用psycopg时遇到了格式化列表的问题，准备插入数据。这里有一段我正在尝试的代码。基本上，我是从一个表中读取数据，然后想把它插入到另一个表里。

代码：

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)))

第一个查询返回的结果是这样的：

[([6193, 3975, 4960, 5286, 3380, 970, 3328, 3173, 2897, 2457, 2443, 2674, 2172, 2740, 3738, 4907, 3691, 4234, 3651, 3215],)]

当我尝试将这些数据插入到另一个表时，出现了格式错误。

   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...

我试过使用cur.mogrify，但似乎没有什么帮助。

如果有人有解决这个问题的方法，请告诉我。

谢谢，Adi

Answer 1

我觉得这里不需要用到 mogrify。可以使用 executemany，把 rows 作为第二个参数传进去。

cur.executemany(
    """INSERT INTO "%s" (data) VALUES (%%s)""" % (args.tableName),rows)

使用参数化的参数可以帮助防止 SQL 注入，这是一种常见的安全问题。

不过，表名是不能用参数化的方式来处理的，所以我们需要用字符串插值的方式把表名放到 SQL 查询里。这里的 %%s 是用来转义百分号的，经过字符串插值后会变成 %s。

---

顺便提一下，（正如 a_horse_with_no_name 已经指出的）你可以使用 INSERT INTO ... SELECT 的形式来把两个 SQL 查询合并成一个：

cur.execute(
    """INSERT INTO %s (data)
       SELECT data FROM Table1
       WHERE lat=-20.004189 AND lon=-63.848004""" % (args.tableName))

---

根据评论中的问题，如果有多个字段，那么 SQL 查询就变成：

cur.executemany(
    """INSERT INTO {t} (lat,lon,data1,data2) 
       VALUES (%s,%s,%s,%s)""".format(t=args.tableName),rows)

（如果你使用 format 方法，那么就不需要转义其他的 %s 了。）