Правильно отформатируйте SQL-запрос при вставке в переменное количество столбцов
Я использую psycopg2 для взаимодействия с базой данных PostgreSQL. У меня есть функция, с помощью которой можно вставить любое количество столбцов (от одного столбца до всех столбцов) в таблице. Мой вопрос: как правильно, динамически построить этот запрос?
В настоящий момент я использую форматирование и конкатенацию строк и знаю, что это абсолютный способ худший для этого. Рассмотрим приведенный ниже код, где в данном случае мое неизвестное количество столбцов (т.е. ключей от dict фактически равно 2):
dictOfUnknownLength = {'key1': 3, 'key2': 'myString'}
def createMyQuery(user_ids, dictOfUnknownLength):
fields, values = list(), list()
for key, val in dictOfUnknownLength.items():
fields.append(key)
values.append(val)
fields = str(fields).replace('[', '(').replace(']', ')').replace("'", "")
values = str(values).replace('[', '(').replace(']', ')')
query = f"INSERT INTO myTable {fields} VALUES {values} RETURNING someValue;"
query = INSERT INTO myTable (key1, key2) VALUES (3, 'myString') RETURNING someValue;
Это обеспечивает правильно отформатированный запрос, но, конечно, подвержен SQL-инъекциям и т.п. и, как таковой, не является приемлемым методом достижения моей цели.
В других запросах я использую методы построения запросов рекомендуемые при обработке известного количества переменных (%s и отдельный аргумент для .execute(), содержащий переменные), но я не уверен, как это адаптировать для размещения неизвестного количества переменных без использования форматирования строк.
Как я могу элегантно и безопасно построить запрос с неизвестным количеством указанных столбцов вставки?
Ответы 1
Кроме того, текущая методология с использованием .replace() склонна к крайним случаям, когда поля или значения содержат [, ] или '. Они будут заменены не важно что и могут испортить ваш запрос.
Вы всегда можете использовать .join(), чтобы присоединиться к количеству значений Переменная в вашем списке. Чтобы дополнить его, формат запрос соответствующим образом с %s после VALUES и передайте свои аргументы в .execute().
Примечание: вы также можете рассмотреть случай, когда количество полей не равно числовым значениям.
import psycopg2
conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()
dictOfUnknownLength = {'key1': 3, 'key2': 'myString'}
def createMyQuery(user_ids, dictOfUnknownLength):
# Directly assign keys/values.
fields, values = list(dictOfUnknownLength.keys()), list(dictOfUnknownLength.values())
if len(fields) != len(values):
# Raise an error? SQL won't work in this case anyways...
pass
# Stringify the fields and values.
fieldsParam = ','.join(fields) # "key1, key2"
valuesParam = ','.join(['%s']*len(values))) # "%s, %s"
# "INSERT ... (key1, key2) VALUES (%s, %s) ..."
query = 'INSERT INTO myTable ({}) VALUES ({}) RETURNING someValue;'.format(fieldsParam, valuesParam)
# .execute('INSERT ... (key1, key2) VALUES (%s, %s) ...', [3, 'myString'])
cur.execute(query, values) # Anti-SQL-injection: pass placeholder
# values as second argument.
Убедиться, что len(fields)==len(values) - очень хороший аргумент, спасибо.
Я считаю, что этот подход приведет к тому, что имена столбцов будут обрабатываться как строки и, таким образом, инкапсулированы в
' 'и, следовательно, будут недопустимым синтаксисом SQL?