Запросы — Python: SQL

• Ускорение запросов
• Возврат идентификатора

Как правило, запросы в базу формируются динамически на основе данных, которые поступают из вне. Например, создание пользователя чаще всего происходит во время регистрации. В таком случае код вставки выглядит так:

name = "Tota"
phone = "1234"

sql = f"INSERT INTO users (username, phone) VALUES ('{name}', '{phone}');"
cursor.execute(sql)
conn.commit()

Запросы составленные таким образом несут в себе серьезную опасность называемую SQL-инъекцией. Вместо обычных данных, пользователь может отправить текст, изменяющий SQL-запрос на тот, который нужен пользователю. Представьте что внутри переменной бы хранилась строка ') INSERT INTO users (username, phone) VALUES ('i am hacker', '777777'). В таком случае, после подстановки мы бы получили следующий запрос:

INSERT INTO users (username, phone) VALUES ('somename', '');
INSERT INTO users (username, phone) VALUES ('i am hacker', '777777');

Фактически вместо одного запроса получилось два. Таким образом, опытный взломщик может выполнить произвольный запрос в базу данных удалив, добавив или изменив все что ему нужно.

В разработке существует правило - никогда не доверяй пользовательским данным. В случае работы с базой данных, это особенно важно. Любые данные перед вставкой должны быть экранированы. Делается это с помощью специальных плейсхолдеров:

name = "John"
age = 19

with conn.cursor() as curs:
    # для позиционных аргументов всегда передается последовательность, даже если параметр один
    # здесь передается кортеж (name,)
    curs.execute("SELECT id, name FROM users WHERE name=%s;", (name,))
    curs.fetchall()

with conn.cursor() as curs:
    # также можно использовать именованные аргументы
    curs.execute(
        "INSERT INTO users (name, age) VALUES (%(name)s, %(age)s);",
        {"age": age, "name": name},
    )
    conn.commit()

conn.close()

Позиционные плейсхолдеры %s, которые расставляются в тех местах, где ожидается подстановка данных. Также плейсхолдеры могут быть и именованными %(имя)s.

Напоследок несколько полезных советов по построению запросов:

• Плейсхолдер должен быть %s даже если тип подставляемого значения отличается от строки
• Не заключайте плейсходер в кавычки
• Если в запросе используется знак %, он должен быть указан как %%

Ускорение запросов

Psycopg2 предоставляет дополнительные функции execute_batch() и execute_values() для исполнения множества запросов за один раз.

from psycopg2.extras import execute_batch, execute_values

users = (
    ("Bob", "bob@mail.com"),
    ("Alice", "alice@mail.com"),
    ("John", "john@mail.com"),
)
execute_batch(curs, "INSERT INTO users (name, email) VALUES (%s, %s)", users)

# в случае execute_values запрос будет выглядеть так
users = [
    ("Bob", "bob@mail.com"),
    ("Alice", "alice@mail.com"),
    ("John", "john@mail.com"),
]
execute_values(curs, "INSERT INTO users (name, email) VALUES %s", users)

Возврат идентификатора

Когда мы вставляем данные в базу, иногда нам нужно получить идентификатор вставленной записи и потом использовать его в коде. Например, когда мы создаем какую-то сущность и хотим потом ее использовать:

user = User()
# Сохраняем пользователя в базу данных
# После этого становится доступен id
id = user.get_id()
# Его можно использовать для формирования ссылок или вставки связанных записей

К сожалению, Psycopg2 не предоставляет встроенного инструмента для получения id записей, но для этого мы можем использовать синтаксис SQL:

# RETURNING возвращает указанное поле
 with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id;",
            (user.name, user.email)
        )
        user.id = cur.fetchone()[0]
conn.close()

Самостоятельная работа

Выполните шаги из урока у себя на компьютере

Добавьте в базу данных несколько пользователей, используя плейсхолдеры

Используя плейсхолдеры, удалите одного из созданных пользователей по его имени

Дополнительные материалы

1. Передача параметров