Psycopg2如何在PostgreSQL数据库中插入Python字典？

Question

在Python 3+中，我想要将字典（或Pandas数据框）中的值插入到数据库中。我选择了使用PostgreSQL数据库的psycopg2。

问题在于我无法找出正确的方法来做到这一点。我可以轻松地连接SQL字符串以执行操作，但是psycopg2文档明确警告不要这样做。理想情况下，我想要像这样做：

cur.execute("INSERT INTO table VALUES (%s);", dict_data)

我希望执行程序能够确定字典的键与表中的列匹配。但是并没有成功。通过查看psycopg2文档的示例，我采用了以下方法。

cur.execute("INSERT INTO table (" + ", ".join(dict_data.keys()) + ") VALUES (" + ", ".join(["%s" for pair in dict_data]) + ");", dict_data)

我从中获得了

TypeError: 'dict' object does not support indexing

什么是将字典插入具有匹配列名的表中最适合使用的Pythonic方式？

- Paamand

Answer 1

两种解决方案：

d = {'k1': 'v1', 'k2': 'v2'}

insert = 'insert into table (%s) values %s'
l = [(c, v) for c, v in d.items()]
columns = ','.join([t[0] for t in l])
values = tuple([t[1] for t in l])
cursor = conn.cursor()
print cursor.mogrify(insert, ([AsIs(columns)] + [values]))

keys = d.keys()
columns = ','.join(keys)
values = ','.join(['%({})s'.format(k) for k in keys])
insert = 'insert into table ({0}) values ({1})'.format(columns, values)
print cursor.mogrify(insert, d)

输出：

insert into table (k2,k1) values ('v2', 'v1')
insert into table (k2,k1) values ('v2','v1')

- Clodoaldo Neto

---

这种方法的优缺点与所提出的 SQL 相比如何？第二种解决方案基本上与所提出的方案相同（除了字符串格式与复合）？据我所知，如果需要，cur.execute 会调用 cur.mogrify。 - Paamand

已经有一段时间了，但我认为这段代码容易受到 SQL 注入攻击的威胁。 - yoni keren

Answer 2

我有时会遇到这个问题，特别是在处理JSON数据时，我希望能像处理字典一样处理它。非常相似……但也许更易读一些？

最初的回答：

def do_insert(rec: dict):
    cols = rec.keys()
    cols_str = ','.join(cols)
    vals = [ rec[k] for k in cols ]
    vals_str = ','.join( ['%s' for i in range(len(vals))] ) 
    sql_str = """INSERT INTO some_table ({}) VALUES ({})""".format(cols_str, vals_str)
    cur.execute(sql_str, vals)

我通常在迭代器内部调用这种类型的操作，并且通常包裹在try/except中。要么游标(cur)已经在外部范围内定义，要么可以修改函数签名并传递游标实例。我很少插入单个行...和其他解决方案一样，这也允许缺失列/值，前提是底层架构允许。只要在插入时未修改键视图下的字典，就无需按名称指定键，因为值将按照键视图中的顺序排序。"最初的回答"

- rosey99

Answer 3

[建议的答案/解决方法 - 欢迎更好的回答！]

经过一些试错，我成功实现了以下操作：

sql = "INSERT INTO table (" + ", ".join(dict_data.keys()) + ") VALUES (" + ", ".join(["%("+k+")s" for k in dict_data]) + ");"

这将返回 SQL 字符串

"INSERT INTO table (k1, k2, ... , kn) VALUES (%(k1)s, %(k2)s, ... , %(kn)s);"

可以被执行的

with psycopg2.connect(database='deepenergy') as con:
    with con.cursor() as cur:
        cur.execute(sql, dict_data)

发布/评论？

- Paamand

---

不确定为什么会收到以下错误消息：（psycopg2.errors.SyntaxError）在或附近的语法错误“％” 第1行：... 1，响应，持续时间）VALUES（％（id）s，％（...SQL插入语句已正确形成：INSERT INTO table(id, name, response, duration_time) VALUES (%(id)s, %(name)s, %(response)s, %(duration_time)s);但是，当我执行SQL语句cursor.execute(sql_insert, data_dict)时，您是否知道问题出在哪里？ - John Barton

Answer 4

这里是另一种解决方案，直接插入字典

产品模型（具有以下数据库列）

name
description
price
image
digital - (defaults to False)
quantity
created_at - (defaults to current date)

解决方案:

    data = {
        "name": "product_name",
        "description": "product_description",
        "price": 1,
        "image": "https",
        "quantity": 2,
    }
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO products (name,description,price,image,quantity) "
        "VALUES(%(name)s, %(description)s, %(price)s, %(image)s, %(quantity)s)", data
    )

    conn.commit()
    conn.close()

注意：要插入的列在执行语句.. INTO products (column names to be filled) VALUES ..., data <- the dictionary (should be the same **ORDER** of keys)中指定，数据字典中的键应该与其顺序相同。

- Ryan Aquino

Answer 5

使用 %(name)s 占位符可能会解决问题：

dict_data = {'key1':val1, 'key2':val2}

cur.execute("""INSERT INTO table (field1, field2) 
VALUES (%(key1)s, %(key2)s);""", 
dict_data)

您可以在psycopg2文档传递参数到SQL查询中找到其用法。

- lily.z