使用Python进行INSERT INTO和字符串拼接

Question

我在往数据库里插入数据时遇到了一个大麻烦。从下面的代码可以看到，我只是简单地构建了一个SQL语句，然后传给执行命令。值是正确的，没问题，但在运行时，Python解释器似乎在参数上加了引号又去掉了引号。

这是往数据库里插入空间数据的正确方法。

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

在Postgres的查询编辑器中可以验证这一点……现在，当我运行下面的Python代码时，它在ST_GeomFromText函数周围加了双引号，然后又把id列的引号去掉了。

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)");

这导致插入失败，PostGIS说这不是一个合适的几何体。当我打印每一个参数在屏幕上查看时，发现引号没有什么奇怪的情况，所以我觉得问题可能出在执行命令上。我使用的是Python 2.7……有没有人能帮我解决这个问题，让这种疯狂的情况不再继续？

    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

这样做会更好（编辑：是指比你现在的做法更好，而不是比第一个答案更好）：

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)

然后把这个传给你的适配器，坐标会以元组的形式正确解析。这是目前为止最简单的方法。如果你真的需要单独构建字符串，可以通过查看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)');"

不要被使用字符串格式化方法来处理SQL查询字符串的诱惑所吸引，尽管这些事情可能会让人感到沮丧。

Answer 2

你传递的字符串是 ST_GeomFromText('POINT(..)')，而 psycopg2 正在对它进行转义。不过，ST_GeomFromText(..) 是一个 PostGIS 函数，而不是你要插入的数据。

要解决这个问题，你需要把 ST_GeomFromText 函数放到静态 SQL 里。比如：

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)