Запрос из Python: execute, параметры, commit и fetch

Что мы изучим?#

До сих пор мы писали SQL «вручную» в Database Client. Теперь подключаем его к коду — по пути, который подчёркивает программа курса:

Python  →  Connection  →  Query  →  Database  →  Result  →  Python
(values)   (open)         (SQL +    (runs it)     (rows)     (tuples,
                           params)                            ints, strs)

• execute с параметрами (%s).
• SQL Injection: опасность и почему её решают параметры.
• commit: сохранение изменений.
• lastrowid: id, который база дала новой строке.
• fetchone / fetchall на настоящих данных.

Зачем это нужно?#

В API значения приходят от пользователя: имя, возраст и email из тела POST. Код должен безопасно поместить их в запрос, сохранить и вернуть сохранённое, включая новый id (201 Created + студент). Всё это происходит в этом уроке, в настоящем коде.

Объяснение с основ#

execute(sql, params)#

cursor.execute(
    "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)",
    ("Dana", 24, "dana@mail.com"),
)

• В SQL вместо каждого значения пишут %s (заполнитель, Placeholder). В MySQL это всегда %s — для любого типа, в том числе для чисел.
• Значения передаются отдельно, в виде tuple, в том же порядке, что и заполнители.
• Драйвер (mysql.connector) безопасно ставит их на место: строки как строки, числа как числа. Не нужны кавычки и не нужно преобразование.

SQL Injection — почему нельзя строить SQL конкатенацией#

Кажется «простым» построить запрос самим:

name = input("Student name to search: ")
sql = "SELECT id, name FROM students WHERE name = '" + name + "'"

Если пользователь вводит Dana, получается запрос ... WHERE name = 'Dana'. Нормально. Но если он введёт:

x' OR '1'='1

Получится запрос:

SELECT id, name FROM students WHERE name = 'x' OR '1'='1'

'1'='1' всегда истинно, поэтому возвращаются все студенты. Пользователь изменил смысл запроса через ввод. Это SQL Injection (SQL-инъекция) — одна из самых распространённых и самых опасных уязвимостей в мире. Через неё взламывают базы данных, крадут пароли и удаляют таблицы.

С %s и параметрами драйвер всегда воспринимает ввод как значение и никогда — как часть SQL. Пользователь может ввести что угодно, и база просто будет искать студента, у которого имя — эта странная строка.

Как именно %s защищает?#

При конкатенации атака работает из-за одной кавычки: кавычка, которую пользователь ввёл после x, закрывает наш текст, и всё, что после неё (OR '1'='1'), уже читается как часть команды.

С %s драйвер подготавливает значение перед отправкой: он добавляет обратную косую черту (\) перед каждой кавычкой во вводе. Обратная косая черта перед кавычкой говорит MySQL: «это обычная кавычка внутри текста, а не конец текста».

Кавычка пользователя больше не может закрыть текст, поэтому весь ввод остаётся внутри как одно имя. Нет никакого OR, ставшего частью команды, и MySQL ищет студента с таким именем — и не находит ([]).

А почему не сделать это самим? Потому что легко пропустить случай: двойную кавычку, обратную косую черту во вводе, особую кодировку. Драйвер умеет обрабатывать их все, каждый раз, — поэтому всегда %s.

commit — настоящее сохранение#

Команды, которые меняют данные (INSERT, UPDATE, DELETE), сначала попадают в открытую транзакцию (Transaction). Изменение видно внутри текущего соединения, но оно не сохранено окончательно, пока вы не вызовете connection.commit(). Если закрыть соединение без commit, изменения выбрасываются.

• SELECT не нуждается в commit, потому что ничего не меняет.
• CREATE TABLE в MySQL сохраняется сразу, даже без commit.

lastrowid и rowcount#

После execute в курсоре есть информация о том, что произошло:

Синтаксис#

connection = get_connection()
try:
    cursor = connection.cursor()

    # change data: execute + commit
    cursor.execute("INSERT INTO t (a, b) VALUES (%s, %s)", (value_a, value_b))
    connection.commit()
    new_id = cursor.lastrowid

    # read data: execute + fetch
    cursor.execute("SELECT a, b FROM t WHERE b > %s", (10,))
    rows = cursor.fetchall()
    cursor.execute("SELECT a, b FROM t WHERE id = %s", (new_id,))
    row = cursor.fetchone()
finally:
    connection.close()

Обратите внимание на (10,): у tuple из одного элемента обязательно есть запятая (вспомните кортежи!). (10) — это просто число 10, и execute упадёт.

Первый пример кода#

week-4/school-db/students_demo.py:

import mysql.connector

def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")

