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

Postgresql – How to use template parameter with extras.execute_values()?

I am using extras.execute_values() method in psycopg2 and things don’t go throw as I expected:

countyid_landid_tuple_list = [(1,1),(1,2),(2,3)]
sql = "update agri_land set county_id = %s"
extras.execute_values(cur, sql, countyid_landid_tuple_list ,"%s where id = %s")

The error message is as follows:

psycopg2.errors.SyntaxError: error:  grammar mistakes around "," or nearby
LINE 1: ...e agri_land set country_id = 1 where id = 1,1 where i...

Basically, the documentation is pretty bad about explaining the usage of template perameter. Why not more examples?

Environment:

  • python 3.7.9(64-bit)
  • psycopg2 2.9.5
  • PostgreSQL 12
Tags:

2

Answers


  1. 0

    The psycopg2 extras.execute_values function is typically used for bulk-insert operations, not for bulk-update operations, because it structures queries in a way that does not easily allow for the WHERE clause to vary from row to row.

    You can consider looping through your list of tuples and executing individual update statements.

    countyid_landid_tuple_list = [(1,1),(1,2),(2,3)]
for county_id, land_id in countyid_landid_tuple_list:
    sql = "UPDATE agri_land SET county_id = %s WHERE id = %s"
    cur.execute(sql, (county_id, land_id))

    If you have a large number of updates to make, you can utilize psycopg2’s ability to batch and commit multiple updates at once:

    countyid_landid_tuple_list = [(1,1),(1,2),(2,3)]
with psycopg2.connect(DSN) as conn:
    with conn.cursor() as cur:
        for county_id, land_id in countyid_landid_tuple_list:
            sql = "UPDATE agri_land SET county_id = %s WHERE id = %s"
            cur.execute(sql, (county_id, land_id))
        conn.commit()

    This way, you still get the performance benefits of batching your updates into a single transaction. It won’t be as fast as a bulk insert, but it should be more efficient than executing and committing each update individually.

    Login or Signup to reply.
  2. 0

    The docs do indeed cover this case. From :

    https://www.psycopg.org/docs/extras.html#fast-execution-helpers

    execute_values(cur,
... """UPDATE test SET v1 = data.v1 FROM (VALUES %s) AS data (id, v1)
... WHERE test.id = data.id""",
... [(1, 20), (4, 50)])

    So:

    countyid_landid_tuple_list = [(1,1),(1,2),(2,3)]
sql = "update agri_land set county_id = data.v1 from (values %) as data(id, v1) where id = data.id"
extras.execute_values(cur, sql, countyid_landid_tuple_list)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search