построить динамический 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-запроса для выполнения:

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)

Как видите, этоs работает, но этот способ некорректен из-за проблемы преобразования между объектом 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()
 reyman6409 нояб. 2012 г., 10:03
Я обновляю простым примером и трассировкой ошибки :)
 Craig Ringer09 нояб. 2012 г., 02:32
Какие'сгенерированный текст запроса из вашей программы? И что's сообщение об ошибке?

Ответы на вопрос(3)

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

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

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

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

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

from psycopg2.extensions import AsIs
<snip>
with transaction() as cur:
    # WARNING: not secure
    cur.execute('SELECT * from %(table)s', {'table': AsIs('mytable')})
</snip>
Решение Вопроса

вероятно, могли быЯ видел это сразу, если выЯ только что посмотрел журнал ошибок 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)');'

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

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

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

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

БУДЬТЕ ОЧЕНЬ ОСТОРОЖНЫ при выполнении такой интерполяции строклегкий путь дляSQL-инъекция, Я'Вы сделали очень грубое цитирование в коде, показанном выше, но яЯ хочу использовать правильную функцию цитирования идентификатора, если ваша клиентская библиотека предлагает такую функцию.

 reyman6409 нояб. 2012 г., 11:36
Спасибо ! Я обновляю свою проблему полным рабочим решением / программой с созданием базы данных, основываясь на вашем ответе.

В этом примере яЯ приму полигон в качестве словаря из вашего CSV-файла. Ключами могут быть name, url, id, point_geom, poly_geom, как упоминалось выше, но они выиграли 'Это действительно важно, если структура таблицы содержит одинаковые ключи.

Там'Это, вероятно, способ сократить это, но я надеюсь, что это проясняет использование функций sql, а именно:sql.SQLsql.Identifier, а такжеsql.Placeholder и как объединить список строк.sql.SQL('..').join(list())

from psycopg2 import sql
table = 'my_table'
polygon = Polyogon.from_file()  # or something
column_list = list()
value_list = list()

# Convert the dictionary to lists
for column, value in polygon.items():
    column_list.append(sql.Identifier(column))  # Convert to identifiers
    value_list.append(value)

# Build the query, values will be inserted later
query = sql.SQL("INSERT INTO {} ({}) VALUES ({}) ON CONFLICT DO NOTHING").format(
                sql.Identifier(table),
                sql.SQL(', ').join(column_list),  # already sql.Identifier
                sql.SQL(', ').join([sql.Placeholder()] * len(value_list)))

# Execute the cursor
with postgres.cursor() as p_cursor:
    # execute requires tuples and not a list
    p_cursor.execute(insert_query, tuple(value_list))  

Ссылка:http://initd.org/psycopg/docs/sql.html

Ваш ответ на вопрос