用Python字典批量更新PostgreSQL

Question

在现有的PostgreSQL表中，我希望使用字典查找（见下面的字典）更新多个现有列的值。类似于这篇不错的博客文章所描述的方式。然而，我无法想出如何使用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
 ...

这是否可能，还是我必须遍历整个表格？

- n1000

---

https://dev59.com/tWw05IYBdhLWcg3wy052 - Ashalynd

你了解SQLAlchemy吗？ - Ashalynd

@Ashalynd 谢谢！我不确定那个链接的问题能否解决我的问题 - 抱歉。我已经尝试使用SQLAlchemy的ORM，但显然我还不够熟悉它。它有相关工具吗？ - n1000

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
)

- cetver

---

谢谢 - 但似乎我在问题表述上不够清晰。我现在会进行编辑... 对此我非常抱歉。 - n1000

如果myTable.furniture的类型是文本兼容的（如text、character varying等），那么没有问题。只需更改行的值（rows[i].furniture = 'chair'）。否则，首先更改列类型（ALTER TABLE myTable ALTER COLUMN furniture TYPE text;）。 - cetver

显然我在这里有些不理解。你是说我应该事先准备一个字典列表，以便基本上逐行更新表格？但我的数据库很大，顺序随机，并且替换字典有许多条目。如果可能的话，我想原地更新。 - n1000

所以不能使用字典进行原地更新吗？经过更多的思考，这是有道理的：这将需要... - n1000

只有 executemany 可以接受元组字典作为参数。您可以从元组创建 CSV 并将其插入临时表（最快的方法），或者直接将其（元组）插入临时表并从 tmpTable 更新 myTable。 - cetver

Answer 2

catver的方法可行。不过，我发现创建一个临时表格更有效率。

import psycopg2
from psycopg2.extensions import AsIs

rows = zip(d.keys(), d.values())
curs.execute("""
    CREATE TEMP TABLE codelist(DKEY INTEGER, DVALUE TEXT) 
    ON COMMIT DROP""")

curs.executemany("""
  INSERT INTO codelist (DKEY, DVALUE)
  VALUES(%s, %s)""",
  rows)

for i in [(AsIs('furniture'), AsIs('furniture')), (AsIs('plant'), AsIs('plant'))]:
    curs.execute("""
        UPDATE my_table
        SET %s = codelist.DVALUE
        FROM codelist
        WHERE codelist.DKEY = my_table.%s;
        """, i)

NB: 这个例子可能不太适用，因为我将INTEGER替换为TEXT值。这可能会引发错误ERROR: operator does not exist: integer = character varying。 在这种情况下，this answer可能会有所帮助。

- n1000