使用 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

您将字符串 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)

Answer 2

最好（编辑：比你正在做的事情，而不是第一个答案）做这样的事情：

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 查询字符串上使用字符串格式化方法的诱惑，即使这些事情可能会令人沮丧。