Вставка Psycopg2 в таблицу с заполнителями

Это может быть довольно глупый вопрос, но что я делаю неправильно здесь? Он создает таблицу, но INSERT INTO не работает, наверное, я что-то не так делаю с заполнителями?

conn = psycopg2.connect("dbname=postgres user=postgres")
cur = conn.cursor()
escaped_name = "TOUR_2"
cur.execute('CREATE TABLE %s(id serial PRIMARY KEY, day date, elapsed_time varchar,    net_time varchar, length float, average_speed float, geometry GEOMETRY);' % escaped_name)

cur.execute('INSERT INTO %s (day,elapsed_time, net_time, length, average_speed, geometry) VALUES (%s, %s, %s, %s, %s, %s)', (escaped_name, day ,time_length,  time_length_net, length_km, avg_speed,  myLine_ppy))

conn.commit()
cur.close()
conn.close()

Вызов INSERT INTO не работает, он дает мне

cur.execute('INSERT INTO %s (day,elapsed_time, net_time, length, average_speed,  
geometry) VALUES (%s, %s, %s, %s, %s, %s)'% (escaped_name, day ,time_length,  
time_length_net, length_km, avg_speed,  myLine_ppy))
psycopg2.ProgrammingError: syntax error at or near ":"
LINE 1: ...h, average_speed, geometry) VALUES (2013/09/01 , 2:56:59, 02...

Может ли кто-нибудь помочь мне в этом? Огромное спасибо!


python psycopg2 postgresql postgis psycopg
person Timothy Dalton    schedule 07.10.2013    source источник

Ответы (3)


arrow_upward
33
arrow_downward

Вы используете форматирование строк Python, и это очень плохая идея (TM). Подумайте о SQL-инъекции. Правильный способ сделать это - использовать связанные переменные:

cur.execute('INSERT INTO %s (day, elapsed_time, net_time, length, average_speed, geometry) VALUES (%s, %s, %s, %s, %s, %s)', (escaped_name, day, time_length, time_length_net, length_km, avg_speed, myLine_ppy))

где кортеж параметров задается в качестве второго аргумента для execute(). Также вам не нужно экранировать никакое значение, psycopg2 сделает экранирование за вас. В этом конкретном случае также предлагается не передавать имя таблицы в переменную (escaped_name), а вставлять его в строку запроса: psycopg2 не умеет заключать в кавычки имена таблиц и столбцов, только значения.

См. документацию psycopg2:

https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries

Если вы хотите программно сгенерировать оператор SQL, обычно используется форматирование Python для оператора и привязка переменных для аргументов. Например, если у вас есть имя таблицы в escaped_name, вы можете сделать:

query = "INSERT INTO %s (col1, ...) VALUES (%%s, ...)" % escaped_name
curs.execute(query, args_tuple)

Очевидно, что для использования заполнителей в вашем запросе вам нужно заключить в кавычки любые %, которые вводят связанный аргумент в первом формате.

Обратите внимание, что это безопасно в том и только в том случае, если escaped_name генерируется вашим кодом, игнорирующим любые внешние входные данные (например, базовое имя таблицы и счетчик), но существует риск внедрения SQL, если вы используете предоставленные данные пользователем.

person fog    schedule 07.10.2013
comment
+1 Чтобы процитировать документ: Предупреждение Никогда, никогда, НИКОГДА не используйте конкатенацию строк Python (+) или интерполяцию строковых параметров (%) для передачи переменных в строку запроса SQL. Даже под прицелом. - person Bruno; 08.10.2013
comment
Спасибо туман, теперь понял. Я искал решение, потому что хочу автоматизировать процесс создания таблицы с порядковым номером, т.е. е. TOUR+'i', где 'i' будет числом, и не всегда будет писать новый оператор SQL. У вас есть идеи, как это сделать? - person Timothy Dalton; 08.10.2013
comment
@TimothyDalton Обычный способ автоматического создания операторов SQL - использовать форматирование Python для создания оператора и привязки переменных для передачи аргументов. Смотрите мой обновленный ответ. - person fog; 08.10.2013
comment
Еще раз спасибо, туман, теперь он работает, используя %% для моих значений, а не только %. Я не мог найти ничего по этому поводу в Интернете. Второй % указывает оператору получить значения из моих вторых параметров, в данном случае args_tuple, верно? - person Timothy Dalton; 08.10.2013
comment
@TimothyDalton Скрыто в документах: When parameters are used, in order to include a literal % in the query you can use the %% string - person ChaimKut; 08.05.2014
comment
Почему предложения Правильный способ сделать это.. и В данном конкретном случае.. противоречат друг другу, причем пример в первом предложении о том, как что-то сделать с последним предложением указывает на невозможность? -1 - person jwg; 20.01.2017

arrow_upward
3
arrow_downward

Начиная с psycopg2 v2.7 существует поддерживаемый способ сделать это: см. psycopg2.sql документы.

person Matt    schedule 08.06.2017
comment
Здесь было бы неплохо привести более конкретный пример. - person Ilja Everilä; 17.10.2020

arrow_upward
2
arrow_downward

Чтобы расширить ответ @Matt, заполнители не работают для идентификаторов, таких как имена таблиц, потому что имя будет заключено в кавычки как строковое значение и приведет к недопустимому синтаксису.

Если вы хотите сгенерировать такой запрос динамически, вы можете использовать упомянутый в pyscopg2.sql модуле. :

from psycopg2.sql import Identifier, SQL

cur.execute(SQL("INSERT INTO {} VALUES (%s)").format(Identifier('my_table')), (10,))
person Eric Smith    schedule 28.06.2021