Обновление и удаление: UPDATE и DELETE по id — из SQL и из Python

В этом уроке#

Урок состоит из двух глав — в том же порядке, что и в презентации:

1. Глава 1 — UPDATE по id: обновление записи из SQL и из Python
2. Глава 2 — DELETE по id: удаление записи из SQL и из Python

Глава 1: UPDATE по id — обновление записи из SQL и из Python#

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

UPDATE = U из CRUD: изменение существующей записи.

• Синтаксис UPDATE ... SET ... WHERE ....
• Обновление одного столбца и нескольких столбцов.
• Почему WHERE обязателен.
• Обновление из Python и rowcount.

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

Каждой системе нужно обновлять: студент сменил email, у товара изменилась цена, заказ перешёл в «отправлен». В API темы API студентов PUT /students/7 = UPDATE students SET ... WHERE id = 7. А ещё UPDATE — команда, которой легче всего уничтожить данные, если забыть одну деталь.

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

Синтаксис#

UPDATE students
SET age = 25
WHERE id = 1;

• UPDATE students — какая таблица.
• SET age = 25 — какой столбец получает какое новое значение. (Здесь = — присваивание, а внутри WHERE — сравнение. Один знак, две роли.)
• WHERE id = 1 — какую строку (или строки) вы обновляете.

Несколько столбцов — через запятую:

UPDATE students
SET name = 'Dana Cohen', age = 25, email = 'dana.cohen@mail.com'
WHERE id = 1;

Значение, вычисленное из существующего значения:

UPDATE products SET stock = stock - 1 WHERE id = 3;

«Новый запас = текущий запас минус 1». (Как x = x - 1 в Python.)

WHERE — обязателен!#

UPDATE students SET age = 25;

Без WHERE изменение применяется ко всем строкам: теперь всем студентам 25. Нет никакого «вы уверены?» и никакого Undo. (После commit старые данные потеряны.)

rowcount — сколько строк изменилось?#

После UPDATE из Python у курсора есть cursor.rowcount = число строк, которые действительно обновились:

UPDATE для несуществующего id — не ошибка в SQL. Команда «выполнилась» и обновила 0 строк.

Обратите внимание на вторую строку таблицы: в MySQL rowcount считает только строки, которые действительно изменились. Если обновить возраст Ави на 31, а ему уже 31, rowcount будет 0, хотя Ави существует. Поэтому, чтобы узнать, существует ли запись (для 404 у PUT), безопасный способ — получить её (SELECT ... WHERE id = %s) и проверить, не вернулся ли None.

Синтаксис#

UPDATE table_name
SET column_a = value_a, column_b = value_b
WHERE id = 7;

cursor = connection.cursor()
cursor.execute(
    "UPDATE students SET name = %s, age = %s WHERE id = %s",
    (new_name, new_age, student_id),
)
connection.commit()
cursor.rowcount        # 1 = changed, 0 = nothing changed

Обратите внимание на порядок параметров: он следует порядку %s во всём запросе: сначала SET, а в конце — id из WHERE.

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

Таблица до:

id | name | age | email
 1 | Dana |  24 | dana@mail.com
 2 | Avi  |  31 | avi@mail.com
 3 | Noa  |  22 | noa@mail.com

SELECT id, name, age FROM students WHERE id = 2;
UPDATE students SET age = 32 WHERE id = 2;
SELECT id, name, age FROM students WHERE id = 2;

Результат:

id | name | age
 2 | Avi  |  31

id | name | age
 2 | Avi  |  32

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

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

Обновление из Python, с rowcount и несуществующей строкой. update_demo.py:

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, email VARCHAR(100) NOT NULL UNIQUE)"
    )
    cursor.executemany(
        "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)",
        [("Dana", 24, "dana@mail.com"), ("Avi", 31, "avi@mail.com"), ("Noa", 22, "noa@mail.com")],
    )
    connection.commit()

    cursor.execute(
        "UPDATE students SET name = %s, age = %s, email = %s WHERE id = %s",
        ("Dana Cohen", 25, "dana.cohen@mail.com", 1),
    )
    connection.commit()
    print("Updated rows for id 1:", cursor.rowcount)

    cursor.execute("UPDATE students SET age = %s WHERE id = %s", (40, 99))
    connection.commit()
    print("Updated rows for id 99:", cursor.rowcount)

    cursor.execute("UPDATE students SET age = %s WHERE id = %s", (31, 2))
    connection.commit()
    print("Updated rows for id 2, same age:", cursor.rowcount)

    cursor.execute("UPDATE students SET age = age + 1 WHERE age < %s", (30,))
    connection.commit()
    print("Birthday for everyone under 30:", cursor.rowcount, "rows")

    cursor = connection.cursor(dictionary=True)
    cursor.execute("SELECT id, name, age, email FROM students ORDER BY id")
    for row in cursor.fetchall():
        print(row)
