INSERT INTO and String Concatenation with Python

问题:

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()

回答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)

回答2:

It is better (edit: than what you are doing, not than 1st answer) to do something like this:

select st_asgeojson(the_geom_ls) from atlas where the_geom_ls &&\
st_geomfromtext('POLYGON((%s %s, %s %s, %s %s, %s %s, %s %s))', 4326)

And pass that to your adapter with the coords in a tuple, they will be parsed correctly. This is by far the least painful way to do it. If you really have to construct the strings separately, you might clean up the escaping by looking a the output of cur.mogrify:

cur.mogrify('INSERT INTO my_table (name, url, id, point_geom, poly_geom) VALUES (%s,%s,%s,%s,%s);', (1,2,3,4,5))
>>>'INSERT INTO my_table (name, url, id, point_geom, poly_geom) VALUES (1,2,3,4,5);'

query = "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)"

cur.mogrify('INSERT INTO my_table (name, url, id, point_geom, poly_geom) VALUES (%s,%s,%s,%s,%s);', (1,2,3,4,query))
>>> "INSERT INTO my_table (name, url, id, point_geom, poly_geom) VALUES (1,2,3,4,E'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)');"

Resist the temptation to use a string formatting method on a sql query string, even though these things can be frustrating.