Bulk update PostgreSQL from Python dict

Question

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)

The original table would look somewhat like this:

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?

http://stackoverflow.com/questions/7019831/bulk-batch-update-upsert-in-postgresql — Ashalynd, May 11 '15 at 08:01
@Ashalynd Thanks! I'm note sure how the linked question could solve my problem - sorry. I played with SQLAlchemy's ORM. But apparently I don't know it well enough. Does it have a tool for that? — n1000, May 11 '15 at 08:11

score 8 · Answer 1 · answered May 11 '15 at 09:06

8

try this:

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
)

answered May 11 '15 at 09:06

cetver

11,279
5
36
56

Thanks - but it seems like I wasn't clear in my question. I will edit it now... I am very sorry about that. – n1000 May 11 '15 at 09:09
If type of myTable.furniture text-compatible (text, character varying, etc) - no problems. Just change value of rows (rows[i].furniture = 'chair'). Otherwise change column type first (ALTER TABLE myTable ALTER COLUMN furniture TYPE text;) – cetver May 11 '15 at 10:17
Obviously I am not getting something here. Are you saying I should prepare a list of dictionaries beforehand in order to update the table basically row-by-row? But my database is huge, in random order, and the replacement dict has many entries. I would like to update in-place - if possible. – n1000 May 11 '15 at 11:50
`executemany` is faster than `execute` in loop, but if you need maximum speed read this http://stackoverflow.com/questions/8910494/how-to-update-selected-rows-with-values-from-a-csv-file-in-postgres#answer-8910810 – cetver May 11 '15 at 14:38
So it is not possible to update inplace with a dict? After giving some more thought, it makes sense though: It would require – n1000 May 12 '15 at 08:22
Only `executemany` obtain tuple of dictionaries as argument. You can create csv from tuple and insert it into temp table (fastest way) or insert it (tuple) directly into temporary table and update `myTable` from `tmpTable` – cetver May 12 '15 at 10:19
Sounds good. Maybe you want to update your answer? :) Or I can try to figure it out and edit. – n1000 May 12 '15 at 10:27

score 0 · Answer 2 · edited May 23 '17 at 12:29

The approach of catver works. However, I found that creating a temporary table proved to be more efficient.

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: This example may not quite work because I am replacing INTEGER with TEXT values. This may throw the error ERROR: operator does not exist: integer = character varying. In that case, this answer might help.

Bulk update PostgreSQL from Python dict

2 Answers2

Linked