построить динамический запрос SQL с помощью библиотеки python psycopg2 и использовать хорошие инструменты для преобразования типов

У меня есть некоторая проблема для разработки хорошего алгоритма, который использует спецификацию библиотеки 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 Query для выполнения:

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

2 Solutions collect form web for “построить динамический запрос SQL с помощью библиотеки python psycopg2 и использовать хорошие инструменты для преобразования типов”

Вы пытаетесь передать имя таблицы в качестве параметра. Вероятно, вы могли это увидеть сразу, если бы посмотрели журнал ошибок PostgreSQL.

Имя таблицы, которую вы пытаетесь передать через psycopg2 в качестве параметра, экранируется, создавая запрос типа:

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

Это не то, что вы намеревались и не будете работать; вы не можете избежать имени таблицы, как литерал. Для построения динамического SQL вы должны использовать стандартную интерполяцию строк Python, вы можете использовать только параметризованные заполнители операторов для фактических значений литерала.

 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) 

Посмотрите, как я интерполировал имя непосредственно для создания строки запроса:

 INSERT INTO my_table(name, url, id, point_geom, poly_geom) VALUES (ST_GeomFromText(%s,%s)); 

( %% преобразуется в обычную замену % на%). Затем я использую этот запрос со строкой, определяющей ST_GeomFromText а другой аргумент – ST_GeomFromText качестве параметров запроса.

Я не тестировал это, но он должен дать вам правильную идею и помочь объяснить, что случилось.

Будьте предельно осторожны при выполнении строковой интерполяции, такой как это, это простой способ для SQL-инъекции . Я сделал очень грубую цитату в приведенном выше коде, но я бы хотел использовать правильную функцию цитирования идентификатора, если ваша клиентская библиотека предлагает ее.

Правильный способ – использовать новый sql модуль psycopg2 2.7, который включает в себя объект Identifier . Это позволяет вам динамически определять SQL-идентификаторы безопасным способом.

К сожалению, 2.7 еще не на PyPi (2.6.2 на момент написания).

До тех пор psycopg2 охватывает это под заголовком «Как передать имена полей / таблиц в запрос?» http://initd.org/psycopg/docs/faq.html#problems-with-type-conversions

Вы можете передавать идентификаторы SQL вместе с значениями данных в функцию выполнения с помощью функции AsIs .

Примечание: это не обеспечивает безопасности . Это так же хорошо, как использование строки формата, которая не рекомендуется. Единственное реальное преимущество этого – вы поощряете будущий код следовать стилю execute +. Вы также можете легко найти AsIs в будущем.

 from psycopg2.extensions import AsIs <snip> with transaction() as cur: # WARNING: not secure cur.execute('SELECT * from %(table)s', {'table': AsIs('mytable')}) 
  • Как преобразовать int в Enum в python?
  • Получение значений с правильным типом в Redis
  • Как преобразовать булевой массив в массив int
  • Преобразование всего кадра данных из нижнего регистра в верхний регистр с помощью Pandas
  • Python - лучший язык программирования в мире.