finally:
    connection.close()

Вывод:

Updated rows for id 1: 1
Updated rows for id 99: 0
Updated rows for id 2, same age: 0
Birthday for everyone under 30: 2 rows
{'id': 1, 'name': 'Dana Cohen', 'age': 26, 'email': 'dana.cohen@mail.com'}
{'id': 2, 'name': 'Avi', 'age': 31, 'email': 'avi@mail.com'}
{'id': 3, 'name': 'Noa', 'age': 23, 'email': 'noa@mail.com'}

Разбор:

• DROP TABLE IF EXISTS — каждый запуск начинается с пустой таблицы (только для демонстрации!).
• executemany(sql, list_of_tuples) — выполняет один и тот же запрос для каждого tuple в списке. Удобно для вставки нескольких строк.
• Обновление 3 столбцов — 4 заполнителя: три для SET и один для WHERE, в том же порядке в tuple.
• id 99 — rowcount = 0. Без ошибки.
• id 2 с тем же возрастом — тоже rowcount = 0, хотя Ави существует! Ничего не изменилось. Поэтому «не найдено» проверяют через SELECT, а не через rowcount.
• age = age + 1 WHERE age < 30 — WHERE может выбрать несколько строк. Дане (25, после обновления) и Ноа (22) меньше 30, поэтому rowcount = 2. Ави не изменился.
• connection.cursor(dictionary=True) — новый курсор, чтобы в конце получить строки в виде словарей.

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

Ошибка 1 (главная): UPDATE без WHERE.

cursor.execute("UPDATE students SET email = %s", (new_email,))

Все студенты получают один и тот же email. (Здесь благодаря UNIQUE база как раз откажет, потому что два студента не могут получить одинаковый email. Ограничения спасают положение! Но на age не было ограничения, которое бы это остановило.) Проверяйте каждый UPDATE: где WHERE?

Ошибка 2: неправильный порядок параметров.

cursor.execute("UPDATE students SET age = %s WHERE id = %s", (student_id, new_age))

Теперь вы обновляете студента, у которого id равен возрасту, на возраст, равный id. Ошибки нет, и это Logical Error, которую очень трудно найти. Порядок в tuple = порядок %s в запросе, слева направо.

Ошибка 3: думать, что UPDATE для несуществующего id выбрасывает ошибку. Нет. Код, который не проверяет, вернёт «успешно обновлено» для несуществующего студента. И наоборот: rowcount == 0 не обязательно значит «не существует» — возможно, просто ни одно значение не изменилось. Проверяйте существование через SELECT.

Ошибка 4: забыть commit (как в INSERT). Обновление видно внутри того же соединения и исчезает после закрытия.

Итоги#

• UPDATE table SET col = %s, col2 = %s WHERE id = %s — обновление существующей записи. В SET знак = — присваивание.
• Без WHERE обновляются все строки. Привычка: SELECT с тем же WHERE перед ручным запуском.
• Новое значение можно вычислять: stock = stock - 1.
• Из Python: параметры в порядке %s (сначала SET, потом WHERE) и commit.
• cursor.rowcount = сколько строк изменилось. 0 = не существует или ни одно значение не изменилось. 404 у PUT решают через SELECT, который возвращает None.

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

• MySQL — UPDATE
• MySQLConnection.commit()

Глава 2: DELETE по id — удаление записи из SQL и из Python#

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

DELETE = D из CRUD: удаление записей.

• Синтаксис DELETE FROM ... WHERE ....
• DELETE без WHERE — что он делает и как этого избежать.
• Удаление из Python и rowcount.
• Что происходит с id после удаления.

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

Пользователь удаляет аккаунт, менеджер удаляет товар, который перестали продавать, студент уходит с курса. В API: DELETE /students/7 = DELETE FROM students WHERE id = 7. А удаление — единственное действие CRUD, у которого нет пути назад: после commit данных больше нет.

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

Синтаксис#

DELETE FROM students WHERE id = 3;

• DELETE FROM students — из какой таблицы.
• WHERE id = 3 — какие строки удалять.
• Списка столбцов нет: удаляют целые строки, а не столбцы. (Чтобы «очистить» поле, используют UPDATE ... SET field = NULL.)

DELETE без WHERE#

