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.

    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

  • Probably because it needs to be an actual string literal; the string itself needs to be wrapped in '
    – geozelot
    Jun 29, 2020 at 7:55
  • @geozelot , I did it, there is a " ' " around the coords variable.
    – Anarach
    Jun 29, 2020 at 7:57
  • 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))'"
    – geozelot
    Jun 29, 2020 at 8:01
  • @geozelot Hard luck :-( doesnt work < i get the below error
    – Anarach
    Jun 29, 2020 at 8:05
  • psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type polygon: "coords" LINE 1: ...T into public.test(timestamp,poly) values (now(),'coords');
    – Anarach
    Jun 29, 2020 at 8:05

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.