How would I do a bulk update using Python 3 | psycopg 3.1.8?

Question

In python I have a list of tuples

Each tuple has four fields, the first field is the row identifier that would be used in a where clause in a SQL update statement.

tuple: (id, status, department, manager_email)

update_tuple_list = [
 (1, 'ACTIVE', 'UC563', 'joe@company.com'),
 (2, 'ACTIVE', 'UC921', 'bob@company.com'),
 (3, 'DISABLED', 'UC983', 'pat@company.com'),
  ...
]

Using Python 3, psycopg ver 3.1.8

How would I do a bulk update?

For table user, the statement would be:

update user set
    status = %s,
    department = %s,
    manager_email = %s
    where id  %s;   # where the value of 'id' is the first field in the tuple.

I can rearrange the tuple to put the row id id at the end of the tuple if that would help.

How do I feed the example list of tuples to a statement like the above and do a single bulk update?

Is there a better more understandable way to do this using Python 3 | psycopg Ver 3.1.8 (NOT psycopg2)

I have seen stack overflow answers involving psycopg2 and I simply do not understand the response or the solution provided there, and also I am not using psycopg2. It's just not clear.

It's very simple with a bulk insert because there is no where clause'. With a bulk update, there is a where clause to identify the row that is being updated.

I have searched on this and tried to find a simple solution using the latest python version and library version, and nothing comes up.

Any and all help to solve this would be greatly appreciated.

All I can tell from this is that you have read something and didn't understand it. Please at least give us a link to the most likely thing you read but didn't understand and/or didn't work. — jjanes, May 26 '23 at 15:03
Don't tag spam, `psycopg2` does not apply and `python` was redundant, so I deleted them. — Adrian Klaver, May 26 '23 at 23:05

score 1 · Answer 1 · answered May 26 '23 at 23:17

\d animals
                        Table "public.animals"
   Column   |          Type          | Collation | Nullable | Default 
------------+------------------------+-----------+----------+---------
 pk_animals | integer                |           | not null | 
 cond       | character varying(200) |           | not null | 
 animal     | character varying(200) |           | not null | 

select * from animals where pk_animals in (3, 16);

 pk_animals | cond  | animal  
------------+-------+---------
         16 | fair  | heron
          3 | good  | mole

import psycopg
con = psycopg.connect("dbname=test host=localhost  user=postgres")
cur = con.cursor()

a_list = [('horse', 'fair', 16), ('lion', 'good', 3)]

cur.executemany('update animals set (animal, cond) = (%s, %s) where pk_animals = %s', a_list)

con.commit()

select * from animals where pk_animals in (3, 16);
 pk_animals | cond | animal 
------------+------+--------
         16 | fair | horse
          3 | good | lion

score -1 · Answer 2 · answered May 26 '23 at 14:51

-1

Maybe it depends what exactly you require from a "bulk update". If you'd just like everything to happen in one transaction, I think you might be good with something like this (untested!).

import psycopg

update_tuple_list = [
 (1, 'ACTIVE', 'UC563', 'joe@company.com'),
 (2, 'ACTIVE', 'UC921', 'bob@company.com'),
 (3, 'DISABLED', 'UC983', 'pat@company.com'),
  ...
]

with psycopg.connect(dbname="yourdb", ...) as conn:

    # Open a cursor to perform database operations
    with conn.cursor() as cur:

        for (id, status, department, manager_email) in update_tuple_list:
            cur.execute("""
                update user set
                status = %s,
                department = %s,
                manager_email = %s
                where id  %s;
                """, 
                (status, department, manager_email, id)
            )

    # Commit everything at the end
    conn.commit()

answered May 26 '23 at 14:51

Matt Smith

26
3

The for loop makes it so that each record updates one row at a time. It does not do what is referred to and understood to be a "PostgreSQL bulk update", that is one statement is executed, and all records are updated. This is entirely possible, but I'm not sure how it would be done. – user10664542 May 26 '23 at 15:16
If we forget about Python for a moment, the way to do this by executing a single statement is to use UPDATE FROM so that you can update based on a join between your datasets. This would require either already having your update_tuple_list in a second table, or selecting it into your update statement using a common table expression (WITH update_tuple_list as (select VALUES (..), (..)) UPDATE t..). This CTE method gets a bit messy when bringing psycopg3 into the equation though. Are you sure you need to do this all in a single statement, and that a single transaction isn't sufficient? – Matt Smith May 26 '23 at 15:51

How would I do a bulk update using Python 3 | psycopg 3.1.8?

2 Answers2