INSERT INTO and String Concatenation with Python

Question

I have hit a major speed bump in inserting data in to my DB. You can see from the code below that I am simply building the SQL statement to pass in to the execute command. The values are correct and all is well there but the python interpreter seems to be adding and removing quotes from the params at runtime.

This is the correct way to insert spatial data in to the DB.

INSERT INTO my_table(
            name, url, id, point_geom, poly_geom)
    VALUES ('test', 'http://myurl', '26971012', 
            ST_GeomFromText('POINT(52.147400 19.050780)',4326), 
            ST_GeomFromText('POLYGON(( 52.146542 19.050557, bleh, bleh, bleh))',4326));

This is verifiable in the Postgres query editor...Now, when I run the Python code below It adds double quotes around the ST_GeomFromText function and then removes the quotes from the id column.

INSERT INTO my_table(
            name, url, id, point_geom, poly_geom)
    VALUES ('test', 'http://myurl', 26971012, 
     "ST_GeomFromText('POINT(52.147400 19.050780)',4326)", 
     "ST_GeomFromText('POLYGON(( 52.146542 19.050557, 52.148430 19.045527, 52.149525 19.045831, 52.147400 19.050780, 52.147400 19.050780, 52.146542 19.050557))',4326)");

This causes the insert to fail and PostGIS is claiming that it's not a proper Geometry. When I print each one of the params to view on the screen, there is nothing funny going on with the quotes so I'm thinking that the problem must be in the execute command. I am using Python 2.7...Can anyone lend a hand on how to prevent this craziness from continuing?

    conn = psycopg2.connect('dbname=mydb user=postgres password=password')
    cur = conn.cursor()
    SQL = 'INSERT INTO my_table (name, url, id, point_geom, poly_geom) VALUES (%s,%s,%s,%s,%s);'
    Data = name, url, id, point, polygon
    #print Data
    cur.execute(SQL, Data)
    conn.commit()
    cur.close()
    conn.close()

Answer 1

You are passing the string ST_GeomFromText('POINT(..)') as a parameter, and psycopg2 is escaping it. However ST_GeomFromText(..) is a PostGIS function, not data you are inserting.

To fix this you need to move the ST_GeomFromText function into the static SQL. For example:

sql = '''
   INSERT INTO foo (point_geom, poly_geom)
   VALUES (ST_PointFromText(%s, 4326), ST_GeomFromText(%s, 4326))'''
params = ['POINT( 20 20 )', 'POLYGON(( 0 0, 0 10, 10 10, 10 0, 0 0 ))']
cur.execute(sql, params)