2

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

  • Probably because it needs to be an actual string literal; the string itself needs to be wrapped in ' – geozelot Jun 29 at 7:55
  • @geozelot , I did it, there is a " ' " around the coords variable. – Anarach Jun 29 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 at 8:01
  • @geozelot Hard luck :-( doesnt work < i get the below error – Anarach Jun 29 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 at 8:05
3

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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