python psycopg2使用_使用psycopg2 python库并使用良好的转换类型工...

我有一个问题要设计一个好的算法,该算法使用描述为here的psycopg2库的规范

我想建立一个等于此字符串的动态查询：

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,其中包含：

0.0;0.0

20.0;0.0

20.0;20.0

0.0;20.0

0.0;0.0

所以我动态地建立查询,在CSV功能行/数据的数量.

在这里,我的程序生成要执行的SQL查询字符串：

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对象之间的转换问题,这种方式是不正确的.

在文档中,我仅看到为静态查询提供和转换数据的示例.您是否知道一种“优雅”的方法来在动态生成的查询中创建具有正确类型的正确字符串？

更新1：

如您所见,当我在此简单示例上使用psycopg类型转换函数时,出现如下错误：

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用户！

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