使用 Psycopg2 插入 Python 字典

Insert Python Dictionary using Psycopg2

提问by user3783608

What is the best way to insert a python dictionary with with many keys into a Postgres database without having to enumerate all keys?

将具有许多键的 python 字典插入 Postgres 数据库而不必枚举所有键的最佳方法是什么？

I would like to do something like...

我想做一些像......

song = dict()
song['title'] = 'song 1'
song['artist'] = 'artist 1'
...

cursor.execute('INSERT INTO song_table (song.keys()) VALUES (song)')

回答by khampson

Something along these lines should do it:

应该这样做：

song = dict()
song['title'] = 'song 1'
song['artist'] = 'artist 1'

cols=song.keys();

vals = [song[x] for x in cols]
vals_str_list = ["%s"] * len(vals)
vals_str = ", ".join(vals_str_list)

cursor.execute("INSERT INTO song_table ({cols}) VALUES ({vals_str})".format(
               cols = cols, vals_str = vals_str), vals)

The key part is the generated string of %selements, and using that in format, with the list passed directly to the executecall, so that psycopg2can interpolate each item in the valslist (thus preventing possible SQL Injection).

关键部分是生成的%s元素字符串，并使用它 in format，将列表直接传递给execute调用，以便psycopg2可以插入vals列表中的每个项目（从而防止可能的SQL 注入）。

Another variation, passing the dictto execute, would be to use these lines instead of vals, vals_str_listand vals_strfrom above:

另一种变体，通过dictto execute，将使用这些行而不是vals,vals_str_list和vals_str从上面：

vals_str2 = ", ".join(["%({0})s".format(x) for x in cols])

cursor.execute("INSERT INTO song_table ({cols}) VALUES ({vals_str})".format(
               cols = cols, vals_str = vals_str2), song)

回答by Clodoaldo Neto

from psycopg2.extensions import AsIs

song = {
    'title': 'song 1',
    'artist': 'artist 1'
}

columns = song.keys()
values = [song[column] for column in columns]

insert_statement = 'insert into song_table (%s) values %s'

    # cursor.execute(insert_statement, (AsIs(','.join(columns)), tuple(values)))
print cursor.mogrify(insert_statement, (AsIs(','.join(columns)), tuple(values)))

Prints:

印刷：

insert into song_table (artist,title) values ('artist 1', 'song 1')

Psycopg adapts a tupleto a recordand AsIsdoes what would be done by Python's string substitution.

Psycopg 将 a 调整tuple为 arecord并AsIs执行 Python 的字符串替换所做的工作。

回答by vikas

You can also insert multiple rows using a dictionary. If you had the following:

您还可以使用dictionary. 如果您有以下情况：

namedict = ({"first_name":"Joshua", "last_name":"Drake"},
            {"first_name":"Steven", "last_name":"Foo"},
            {"first_name":"David", "last_name":"Bar"})

You could insert all three rows within the dictionary by using:

您可以使用以下命令在字典中插入所有三行：

cur = conn.cursor()
cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES (%(first_name)s, %(last_name)s)""", namedict)

The cur.executemanystatement will automatically iterate through the dictionary and execute the INSERT query for each row.

该cur.executemany语句将自动遍历字典并对每一行执行 INSERT 查询。

PS: This example is taken from here

PS：这个例子取自here

回答by Victor Orletchi

an other approach for query to mySQL or pgSQL from dictionaryis using construction %(dic_key)s, it will be replaced by value from dictionary coresponding by dic_key like {'dic_key': 'dic value'}working perfect, and prevent sqlInjection tested: Python 2.7 see below:

从字典查询 MySQL 或 pgSQL的另一种方法是使用构造%(dic_key)s，它将被字典中的值替换为 dic_key 对应的字典，就像{'dic_key': 'dic value'}工作完美一样，并防止 sqlInjection 测试：Python 2.7 见下文：

# in_dict = {u'report_range': None, u'report_description': None, 'user_id': 6, u'rtype': None, u'datapool_id': 1, u'report_name': u'test suka 1', u'category_id': 3, u'report_id': None}


cursor.execute('INSERT INTO report_template (report_id, report_name, report_description, report_range, datapool_id, category_id, rtype, user_id) VALUES ' \
                                                                 '(DEFAULT, %(report_name)s, %(report_description)s, %(report_range)s, %(datapool_id)s, %(category_id)s, %(rtype)s, %(user_id)s) ' \
                                                                 'RETURNING "report_id";', in_dict)

---

---

出去： INSERT INTO report_template (report_id, report_name, report_description, report_range, datapool_id, category_id, rtype, user_id) VALUES (DEFAULT, E'test suka 1', NULL, NULL, 1, 3, NULL, 6) RETURNING "report_id"; INSERT INTO report_template (report_id, report_name, report_description, report_range, datapool_id, category_id, rtype, user_id) VALUES (DEFAULT, E'test suka 1', NULL, NULL, 1, 3, NULL, 6) RETURNING "report_id";

回答by Antoine Dusséaux

The new sqlmodule was created for this purpose and added in psycopg2 version 2.7. According to the documentation:

sql为此目的创建了新模块，并在 psycopg2 2.7 版中添加了该模块。根据文档：

If you need to generate dynamically an SQL query (for instance choosing dynamically a table name) you can use the facilities provided by the psycopg2.sql module.

如果您需要动态生成 SQL 查询（例如动态选择表名），您可以使用 psycopg2.sql 模块提供的工具。

Two examples are given in the documentation: http://initd.org/psycopg/docs/sql.html

文档中给出了两个示例：http: //initd.org/psycopg/docs/sql.html

names = ['foo', 'bar', 'baz']

q1 = sql.SQL("insert into table ({}) values ({})").format(
    sql.SQL(', ').join(map(sql.Identifier, names)),
    sql.SQL(', ').join(sql.Placeholder() * len(names)))
print(q1.as_string(conn))

insert into table ("foo", "bar", "baz") values (%s, %s, %s)

插入表 ("foo", "bar", "baz") 值 (%s, %s, %s)

q2 = sql.SQL("insert into table ({}) values ({})").format(
    sql.SQL(', ').join(map(sql.Identifier, names)),
    sql.SQL(', ').join(map(sql.Placeholder, names)))
print(q2.as_string(conn))

insert into table ("foo", "bar", "baz") values (%(foo)s, %(bar)s, %(baz)s)

插入表 ("foo", "bar", "baz") 值 (%(foo)s, %(bar)s, %(baz)s)

Though string concatenation would produce the same result, it should not be used for this purpose, according to psycopg2 documentation:

根据 psycopg2 文档，尽管字符串连接会产生相同的结果，但不应将其用于此目的：

Warning: Never, never, NEVERuse Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

警告：从不、从不、从不使用 Python 字符串连接 ( +) 或字符串参数插值 ( %) 将变量传递给 SQL 查询字符串。甚至不是在枪口下。

回答by Ragin Sharma

Python has certain inbuilt features such as joinand listusing which one can generate the query. Also,the python dictionary offers keys()and values()which can be used to extract column name and column values respectively. This is the approach I used and this should work.

Python 具有某些内置功能，例如连接和列表，可以使用它们生成查询。此外，python 字典提供了keys()和values()可用于分别提取列名和列值。这是我使用的方法，这应该有效。

song = dict()
song['title'] = 'song 1'
song['artist'] = 'artist 1'

query = '''insert into song_table (''' +','.join(list(song.keys()))+''') values '''+ str(tuple(song.values()))
cursor.execute(query)