connection = get_connection()
try:
    cursor = connection.cursor()
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            age INT NOT NULL,
            email VARCHAR(100) NOT NULL UNIQUE
        )
        """
    )

    cursor.execute(
        "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)",
        ("Dana", 24, "dana@mail.com"),
    )
    connection.commit()
    print("Inserted student with id", cursor.lastrowid)

    cursor.execute("SELECT id, name, age, email FROM students")
    print("All rows:", cursor.fetchall())
finally:
    connection.close()

Вывод (первый запуск, на пустой таблице):

Inserted student with id 1
All rows: [(1, 'Dana', 24, 'dana@mail.com')]

Разбор кода построчно#

Запустите ещё раз:

Traceback (most recent call last):
  ...
mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry 'dana@mail.com' for key 'students.email'

При втором запуске INSERT Даны падает, потому что такой email уже есть, и база обеспечила UNIQUE. (И несмотря на ошибку, соединение закрылось благодаря finally.) Позже в этой теме мы правильно обработаем эту ошибку. А пока, чтобы начать заново: в Database Client выполните DROP TABLE students;.

Ещё один пример#

Добавляем нескольких студентов, поиск с параметрами и демонстрация SQL Injection.

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("DROP TABLE IF EXISTS students")
    cursor.execute(
        "CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, "
        "name VARCHAR(100) NOT NULL, age INT NOT NULL)")
    for name, age in [("Dana", 24), ("Avi", 31), ("Noa", 22)]:
        cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", (name, age))
    connection.commit()

    cursor.execute("SELECT id, name, age FROM students WHERE id = %s", (2,))
    print("By id 2:", cursor.fetchone())

    cursor.execute("SELECT name FROM students WHERE age > %s ORDER BY age", (23,))
    print("Older than 23:", cursor.fetchall())

    cursor.execute("SELECT id, name FROM students WHERE id = %s", (99,))
    print("By id 99:", cursor.fetchone())

    attack = "x' OR '1'='1"

    unsafe_sql = "SELECT name FROM students WHERE name = '" + attack + "'"
    print("UNSAFE query:", unsafe_sql)
    cursor.execute(unsafe_sql)
    print("UNSAFE result:", cursor.fetchall())

    cursor.execute("SELECT name FROM students WHERE name = %s", (attack,))
    print("SAFE result:", cursor.fetchall())
finally:
    connection.close()

Вывод:

By id 2: (2, 'Avi', 31)
Older than 23: [('Dana',), ('Avi',)]
By id 99: None
UNSAFE query: SELECT name FROM students WHERE name = 'x' OR '1'='1'
UNSAFE result: [('Dana',), ('Avi',), ('Noa',)]
SAFE result: []

Разбор:

• DROP TABLE IF EXISTS students — удаляет таблицу, если она есть, чтобы каждый запуск начинался с чистого листа. (Удобно для демонстраций. В настоящем коде так не делают!)
• INSERT в цикле и один commit в конце — три вставки сохраняются вместе.
• fetchone() на существующий id → tuple. На несуществующий id → None. Это и есть «не найдено», которое станет 404.
• (2,), (23,) — tuple из одного значения, с запятой.
• Небезопасная версия вернула всех студентов, потому что ввод изменил сам запрос.
• Безопасная версия искала студента, у которого имя ровно x' OR '1'='1, а такого нет, поэтому []. Атака не удалась.

Типичная ошибка#

Ошибка 1: забыть commit.

cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Tom", 27))
connection.close()

Ошибки нет, и Том не сохранён. Закрытие без commit = отмена изменений. Это одна из самых запутанных ошибок, потому что «всё работало».

Ошибка 2: один параметр без запятой.

cursor.execute("SELECT name FROM students WHERE id = %s", (2))

mysql.connector.errors.ProgrammingError: Could not process parameters: int(2), it must be of type list, tuple or dict

(2) — это int, а не tuple. → (2,).

Ошибка 3: число параметров не совпадает.

cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Tom",))

mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

Ошибка 4: %s в кавычках.

cursor.execute("SELECT * FROM students WHERE name = '%s'", ("Dana",))

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; ... near 'Dana''' at line 1

Драйвер и так сам добавляет кавычки, а теперь кавычки двойные. %s — всегда без кавычек.

Ошибка 5: %s вместо имени таблицы или столбца. "SELECT * FROM %s" не работает: заполнители — только для значений: драйвер превращает students в 'students' (строку), а это синтаксическая ошибка. Имена таблиц и столбцов пишут в коде фиксированными.

Практика#

Упражнения опираются только на то, что пройдено в этом уроке и в предыдущих. Сначала попробуйте сами, и только потом открывайте решение.

Упражнение 1: execute с параметрами#

Напишите функцию add_student(connection, name, age), которая добавляет студента с параметрами (%s), сохраняет и возвращает новый id.

cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT NOT NULL)")
print(add_student(connection, "Dana", 24))
print(add_student(connection, "Avi", 31))
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())

Ожидаемый вывод:

1
2
[(1, 'Dana', 24), (2, 'Avi', 31)]

import mysql.connector


def add_student(connection, name, age):
    cursor = connection.cursor()
    cursor.execute(
        "INSERT INTO students (name, age) VALUES (%s, %s)", (name, age))
    connection.commit()
    return cursor.lastrowid


connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT NOT NULL)")
print(add_student(connection, "Dana", 24))
print(add_student(connection, "Avi", 31))
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())
connection.close()

1
2
[(1, 'Dana', 24), (2, 'Avi', 31)]

Упражнение 2: SQL Injection#

В таблице два студента — Дана и Ави. Создайте её так:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS students")   # start clean, so ids start from 1
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi')")
connection.commit()
connection.close()

Код, который строит запрос из ввода пользователя:

name = "x' OR '1'='1"
sql = "SELECT name FROM students WHERE name = '" + name + "'"

1. Как будет выглядеть созданная команда и что она вернёт?
2. Покажите разницу в коде: выполните запрос через конкатенацию и напечатайте Unsafe: и результат, а затем тот же запрос с %s и напечатайте Safe: и результат.

Ожидаемый вывод:

Unsafe: [('Dana',), ('Avi',)]
Safe: []

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi')")
connection.commit()

name = "x' OR '1'='1"

sql = "SELECT name FROM students WHERE name = '" + name + "'"
cursor.execute(sql)
print("Unsafe:", cursor.fetchall())

cursor.execute("SELECT name FROM students WHERE name = %s", (name,))
print("Safe:", cursor.fetchall())
connection.close()

Unsafe: [('Dana',), ('Avi',)]
Safe: []

Упражнение 3: Забытый commit#

connection = mysql.connector.connect(...)
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.close()

Программа выполнилась без ошибки, но Ноа в базе нет. Почему?

Покажите это в коде: начните с пустой таблицы students (с name и age). Вставьте Ноа без commit, закройте и в новом соединении посчитайте, сколько строк в таблице. Затем вставьте её снова с commit и посчитайте ещё раз.

Ожидаемый вывод:

Without commit: 0 rows
With commit: 1 rows

import mysql.connector


def connect():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def count_rows():
    connection = connect()
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM students")
        return len(cursor.fetchall())
    finally:
        connection.close()


setup = connect()
setup.cursor().execute("CREATE TABLE IF NOT EXISTS students (name VARCHAR(100), age INT)")
setup.close()

connection = connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.close()
print("Without commit:", count_rows(), "rows")

connection = connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.commit()
connection.close()
print("With commit:", count_rows(), "rows")

Without commit: 0 rows
With commit: 1 rows

Упражнение 4: Безопасный поиск по id#

В таблице один студент — Дана, 24 года. Создайте её так:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS students")   # start clean, so ids start from 1
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
connection.commit()
connection.close()

Напишите find_by_id(student_id), которая открывает соединение, ищет студента с параметром и всегда закрывает соединение. Она возвращает строку или None.

print(find_by_id(1))
print(find_by_id(99))

Ожидаемый вывод:

(1, 'Dana', 24)
None

import mysql.connector


def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def find_by_id(student_id):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM students WHERE id = %s", (student_id,))
        return cursor.fetchone()
    finally:
        connection.close()


setup = get_connection()
cursor = setup.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
setup.commit()
setup.close()

print(find_by_id(1))
print(find_by_id(99))

(1, 'Dana', 24)
None

Упражнение 5: Путь запроса#

Расставьте по порядку шаги выполнения запроса из Python:

• (а) connection.commit() — только если мы меняли данные
• (б) mysql.connector.connect(...), а затем connection.cursor()
• (в) connection.close()
• (г) cursor.execute(sql, params)
• (д) cursor.fetchone() / fetchall() — только если мы читали данные

Итоги#

• Путь: Python → Connection → Query (с %s и параметрами) → Database → Result → Python.
• cursor.execute(sql, params) — значения только через %s и tuple. Tuple из одного значения: (x,).
• SQL Injection = ввод, который меняет запрос. Никогда не склеивайте значения снаружи в SQL.
• Изменение данных (INSERT/UPDATE/DELETE) → commit(), иначе изменение выбрасывается при закрытии.
• cursor.lastrowid = новый id. fetchone() = строка или None. fetchall() = список tuple.
• Функция на каждое действие, со своим соединением и try/finally.

Официальная документация#

• MySQLCursor.execute()
• Inserting Data Using Connector/Python
• Querying Data Using Connector/Python