Question 177188 in PostgreSQL Question posted in
The official documentation can be found here.

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

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', '[email protected]'),
 (2, 'ACTIVE', 'UC921', '[email protected]'),
 (3, 'DISABLED', 'UC983', '[email protected]'),
  ...
]

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.

Tags:

2

Answers


  1. 0

    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', '[email protected]'),
 (2, 'ACTIVE', 'UC921', '[email protected]'),
 (3, 'DISABLED', 'UC983', '[email protected]'),
  ...
]

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()
    Login or Signup to reply.
  2. 0 
    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search