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

Question

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

Answer 1

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 %s) as data(v1,id) where id = data.id"
extras.execute_values(cur, sql, countyid_landid_tuple_list)