使用Python插入和字符串连接 [英] INSERT INTO and String Concatenation with Python

问题描述

在将数据插入到数据库中时，我遇到了重大的减速.您可以从下面的代码中看到，我只是在构建SQL语句以传递给execute命令.值是正确的，并且一切都很好，但是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));

这在Postgres查询编辑器中是可验证的...现在，当我运行下面的Python代码时，它将在ST_GeomFromText函数周围添加双引号，然后从id列中删除引号.

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

这将导致插入失败，并且PostGIS声称这不是正确的几何图形.当我打印每个参数以在屏幕上查看时，引号并没有发生什么有趣的事情，因此我认为问题必须出在execute命令中.我正在使用Python 2.7 ...有人可以伸出援手防止这种疯狂继续吗?

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

推荐答案

您正在将字符串ST_GeomFromText('POINT(..)')作为参数传递，而psycopg2正在对其进行转义.但是ST_GeomFromText(..)是PostGIS函数，而不是您要插入的数据.

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.

要解决此问题，您需要将ST_GeomFromText函数移到静态SQL中.例如:

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)

这篇关于使用Python插入和字符串连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！