Inserting Polygon data into PostGIS using Python

Question

I am able to insert polygon data manually into a postGres DB with one column having polygon datatype however, when I try to do the same dynamically it is not working.

This works.

cur.execute("INSERT into  public.test(timestamp,poly) values (now(),'((2,2),(3,4),(3,6),(1,1))');")

This doesnt.

    coords='((2,2),(3,4),(3,6),(1,1))'
    cur = cnn.cursor()
    cur.execute("INSERT into  public.test(timestamp,poly) values (now(),'coords');")

so only difference is that , this time I am converting the same co ordinates into a tuple of tuples and passing that as a variable into the query.

Error: psycopg2.errors.InternalError_: parse error - invalid geometry

Answer 1

psycopg2 either needs a string literal wrapped in ' when building the string, or get passed a value properly via the parameter interface. So either do:

coords = "'((2,2),(3,4),(3,6),(1,1))'"  # note the "'"

cursor.execute("INSERT INTO public.test(timestamp, poly) VALUES(NOW(), " + coords + ");")

# this builds the string with concatenating the '-wrapped string literal to the statement
# note that NOW() is a valid PostgreSQL function

or, the recommended way to have psycopg2 take care of properly substituting parameters (to e.g. safeguard against SQL injection):

coords = '((2,2),(3,4),(3,6),(1,1))'  # no wrapping needed here

cursor = conn.cursor()
cursor.execute("INSERT INTO public.test(timestamp, poly) VALUES(NOW(), %s);", [coords])

# parameters are correctly formatted using specific wildcards, e.g. %s for string literals 

---

Running your example command

 cur.execute("INSERT into  public.test(timestamp,poly) values (now(),'coords');")

will result in the query

INSERT into public.test(timestamp,poly) values (now(),'coords');

where 'coords' is passed in directly (not replaced with the Python variable), and due to the ' gets treated as string by PostgreSQL.