Python / psycopg2: Parsing error when updating geometries

Question

I am updating a table with geometries using the following SQL code, working using the query tool in pgadmin:

INSERT INTO mytable (id, geometry) VALUES (1, ST_SetSRID(ST_GeomFromText('Point(15.5 17.7)'),4326), < more tuples >
ON CONFLICT (id) DO UPDATE 
geometry = EXCLUDED.geometry

I'd like to use this code for a bulk upsert from Python using psycopg2.

# Mock code:
# Prepare geodataframe for upsert:
points = ["ST_SetSRID(ST_GeomFromText('Point({} {})', 4326)".format(geo.x, geo.y) for geo in gdf.geometry]
gdf = gdf.assign(points=points)

upsert_tuples = [tuple(x) for x in gdf[['points', 'id', <other relevant cols>]].to_numpy()]

# SQL code
upsert_sql =  '''INSERT INTO mytable (geometry, id, <other cols>) VALUES %s 
              ON CONFLICT (id) DO UPDATE SET
              geometry = EXCLUDED.geometry,
              <other = etc>;'''

# Connect to database
...
extras.execute_values(cursor, upsert_sql, upsert_tuples)
...

This raises an error message: parse error - invalid geometry. How can I upsert geometries from Python?

Answer 1

It works after converting the entry to WKB.