使用psycopg2 python库并使用良好的转换类型工具构建SQL动态查询 [英] build SQL dynamic query with psycopg2 python library and using good conversion type tools
问题描述
我在设计一个好的算法时遇到了一些问题,该算法使用了psycopg2库的规范,描述了
I have some problem to design a good algorithm which use specification of psycopg2 library described here
我想建立一个与该字符串相等的动态查询:
I want to build a dynamic query equal to this string :
SELECT ST_GeomFromText('POLYGON((0.0 0.0,20.0 0.0,20.0 20.0,0.0 20.0,0.0 0.0))');
如您所见,我的POLYGON对象包含多个点,请读取一个简单的csv文件some.csv,其中包含:
As you can see, my POLYGON object contain multiple point, read in a simple csv file some.csv which contain :
0.0;0.0
20.0;0.0
20.0;20.0
0.0;20.0
0.0;0.0
所以我动态地构建查询,即csv中行数/数据数的函数.
So i build the query dynamically, function of the number of line/data in the csv.
这是我的程序,用于生成要执行的SQL查询字符串:
Here my program to generate the SQL Query string to execute :
import psycopg2
import csv
# list of points
lXy = []
DSN= "dbname='testS' user='postgres' password='postgres' host='localhost'"
conn = psycopg2.connect(DSN)
curs = conn.cursor()
def genPointText(curs,x,y):
generatedPoint = "%s %s" % (x,y)
return generatedPoint
#Lecture fichier csv
polygonFile = open('some.csv', 'rb')
readerCSV = csv.reader(polygonFile,delimiter = ';')
for coordinates in readerCSV:
lXy.append(genPointText(curs,float(coordinates[0]),float(coordinates[1])))
# function of list concatenation by separator
def convert(myList,separator):
return separator.join([str(i) for i in myList])
# construct simple query with psycopg
def genPolygonText(curs,l):
# http://initd.org/psycopg/docs/usage.html#python-types-adaptation
generatedPolygon = "POLYGON((%s))" % convert(l, ",")
return generatedPolygon
def executeWKT(curs,geomObject,srid):
try:
# geometry ST_GeomFromText(text WKT, integer srid);
finalWKT = "SELECT ST_GeomFromText('%s');" % (geomObject)
print finalWKT
curs.execute(finalWKT)
except psycopg2.ProgrammingError,err:
print "ERROR = " , err
polygonQuery = genPolygonText(curs,lXy)
executeWKT(curs,polygonQuery,4326)
如您所见,这是可行的,但是由于python对象和sql postgresql对象之间的转换问题,这种方式是不正确的.
As you can see, that's works, but this way is not correct because of conversion problem between python object and sql postgresql object.
在文档中,我仅看到为静态查询提供和转换数据的示例.您是否知道一种优雅"的方法来在动态构建的查询中创建具有正确类型的正确字符串?
In the documentation, i see only example to feed and convert data for static query. Do you know an "elegant" way to create correct string with correct type in a dynamic build for query ?
更新1:
如您所见,当我在此简单示例上使用psycopg类型转换函数时,出现类似以下错误:
As you can see, when i use psycopg type transformation function on this simple example, i have error like this :
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)"
name = "my_table"
try:
curs.execute('INSERT INTO %s(name, url, id, point_geom, poly_geom) VALUES (%s);', (name,query))
except psycopg2.ProgrammingError,err:
print "ERROR = " , err
错误均等:
ERROR = ERREUR: erreur de syntaxe sur ou près de « E'my_table' »
LINE 1: INSERT INTO E'my_table'(name, poly_geom) VALUES (E'ST_GeomFr...
更新2:
最终代码有效,这要感谢stackoverflow用户!
Final code which work thanks to stackoverflow users !
#info lib : http://www.initd.org/psycopg/docs/
import psycopg2
# info lib : http://docs.python.org/2/library/csv.html
import csv
# list of points
lXy = []
DSN= "dbname='testS' user='postgres' password='postgres' host='localhost'"
print "Opening connection using dns:", DSN
conn = psycopg2.connect(DSN)
curs = conn.cursor()
def genPointText(curs,x,y):
generatedPoint = "%s %s" % (x,y)
return generatedPoint
#Lecture fichier csv
polygonFile = open('some.csv', 'rb')
readerCSV = csv.reader(polygonFile,delimiter = ';')
for coordinates in readerCSV:
lXy.append(genPointText(curs,float(coordinates[0]),float(coordinates[1])))
# function of list concatenation by separator
def convert(myList,separator):
return separator.join([str(i) for i in myList])
# construct simple query with psycopg
def genPolygonText(l):
# http://initd.org/psycopg/docs/usage.html#python-types-adaptation
generatedPolygon = "POLYGON((%s))" % convert(l, ",")
return generatedPolygon
def generateInsert(curs,tableName,name,geomObject):
curs.execute('INSERT INTO binome1(name,geom) VALUES (%s, %s);' , (name,geomObject))
def create_db_binome(conn,name):
curs = conn.cursor()
SQL = (
"CREATE TABLE %s"
" ("
" polyname character varying(15),"
" geom geometry,"
" id serial NOT NULL,"
" CONSTRAINT id_key PRIMARY KEY (id)"
" )"
" WITH ("
" OIDS=FALSE"
" );"
" ALTER TABLE %s OWNER TO postgres;"
) %(name,name)
try:
#print SQL
curs.execute(SQL)
except psycopg2.ProgrammingError,err:
conn.rollback()
dropQuery = "ALTER TABLE %s DROP CONSTRAINT id_key; DROP TABLE %s;" % (name,name)
curs.execute(dropQuery)
curs.execute(SQL)
conn.commit()
def insert_geometry(polyname,tablename,geometry):
escaped_name = tablename.replace('""','""')
try:
test = 'INSERT INTO %s(polyname, geom) VALUES(%%s, ST_GeomFromText(%%s,%%s))' % (escaped_name)
curs.execute(test, (tablename, geometry, 4326))
conn.commit()
except psycopg2.ProgrammingError,err:
print "ERROR = " , err
################
# PROGRAM MAIN #
################
polygonQuery = genPolygonText(lXy)
srid = 4326
table = "binome1"
create_db_binome(conn,table)
insert_geometry("Berlin",table,polygonQuery)
insert_geometry("Paris",table,polygonQuery)
polygonFile.close()
conn.close()
推荐答案
您正试图将表名作为参数传递.如果仅查看PostgreSQL错误日志,您可能会立即看到.
You are trying to pass a table name as a parameter. You probably could've seen this immediately if you'd just looked at the PostgreSQL error log.
您试图通过psycopg2作为参数传递的表名被转义,从而产生如下查询:
The table name you're trying to pass through psycopg2 as a parameter is being escaped, producing a query like:
INSERT INTO E'my_table'(name, url, id, point_geom, poly_geom) VALUES (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)');'
这不是您想要的,不会起作用;您无法转义像文字一样的表名.您必须使用普通的Python字符串插值来构造动态SQL,只能将参数化的语句占位符用于实际文字值.
This isn't what you intended and won't work; you can't escape a table name like a literal. You must use normal Python string interpolation to construct dynamic SQL, you can only use parameterized statement placeholders for actual literal values.
params = ('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)
escaped_name = name.replace('"",'""')
curs.execute('INSERT INTO "%s"(name, url, id, point_geom, poly_geom) VALUES (ST_GeomFromText(%%s,%%s));' % escaped_name, params)
看看我如何直接对名称进行插值以产生查询字符串:
See how I've interpolated the name directly to produce the query string:
INSERT INTO my_table(name, url, id, point_geom, poly_geom) VALUES (ST_GeomFromText(%s,%s));
(%%
通过%替换转换为纯%
).然后,我将该查询与定义POLYGON
的字符串以及ST_GeomFromText
的另一个参数作为查询参数一起使用.
(%%
gets converted to plain %
by % substitution). Then I'm using that query with the string defining the POLYGON
and the other argument to ST_GeomFromText
as query parameters.
我还没有测试过,但是它应该为您提供正确的想法并帮助您解释问题所在.
I haven't tested this, but it should give you the right idea and help explain what's wrong.
要格外小心,在进行这样的字符串插值时,这是 SQL注入.我已经在上面显示的代码中做了非常粗略的引用,但是如果您的客户端库提供了一个标识符引用功能,我想使用一个适当的标识符引用功能.
BE EXTEMELY CAREFUL when doing string interpolation like this, it's an easy avenue for SQL injection. I've done very crude quoting in the code shown above, but I'd want to use a proper identifier quoting function if your client library offers one.
这篇关于使用psycopg2 python库并使用良好的转换类型工具构建SQL动态查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!