DELETE FROM students;

Удаляет все строки таблицы. Сама таблица остаётся (пустой), но все данные исчезают. Это, пожалуй, команда, которая «уничтожила» больше всего данных в истории ПО. То же правило, что и в UPDATE: сначала SELECT с тем же WHERE.

rowcount — снова#

Что происходит с id?#

С AUTO_INCREMENT MySQL не переиспользует id. Если вы удалили студента 3, следующий добавленный студент получит 4 (а не 3). Почему это хорошо? Потому что если бы id 3 перешёл к кому-то другому, всё, что указывало на «студента 3» (ссылка, заказ, лог), вдруг указывало бы на другого человека.

Мягкое удаление (Soft Delete) — на вводном уровне#

Во многих системах по-настоящему не удаляют. В таблицу добавляют столбец вроде is_active — 1 = активен, 0 = «удалён», — и «удаление» на самом деле — это UPDATE. Так можно восстановить запись и сохранить историю (например, старые заказы ушедшего клиента остаются действительными).

В SQL:

-- the table has: is_active INT NOT NULL DEFAULT 1
-- "delete" = mark the row, do not remove it
UPDATE students SET is_active = 0 WHERE id = 3;

-- every read shows only active students
SELECT id, name FROM students WHERE is_active = 1;
-- 1 | Dana
-- 2 | Avi

-- restore = one more UPDATE
UPDATE students SET is_active = 1 WHERE id = 3;

Из Python — точно как delete_student, только с UPDATE вместо DELETE:

def soft_delete_student(student_id):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute(
            "UPDATE students SET is_active = 0 "
            "WHERE id = %s", (student_id,))
        connection.commit()
        return cursor.rowcount > 0
    finally:
        connection.close()

DEFAULT 1 означает, что каждый новый студент создаётся активным, и это не нужно писать в INSERT. Цена мягкого удаления: каждый запрос, который показывает список, должен помнить о WHERE is_active = 1, иначе «удалённые» студенты появятся снова. Здесь мы будем использовать настоящий DELETE, но хорошо знать, что такой вариант существует.

Синтаксис#

DELETE FROM table_name WHERE id = 7;

cursor = connection.cursor()
cursor.execute("DELETE FROM students WHERE id = %s", (student_id,))
connection.commit()
cursor.rowcount        # 1 = deleted, 0 = no such row

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

SELECT id, name FROM students WHERE id = 3;
DELETE FROM students WHERE id = 3;
SELECT id, name FROM students;

Результат:

id | name
 3 | Noa

id | name
 1 | Dana
 2 | Avi

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

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

Удаление из Python: rowcount, двойное удаление и id, который не переиспользуется. delete_demo.py:

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)"
    )
    cursor.executemany("INSERT INTO students (name) VALUES (%s)", [("Dana",), ("Avi",), ("Noa",)])
    connection.commit()

    cursor.execute("DELETE FROM students WHERE id = %s", (3,))
    connection.commit()
    print("Deleted id 3:", cursor.rowcount, "row(s)")

    cursor.execute("DELETE FROM students WHERE id = %s", (3,))
    connection.commit()
    print("Deleted id 3 again:", cursor.rowcount, "row(s)")

    cursor.execute("INSERT INTO students (name) VALUES (%s)", ("Tom",))
    connection.commit()
    print("Tom got id:", cursor.lastrowid)

    cursor.execute("SELECT id, name FROM students ORDER BY id")
    print("Rows now:", cursor.fetchall())
finally:
    connection.close()

Вывод:

Deleted id 3: 1 row(s)
Deleted id 3 again: 0 row(s)
Tom got id: 4
Rows now: [(1, 'Dana'), (2, 'Avi'), (4, 'Tom')]

Разбор:

• DROP TABLE IF EXISTS — каждый запуск начинается с пустой таблицы (только для демонстрации!).
• [("Dana",), ("Avi",), ("Noa",)] — у каждой строки tuple из одного значения, поэтому после каждого имени стоит запятая.
• Первое удаление → rowcount = 1.
• Повторное удаление того же id → rowcount = 0, без ошибки. В API: первый запрос возвращает 204, а второй — 404.
• Том получил 4, а не 3. AUTO_INCREMENT не переиспользует. В нумерации «дыра», и это нормально.

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

Ошибка 1: DELETE без WHERE.

cursor.execute("DELETE FROM students")
connection.commit()

Все студенты удалены. В каждом куске кода, где есть DELETE: где WHERE и каков его параметр?

Ошибка 2: WHERE, который захватывает больше, чем вы думали.

