从Python dict批量更新PostgreSQL [英] Bulk update PostgreSQL from Python dict

问题描述

在一个现有的PostgreSQL表中，我想UPDATE使用字典查找中的值对几个现有的列进行查找(请参见下面的dict).类似于不错的博客帖子中所述的内容.但是，我不知道如何使用Python字典来做到这一点.这是可怕的伪代码:

In an existing PostgreSQL table, I would like to UPDATE several existing columns with values from a dictionary lookup (see dict below). Somewhat like described in this nice blog post. However, I can't figure out how to do that with a Python dictionary. Here comes the terrible pseudo-code:

d = {10:'chair', 11:'table', 12:'lamp', 
    20:'english ivy', 21:'peace lily', 22:'spider plant'}

curs.execute("""
    UPDATE my_table t
    SET furniture = %(t.furniture)s,
    SET plant = %(t.plant)s""",
    d)

原始表如下所示:

gid | furniture | plant
-----------------------
 0  |    10     |  21
 1  |    11     |  20
 ...

操作后，它应如下所示:

After the operation it should look like this:

gid | furniture |    plant
-----------------------------
 0  |   chair   | peace lily
 1  |   table   | english ivy
 ...

这可能吗?还是我必须遍历这张桌子?

Is this possible or will I have to loop through the table?

推荐答案

尝试一下:

rows = (
    {'gid': 10, 'furniture': 10, 'plant': 10},
    {'gid': 20, 'furniture': 20, 'plant': 20}
)
cur.executemany(
    '''
        UPDATE myTable 
        SET
            furniture = %(furniture)s,
            plant = %(plant)s
        WHERE
            gid = %(gid)s
    ''',
    rows
)

这篇关于从Python dict批量更新PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！