使用Psycopg2插入Python字典

Question

如何将一个包含许多键的Python字典插入Postgres数据库，而不必枚举所有键？

我想要做这样的事情...

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

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

- user3783608

Answer 1

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

输出:

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

Psycopg将tuple适配到record，而AsIs则执行Python字符串替换所执行的操作。

- Clodoaldo Neto

---

不知道 AsIs。有趣的是，它可以避免处理乘出来的 %s... - khampson

我担心由于不充分的转义，这可能会使用户容易受到注入攻击的威胁，但是至少使用基本示例时，单引号似乎已经被正确转义了。我没有时间测试更高级的注入技术，例如（或类似于）以下链接中描述的技术，因此它们可能仍然存在与更标准的参数化技术相比的问题。https://dev59.com/Ym025IYBdhLWcg3w4qEx#12118602 - evan_b

为什么不直接使用song.values()来获取值呢？ :) - Brian Wylie

如果我想获取插入行的ID，该怎么做？ - Pabitra Roy

需要获取 mogrify 的时候，传入的是一个字符串 'str'，而不是 psycopg2.extensions.cursor 类型的 insert_statement。 - John Barton

看起来这是用Python2写的，在字典被排序之前。现在，你可以使用.keys()和.values()并且顺序将会被维护。 - Justin Furuness

Answer 2

你也可以使用 字典 插入多行。如果你有以下内容：

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

您可以使用以下方式将所有三行插入字典中：

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

< p > cur.executemany 语句将自动遍历字典并为每一行执行INSERT查询。

< p >PS：此示例取自此处

- vikas

---

嗨，维卡斯，有没有对在循环内使用cursor.execute()的性能进行比较的内容？ - Jorge Vidinha

有没有类似的解决方案，利用mogrify来获得性能优势？ - Rexovas

Answer 3

这样的东西应该就可以了：

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)

关键部分是生成的包含%s元素的字符串，并将其用于format，并直接将列表传递给execute调用，以便psycopg2可以插入vals列表中的每个项目（从而防止可能的SQL注入）。另一种变化是将dict传递给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)

- khampson

---

我会将 cols 替换为 [cursor.mogrify(x) for x in cols]，并对 vals_str 做同样的替换，以防止 SQL 注入。 - 9000

同意这肯定会增加额外的保护。 - khampson

读了一下psycopg2文档，mogrify可能是不必要的，因为该方法的定义指出返回的字符串正是将被发送到运行execute()方法或类似方法的数据库中的字符串，所以我认为列和%s字符串将在execute调用中进行mogrify。 - khampson

我认为主要的区别在于它是在调用execute之前还是在execute本身期间完成。如果在之前完成，那么在execute运行期间它基本上只会返回相同的字符串，因为它已经被修改过了。如果你想确切地知道传输了什么，比如说，为了记录日志，提前完成可能是有益的。 - khampson

Answer 4

新的sql模块是为了这个目的而创建的，并在psycopg2版本2.7中添加。根据文档：

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

文档中给出了两个示例：https://www.psycopg.org/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))

插入到表中（"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))

插入到表中（"foo"，"bar"，"baz"）的语句为：values（%(foo)s，%(bar)s，%(baz)s）。

尽管字符串拼接可以得到相同的结果，但根据psycopg2文档，不应该将其用于此目的：

警告：绝对不能使用Python字符串拼接（+）或字符串参数插值（%）将变量传递给SQL查询字符串。即使在枪口下也不行。

- Antoine Dusséaux

Answer 5

另一种从字典查询MySQL或pgSQL的方法是使用构造函数%(dic_key)s，它将被字典中与dic_key相对应的值替换，例如{'dic_key': 'dic value'}。这种方法运行完美，并防止SQL注入。测试过：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";

- Victor Orletchi

Answer 6

使用 Python3 的 "OrderedDict" 功能，Clodaldo 的答案变得更加简单:

from psycopg2.extensions import AsIs

song = dict(title='song 1', artist='artist 1')

insert_statement = 'insert into song_table (%s) values %s'
cursor.execute(insert_statement, (AsIs(','.join(song.keys())), tuple(song.values())))

- hansaplast

Answer 7

使用 execute_values https://www.psycopg.org/docs/extras.html 更快，并且有一个 fetch 参数可以返回结果。下面是一些可能有用的代码。 columns 是一个字符串，例如 col_name1, col_name2 template 是允许匹配的字符串，例如 %(col_name1)s, %(col_name2)

def insert(cur: RealDictCursor,
        table_name: str,
        values: list[dict],
        returning: str = ''
        ):
    if not values:
        return []

    query = f"""SELECT
                    column_name AS c
                FROM
                    information_schema.columns
                WHERE
                    table_name = '{table_name}'
                AND column_default IS NULL;"""
    cur.execute(query)
    columns_names = cur.fetchall()

    fetch = False
    if returning:
        returning = f'RETURNING {returning}'
        fetch = True

    columns = ''
    template = ''
    for col in columns_names:
        col_name = col['c']
        for val in values:
            if col_name in val:
                continue
            val[col_name] = None

        columns += f'{col_name}, '
        template += f'%({col_name})s, '
    else:
        columns = columns[:-2]
        template = template[:-2]

    query = f"""INSERT INTO {table_name} 
                    ({columns})
                    VALUES %s {returning}"""
    return execute_values(cur, query, values,
                        template=f'({template})', fetch=fetch)

- Ricardo Garcia Saez

Answer 8

Python具有某些内置功能，例如join和list，可以使用它们生成查询。此外，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)

- Ragin Sharma

---

这是一个非常非常糟糕的想法。引用psycopg文档的话：“千万不要使用Python字符串拼接（+）或字符串参数插值（%）将变量传递给SQL查询字符串。即使在枪口下也不行。” - Nathan