DELETE FROM students WHERE name = 'Dana';

Если Дан две, удалятся обе. Удаляйте по первичному ключу (id), который гарантированно уникален. Имя — не идентификатор.

Ошибка 3: забыть commit. Удаление «работает» внутри соединения и исчезает после закрытия. В случае удаления это скорее «спасительный баг», но всё равно баг.

Ошибка 4: читать запись после того, как вы её удалили. get_student(id) после удаления возвращает None. Если код предполагает, что запись есть (student["name"]), он упадёт. Поэтому delete_student будет возвращать True/False, а не удалённого студента.

Итоги#

• DELETE FROM table WHERE id = %s — удаляет целые строки. После commit пути назад нет.
• Без WHERE удаляются все строки. Удаляйте по первичному ключу и проверяйте заранее через SELECT.
• cursor.rowcount: 1 = удалено, 0 = не существовало, без ошибки. В API: 204 / 404.
• AUTO_INCREMENT не переиспользует id. «Дыры» в нумерации — это нормально.
• Мягкое удаление (Soft Delete) — распространённая альтернатива в настоящих системах.

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

• MySQL — DELETE
• MySQLCursor.rowcount

Практика#

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

Упражнение 1: UPDATE в SQL#

Напишите команду SQL, которая меняет возраст студента с id 2 на 32. Что произойдёт, если забыть WHERE?

UPDATE students SET age = 32 WHERE id = 2;

Упражнение 2: UPDATE из Python и rowcount#

В таблице один студент — Дана (id 1, 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()

Напишите update_age(student_id, new_age), которая обновляет с параметрами и возвращает, сколько строк обновилось (rowcount).

print("Updated rows for id 1:", update_age(1, 25))
print("Updated rows for id 99:", update_age(99, 40))

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

Updated rows for id 1: 1
Updated rows for id 99: 0

import mysql.connector


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


def update_age(student_id, new_age):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute(
            "UPDATE students SET age = %s WHERE id = %s", (new_age, student_id))
        connection.commit()
        return cursor.rowcount
    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("Updated rows for id 1:", update_age(1, 25))
print("Updated rows for id 99:", update_age(99, 40))

Updated rows for id 1: 1
Updated rows for id 99: 0

Упражнение 3: update_student#

Начните снова с таблицы, в которой только Дана (id 1, 24 года), как в упражнении 2. Напишите update_student(student_id, name, age), которая обновляет оба поля и возвращает обновлённого студента в виде словаря или None, если его нет.

print(update_student(1, "Dana Cohen", 26))
print(update_student(99, "Nobody", 1))

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

{'id': 1, 'name': 'Dana Cohen', 'age': 26}
None

import mysql.connector


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


def update_student(student_id, name, age):
    connection = get_connection()
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute(
            "UPDATE students SET name = %s, age = %s WHERE id = %s",
            (name, age, student_id))
        connection.commit()
        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(update_student(1, "Dana Cohen", 26))
print(update_student(99, "Nobody", 1))

{'id': 1, 'name': 'Dana Cohen', 'age': 26}
None

Упражнение 4: delete_student#

В таблице три студента — Дана (1), Ави (2) и Ноа (3). Создайте её так:

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'), ('Noa')")
connection.commit()
connection.close()

Напишите delete_student(student_id), которая возвращает True, если строка удалена, и False, если нет. Удалите одного и того же студента (Ноа, id 3) дважды.

print(delete_student(3))
print(delete_student(3))

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

True
False

import mysql.connector


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


def delete_student(student_id):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute("DELETE FROM students WHERE id = %s", (student_id,))
        connection.commit()
        return cursor.rowcount > 0
    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))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi'), ('Noa')")
setup.commit()
setup.close()

print(delete_student(3))
print(delete_student(3))

True
False

Упражнение 5: Что происходит с удалённым id?#

С тремя студентами (1, 2, 3): удалите 3, добавьте Тома и напечатайте его id и все строки.

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

Tom got id: 4
[(1, 'Dana'), (2, 'Avi'), (4, 'Tom')]

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'), ('Noa')")
connection.commit()

cursor.execute("DELETE FROM students WHERE id = %s", (3,))
cursor.execute("INSERT INTO students (name) VALUES (%s)", ("Tom",))
connection.commit()
print("Tom got id:", cursor.lastrowid)
cursor.execute("SELECT id, name FROM students ORDER BY id")
print(cursor.fetchall())
connection.close()

Tom got id: 4
[(1, 'Dana'), (2, 'Avi'), (4, 'Tom')]