Psycopg2在Postgres数据库中插入python字典 [英] Psycopg2 insert python dictionary in postgres database

问题描述

在python 3+中，我想将字典(或pandas数据框)中的值插入数据库中.我选择了带有postgres数据库的psycopg2.

In python 3+, I want to insert values from a dictionary (or pandas dataframe) into a database. I have opted for psycopg2 with a postgres database.

问题在于我无法弄清楚执行此操作的正确方法.我可以轻松地连接要执行的SQL字符串，但是psycopg2文档明确警告了这一点.理想情况下，我想做这样的事情:

The problems is that I cannot figure out the proper way to do this. I can easily concatenate a SQL string to execute, but the psycopg2 documentation explicitly warns against this. Ideally I wanted to do something like this:

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

，并希望执行程序可以确定dict的键与表中的列匹配.这没有用.从psycopg2文档的示例中，我了解了这种方法

and hoped that the execute could figure out that the keys of the dict matches the columns in the table. This did not work. From the examples of the psycopg2 documentation I got to this approach

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

将字典插入具有匹配的列名的表中最有系统性的方法是什么?

What is the most phytonic way of inserting a dictionary into a table with matching column names?

推荐答案

两种解决方案:

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

这篇关于Psycopg2在Postgres数据库中插入python字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！