python - 从 Python 字典批量更新 PostgreSQL

Question

在现有的 PostgreSQL 表中，我想UPDATE 几个现有的列，这些列的值来自字典查找(参见下面的字典)。有点像这个 nice blog post 中描述的.但是，我无法弄清楚如何使用 Python 字典来做到这一点。可怕的伪代码来了:

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

在操作之后它应该是这样的:

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

这是可能的还是我必须遍历表格？

Answer 1

试试这个:

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
)