[SOLVED] psycopg2.extras.execute_values to insert multiple rows for geometry data

psycopg2.extras.execute_values to insert multiple rows for geometry data

This is my insert query

sql = "INSERT INTO sightings (run_id, geometry, properties)
             VALUES(%s, ST_MakePoint(%s, %s), %s);"

1) run_id is an internal uuid

2) geometry - this is geopoint object

3) properties is a JSON object

On running psycopg2.extras.execute_values(cur, sql, listOfTuples) I get an error that says:

the query contains more than one '%s' placeholder'

I realize my mistake around this, but how can I get around it. Is there a way the template parameter can be of any help.

Solution

Yes, the template parameter explained in a documentation for fast execution helpers does exactly what you need.

psycopg2.extras.execute_values(cur, sql, argslist, template=None, page_size=100)

template – the snippet to merge to every item in argslist to compose the query.

Try something like this:

sql = "INSERT INTO sightings (run_id, geometry, properties) VALUES %s"
user_id = 1
lnglat = (20.0, 40.0)
props = {"foo": "bar"}

listOfTuples = [(user_id,) + lnglat + (json.dumps(props),)]
# lisftOfTuples = [(1, 20.0, 40.0, '{"foo": "bar"}')]

psycopg2.extras.execute_values(
  cur, 
  sql, 
  listOfTuples, 
  template='(%s, st_makepoint(%s, %s), %s)'
)