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

• geozelot: Probably because it needs to be an actual string literal; the string itself needs to be wrapped in '
• Anarach: @geozelot , I did it, there is a " ' " around the coords variable.
• geozelot: Those ' above declares a string in Python, but PostgreSQL needs a string that is wrapped in '; try coords = "'((2,2),(3,4),(3,6),(1,1))'"
• Anarach: @geozelot Hard luck :-( doesnt work < i get the below error
• Anarach: psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type polygon: "coords" LINE 1: ...T into public.test(timestamp,poly) values (now(),'coords')

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.