Блог

Предполагая, что у меня есть база данных с таблицей users со строкой:

ID = 0, name = 'myName'

Я могу получить значение идентификатора (при условии, что я подключен), используя либо:

 cursor.execute("""SELECT ID FROM users WHERE %s = %s""" % ('name', 'myName'))
print(cursor.fetchone())
 

или

 cursor.execute("""SELECT ID FROM users WHERE {} = {}""".format('name', 'myName'))
print(cursor.fetchone())
 

которые оба работают отлично, но являются плохой практикой в соответствии с документацией.

В документации предлагается использовать либо qmark, либо именованный стиль для запросов с переменным вводом. Проблема возникает при использовании предложенных стилей в SELECT * FROM myDatabase WHERE ? = ? запросе.

стиль qmark:

 cursor.execute("""SELECT ID FROM users WHERE ? = ?""", ('name', 'myName'))
print(cursor.fetchone())
 

именованный стиль

 cursor.execute("""SELECT ID FROM users WHERE :column = :cell""", {'column': 'name', 'cell':'myName'})
print(cursor.fetchone())
 

Использование любого из последних стилей приводит к None возвращению. При использовании qmark или именованного стиля в контексте INSERT , подобном тому, который используется в примере в документации, он работает должным образом.

(Чтобы уточнить, в стиле qmark добавление круглых скобок к каждому ? или ко всему ? = ? не меняет результата. Использование круглых скобок с каждым ? и использование дополнительного аргумента приводит execute() к сбою из-за слишком большого количества приведенных аргументов.)

Что вызывает такое поведение? Можно ли использовать qmark или именованный стиль в SELECT...WHERE ? = ? запросе?

Подстановка параметров предназначена для значений, а не идентификаторов (имен столбцов и таблиц и т.д.). СУБД имеют разные правила для цитирования значений и идентификаторов. Использование заполнителя подстановки параметров для идентификаторов приводит к неправильному указанию идентификатора в кавычках, например

 cur.execute('SELECT * FROM tbl WHERE ? = ?', ('col1', 42))
 

заканчивается тем, что

 SELECT * FROM tbl WHERE 'col1' = 42
 

обратите внимание на одинарные кавычки вокруг col1, которые приводят к тому, что он оценивается как строка, а не имя столбца.

Если вы хотите, чтобы в вашем запросе были динамические идентификаторы, а также значения, используйте форматирование строк для идентификаторов и замену параметров для значений. Например, использование двойных кавычек для идентификатора

 cur.execute('SELECT * FROM tbl WHERE "{}" = ?'.format('col1'), (42,))
 

Вот пример форматирования строк, приводящего к ошибке

 >>> conn = sqlite3.connect(':memory:')
>>> conn.execute('create table tbl (col1 date)')
<sqlite3.Cursor object at 0x7f56abcf1ce0>
>>> cur = conn.cursor()
>>> cur.execute('INSERT INTO tbl (col1) VALUES(?)', ('2021-05-01',))
<sqlite3.Cursor object at 0x7f56abc8f030>
>>> cur.execute('INSERT INTO tbl (col1) VALUES(%s)' % '2021-05-01')
<sqlite3.Cursor object at 0x7f56abc8f030>
>>> conn.commit()
>>> cur.execute('SELECT col1 FROM tbl WHERE %s = %s' % ('col1', '2021-05-01'))
<sqlite3.Cursor object at 0x7f56abc8f030>
>>> for row in cur:print(row)
... 
(2015,)
 

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