# Запрос из Python: execute, параметры, commit и fetch<no value>

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

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

```text
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)`

```python
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 конкатенацией

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

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

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

```text
x' OR '1'='1
```

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

```sql
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: «это обычная кавычка внутри текста, а не конец текста».

| Шаг | Что видно |
|---|---|
| Пользователь вводит | `x' OR '1'='1` |
| Драйвер добавляет `\` перед каждой кавычкой | `x\' OR \'1\'=\'1` |
| Что получает MySQL | `SELECT name FROM students WHERE name = 'x\' OR \'1\'=\'1'` |
| Как MySQL это читает | **Один** текст: длинное странное имя — `x' OR '1'='1` |

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

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

{{< box "warn" "Железное правило" >}}
**Никогда** не стройте SQL через `+` из значений, которые приходят снаружи. **Всегда `%s` и параметры.** Исключений нет.
{{< /box >}}

{{< box "analogy" "Аналогия: бланк с полями" >}}
Конкатенация строк = позволить постороннему свободно писать на вашем чеке, в том числе в строке суммы. Параметры = печатный чек с отдельным полем для имени, и то, что написано в поле, — **только имя**: из него нельзя «вылезти» и изменить сумму.
{{< /box >}}

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

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

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

### `lastrowid` и `rowcount`

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

| Атрибут | Что в нём | Применение |
|---|---|---|
| `cursor.lastrowid` | id, который база дала **последней вставленной строке** | Вернуть новый элемент из POST |
| `cursor.rowcount` | **Сколько строк изменилось** в INSERT/UPDATE/DELETE | Обнаружить «не найдено» в UPDATE/DELETE (позже в этой теме) |

## Синтаксис

```python
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`:

```python
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()
```

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

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

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

| Код | Шаг пути |
|---|---|
| `get_connection()` | **Connection**: открытие. |
| `cursor = connection.cursor()` | Курсор, через который мы выполним все запросы. |
| `"""..."""` | **Многострочная строка** (тройные кавычки). Удобна для длинного SQL. |
| `execute(CREATE TABLE ...)` | Создание таблицы, если её нет. |
| `execute("INSERT ... VALUES (%s, %s, %s)", ("Dana", 24, "dana@mail.com"))` | **Запрос** с 3 заполнителями и 3 значениями. **Python → Database.** |
| `connection.commit()` | **Сохранение.** Без него студент исчез бы при закрытии. |
| `cursor.lastrowid` | id, который дала база: 1. |
| `execute("SELECT ...")` + `fetchall()` | **Result → Python**: список tuple. |
| `finally: connection.close()` | Закрытие, всегда. |

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

```text
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.**

```python
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()
```

**Вывод:**

```text
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`.**

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

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

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

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

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

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

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

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

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

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

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

```text
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'` (строку), а это синтаксическая ошибка. Имена таблиц и столбцов пишут в коде фиксированными.

<!-- exercises:start — generated by scripts/intro6/sync_site_exercises.py; edit scripts/intro6/exercises/ -->
## Практика

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

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

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

```python
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())
```

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

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

{{< solution >}}
```python
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()
```

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

- `%s` — **заполнители**, а значения передаются отдельно в виде tuple. (В MySQL `%s` пишут для любого типа — и для чисел.)
- `cursor.lastrowid` — `id`, который база дала новой строке.
{{< /solution >}}

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

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

```py
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()
```

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

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

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

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

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

{{< solution >}}
1. Команда — `SELECT name FROM students WHERE name = 'x' OR '1'='1'`, и условие `'1'='1'` всегда истинно, поэтому она возвращает **всех студентов**. Ввод пользователя стал частью **команды**.
2.

```python
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()
```

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

С `%s` значение передаётся **отдельно** — драйвер воспринимает его как странное имя и никогда — как команду. **Никогда не стройте SQL конкатенацией ввода.**
{{< /solution >}}

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

```text
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` и посчитайте ещё раз.

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

```text
Without commit: 0 rows
With commit: 1 rows
```

{{< solution >}}
Без `commit()` изменение остаётся **временным**, и `close()` молча его отменяет.

```python
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")
```

```text
Without commit: 0 rows
With commit: 1 rows
```

`CREATE TABLE` сохраняется сразу, а каждый `INSERT`, `UPDATE` и `DELETE` ждёт `commit`.
{{< /solution >}}

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

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

```py
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`.

```python
print(find_by_id(1))
print(find_by_id(99))
```

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

```text
(1, 'Dana', 24)
None
```

{{< solution >}}
```python
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))
```

```text
(1, 'Dana', 24)
None
```

Обратите внимание на `(student_id,)` — tuple из **одного** элемента нуждается в запятой. Без запятой это просто скобки вокруг числа.
{{< /solution >}}

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

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

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

{{< solution >}}
Порядок: **(б), (г), затем (д) или (а), а в конце (в)**

1. **connect + cursor** — открыть соединение и создать курсор.
2. **execute** — выполнить запрос с параметрами.
3. Если это чтение (`SELECT`) — **fetch** результатов. Если это изменение (`INSERT`, `UPDATE`, `DELETE`) — **commit**.
4. **close** — закрыть, внутри `finally`.
{{< /solution >}}

<!-- exercises:end -->

## Итоги

- Путь: **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`.

<!-- docs:start — generated by scripts/intro6/site_docs.py; edit the links there -->

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

- [MySQLCursor.execute()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html)
- [Inserting Data Using Connector/Python](https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html)
- [Querying Data Using Connector/Python](https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html)

<!-- docs:end -->