# Updating and Deleting: UPDATE and DELETE by id — from SQL and from Python<no value>

## In this lesson

This lesson is built from two chapters, in the same order as the slides:

1. **Chapter 1** — UPDATE by id: updating a record from SQL and from Python
2. **Chapter 2** — DELETE by id: deleting a record from SQL and from Python

## Chapter 1: UPDATE by id — Updating a Record from SQL and from Python

### What will we learn?

**`UPDATE`** = the **U** of CRUD: changing an **existing** record.

- The syntax `UPDATE ... SET ... WHERE ...`.
- Updating one column, and several columns.
- **Why `WHERE` is required.**
- Updating from Python, and **`rowcount`**.

### Why do we need this?

Every system needs to update: a student changed their email, a product's price changed, an order moved to "shipped". In the API of the students API topic, `PUT /students/7` = `UPDATE students SET ... WHERE id = 7`. And `UPDATE` is also the command with which it is easiest to **destroy** data, if you forget one detail.

### Explanation from the basics

#### The syntax

```sql
UPDATE students
SET age = 25
WHERE id = 1;
```

- **`UPDATE students`** — which table.
- **`SET age = 25`** — which column gets which new value. (Here `=` is **assignment**, and inside `WHERE` it is **comparison**. The same sign, two roles.)
- **`WHERE id = 1`** — **which row** (or rows) you update.

**Several columns** — separated by commas:

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

**A value calculated from the existing value:**

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

"The new stock = the current stock minus 1". (Like `x = x - 1` in Python.)

#### WHERE — required!

```sql
UPDATE students SET age = 25;
```

**Without `WHERE`**, the change applies to **all the rows**: **all** the students are now 25. There is no "are you sure?", and no Undo. (After a commit, the old data is lost.)

{{< box "warn" "Rule: before UPDATE or DELETE — SELECT" >}}
A professionals' habit: before manually running an `UPDATE` or a `DELETE`, run a **`SELECT` with exactly the same `WHERE`**, and check which rows will be affected. Only then replace the `SELECT` with the `UPDATE`.
{{< /box >}}

{{< box "analogy" "Analogy: an eraser on a form" >}}
`UPDATE ... WHERE id = 1` = you take **file number 1** out of the cabinet, erase the age and write a new age. `UPDATE` without `WHERE` = going over **all** the files in the cabinet and changing the age in all of them, because you forgot to say which file you meant.
{{< /box >}}

#### `rowcount` — how many rows changed?

After an `UPDATE` from Python, the Cursor has **`cursor.rowcount`** = the number of rows that were actually updated:

| rowcount | Meaning |
|---|---|
| `1` | The row was found, and its values **changed** ✓ |
| `0` | **No row changed**: there is no student with this id — **or** the new values are identical to the old ones |

**An UPDATE on an id that does not exist is not an error in SQL.** The command "succeeded" and updated 0 rows.

Notice the second row in the table: in MySQL, `rowcount` counts only rows that **really changed**. If you update Avi's age to 31, and he is already 31 — `rowcount` is 0, even though Avi exists. So, to know whether a record **exists** (for the **404** of `PUT`), the safe way is to **fetch it** (`SELECT ... WHERE id = %s`) and check whether `None` came back.

### Syntax

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

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

**Notice the order of the parameters:** they follow the order of the `%s` in the whole query: first the `SET`, and **at the end** the `id` of the `WHERE`.

### First code example

The table before:

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

```sql
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;
```

**Result:**

```text
id | name | age
 2 | Avi  |  31

id | name | age
 2 | Avi  |  32
```

### Breaking down the code line by line

| Query | What happened |
|---|---|
| `SELECT ... WHERE id = 2` | **Before**: check what there is, and which row will be affected. |
| `UPDATE students SET age = 32 WHERE id = 2` | Only the row with id 2 changed. Only `age`. The name and the email stayed. |
| `SELECT ... WHERE id = 2` | **After**: verify. |

### Another example

**Updating from Python, with rowcount, and a row that does not exist.** `update_demo.py`:

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

**Output:**

```text
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'}
```

**Breakdown:**

- **`DROP TABLE IF EXISTS`** — every run starts from an empty table (only for the demonstration!).
- **`executemany(sql, list_of_tuples)`** — runs the same query for every tuple in the list. Convenient for inserting several rows.
- **Updating 3 columns** — 4 Placeholders: three for the `SET` and one for the `WHERE`, in the same order in the tuple.
- **id 99** — `rowcount = 0`. **Without an error.**
- **id 2 with the same age** — also `rowcount = 0`, even though Avi exists! Nothing changed. That is why "not found" is checked with `SELECT`, not with `rowcount`.
- **`age = age + 1 WHERE age < 30`** — `WHERE` can choose **several** rows. Dana (25, after the update) and Noa (22) are under 30, so `rowcount = 2`. Avi did not change.
- **`connection.cursor(dictionary=True)`** — a new Cursor, to get the rows at the end as dictionaries.

### Common mistake

**Mistake 1 (the big one): UPDATE without WHERE.**

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

**All** the students get the same email. (Here, thanks to `UNIQUE`, the database actually refuses, because two students cannot get the same email. Constraints save the day! But there was no constraint on `age` to stop it.) **Check every UPDATE: where is the WHERE?**

**Mistake 2: a wrong order of parameters.**

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

Now you update **the student whose id equals the age**, to **an age equal to the id**. There is no error, and it is a Logical Error that is very hard to find. **The order in the tuple = the order of the `%s` in the query, from left to right.**

**Mistake 3: thinking that an UPDATE on an id that does not exist throws an error.** It does not. Code that does not check will return "updated successfully" for a student that does not exist. And the opposite: `rowcount == 0` does not necessarily mean "does not exist" — maybe simply no value changed. **Check existence with `SELECT`.**

**Mistake 4: forgetting commit** (as in INSERT). The update is visible inside the same connection, and disappears after closing.

### Summary

- **`UPDATE table SET col = %s, col2 = %s WHERE id = %s`** — updating an existing record. In `SET`, `=` is assignment.
- **Without `WHERE`, all the rows are updated.** A habit: a `SELECT` with the same `WHERE` before running it manually.
- A new value can be calculated: `stock = stock - 1`.
- From Python: parameters in the order of the `%s` (SET and then WHERE), and `commit`.
- **`cursor.rowcount`** = how many rows **changed**. 0 = it does not exist, **or** no value changed. The 404 of PUT is decided with a `SELECT` that returns `None`.

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

### Official documentation

- [MySQL — UPDATE](https://dev.mysql.com/doc/refman/8.4/en/update.html)
- [MySQLConnection.commit()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-commit.html)

<!-- docs:end -->

## Chapter 2: DELETE by id — Deleting a Record from SQL and from Python

### What will we learn?

**`DELETE`** = the **D** of CRUD: deleting records.

- The syntax `DELETE FROM ... WHERE ...`.
- **DELETE without WHERE** — what it does, and how to avoid it.
- Deleting from Python, and **`rowcount`**.
- What happens to the **id** after deleting.

### Why do we need this?

A user deletes an account, a manager deletes a product that stopped being sold, a student leaves the course. In the API: `DELETE /students/7` = `DELETE FROM students WHERE id = 7`. And deleting is **the only action in CRUD that has no way back**: after a commit, the data no longer exists.

### Explanation from the basics

#### The syntax

```sql
DELETE FROM students WHERE id = 3;
```

- **`DELETE FROM students`** — from which table.
- **`WHERE id = 3`** — **which rows** to delete.
- There is no list of columns: you **delete whole rows**, not columns. (To "empty" a field, use `UPDATE ... SET field = NULL`.)

#### DELETE without WHERE

```sql
DELETE FROM students;
```

**Deletes all the rows in the table.** The table itself stays (empty), but all the data disappears. This is probably the command that has "destroyed" the most data in the history of software. The same rule as in UPDATE: **a `SELECT` with the same `WHERE` first.**

#### rowcount — again

| rowcount after DELETE | Meaning |
|---|---|
| `1` | A record was deleted ✓ |
| `0` | There is no record with this id. **Not an error in SQL.** In the API → 404 |

#### What happens to the id?

With `AUTO_INCREMENT`, **MySQL does not reuse an id**. If you deleted student 3, the next student that is added gets 4 (and not 3). Why is this good? Because if id 3 passed to someone else, everything that pointed to "student 3" (a link, an order, a log) would suddenly point to **a different person**.

{{< box "analogy" "Analogy: ID card numbers" >}}
When a person dies, their ID card number does not pass to a baby who is born. A number that was taken once stays with the same record forever. "Holes" in the numbering (1, 2, 4, 5) are **valid** and even desirable.
{{< /box >}}

#### Soft Delete — at an introductory level

In many systems you **don't really delete**. You add a column to the table like `is_active` — 1 = active, 0 = "deleted" — and "deleting" is actually an `UPDATE`. That way you can restore, and keep history (for example, the old orders of a customer who left stay valid).

| | A regular delete (Hard Delete) | A soft delete (Soft Delete) |
|---|---|---|
| The command | `DELETE` | `UPDATE ... SET is_active = 0` |
| The row | Disappears from the table | Stays, only marked |
| Restoring | Impossible | `UPDATE ... SET is_active = 1` |
| Reading | A regular `SELECT` | Every `SELECT` of a list adds `WHERE is_active = 1` |

**In SQL:**

```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;
```

**From Python** — exactly like `delete_student`, only with `UPDATE` instead of `DELETE`:

```python
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` means every new student is created active, without having to write it in the `INSERT`. The price of a soft delete: **every** query that shows a list must remember `WHERE is_active = 1`, otherwise "deleted" students will appear again. Here we will use a real DELETE, but it is good to know the option exists.

### Syntax

```sql
DELETE FROM table_name WHERE id = 7;
```

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

### First code example

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

**Result:**

```text
id | name
 3 | Noa

id | name
 1 | Dana
 2 | Avi
```

### Breaking down the code line by line

| Query | What happened |
|---|---|
| `SELECT ... WHERE id = 3` | **A check in advance**: what is about to be deleted? Noa. |
| `DELETE FROM students WHERE id = 3` | Noa's row was deleted, the whole of it. |
| `SELECT id, name FROM students` | Dana and Avi are left. |

### Another example

**Deleting from Python: rowcount, a double delete, and an id that is not reused.** `delete_demo.py`:

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

**Output:**

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

**Breakdown:**

- `DROP TABLE IF EXISTS` — every run starts from an empty table (only for the demonstration!).
- `[("Dana",), ("Avi",), ("Noa",)]` — every row has a tuple of **one value**, so there is a comma after every name.
- **The first delete** → `rowcount = 1`.
- **A second delete of the same id** → `rowcount = 0`, **without an error**. In the API: the first request returns **204**, and the second **404**.
- **Tom got 4, and not 3.** `AUTO_INCREMENT` does not reuse. There is a "hole" in the numbering, and that is fine.

### Common mistake

**Mistake 1: DELETE without WHERE.**

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

All the students were deleted. **In every piece of code that contains a DELETE: where is the WHERE, and what is its parameter?**

**Mistake 2: a WHERE that catches more than you thought.**

```sql
DELETE FROM students WHERE name = 'Dana';
```

If there are **two** Danas, both are deleted. **Delete by the Primary Key** (`id`), which is guaranteed to be unique. A name is not an identifier.

**Mistake 3: forgetting commit.** The delete "works" inside the connection, and disappears after closing. In the case of deleting, it is actually a "saving bug", but still a bug.

**Mistake 4: reading a record after you deleted it.** `get_student(id)` after deleting returns `None`. If the code assumes it exists (`student["name"]`), it will crash. That is why `delete_student` will return **`True`/`False`** and not the student that was deleted.

### Summary

- **`DELETE FROM table WHERE id = %s`** — deletes whole rows. After a commit, there is no way back.
- **Without WHERE all the rows are deleted.** Delete by the **Primary Key**, and check with a SELECT beforehand.
- `cursor.rowcount`: **1** = deleted, **0** = it did not exist, without an error. In the API: 204 / 404.
- **`AUTO_INCREMENT` does not reuse an id.** "Holes" in the numbering are fine.
- Soft Delete (`is_deleted`) is a common alternative in real systems.

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

### Official documentation

- [MySQL — DELETE](https://dev.mysql.com/doc/refman/8.4/en/delete.html)
- [MySQLCursor.rowcount](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-rowcount.html)

<!-- docs:end -->

<!-- exercises:start — generated by scripts/intro6/sync_site_exercises.py; edit scripts/intro6/exercises/ -->
## Practice

The exercises use only what was taught in this lesson and the lessons before it. Try on your own first, and only then open the solution.

### Exercise 1: UPDATE in SQL

Write an SQL command that changes the age of the student whose `id` is 2 to 32. What will happen if you forget the `WHERE`?

{{< solution >}}
```sql
UPDATE students SET age = 32 WHERE id = 2;
```

Without `WHERE`, **all** the students in the table will get age 32. `WHERE` is the critical part of the command — it decides **which** rows are updated.
{{< /solution >}}

### Exercise 2: UPDATE from Python and rowcount

The table has one student — Dana (id 1, age 24). Create it like this:

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

Write `update_age(student_id, new_age)` that updates with parameters, and returns how many rows were updated (`rowcount`).

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

**Expected output:**

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

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

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

`rowcount` is 0 when there is no such row — **this is not an error**. The calling code decides what to do with it (for example, 404). Notice: in MySQL, `rowcount` counts only rows that **really changed** — an update to exactly the same value also returns 0.
{{< /solution >}}

### Exercise 3: update_student

Start again from a table with only Dana (id 1, age 24), as in exercise 2. Write `update_student(student_id, name, age)` that updates both fields and returns the updated student as a dictionary, or `None` if they do not exist.

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

**Expected output:**

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

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

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

After the update, fetch the row again: if there is no such student — `fetchone()` returns `None`, by the same convention as `get_student`. (We don't rely on `rowcount` here, because an update to exactly the same values returns 0 — even though the student exists.)
{{< /solution >}}

### Exercise 4: delete_student

The table has three students — Dana (1), Avi (2) and Noa (3). Create it like this:

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

Write `delete_student(student_id)` that returns `True` if a row was deleted and `False` if not. Delete the same student (Noa, id 3) twice.

```python
print(delete_student(3))
print(delete_student(3))
```

**Expected output:**

```text
True
False
```

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

```text
True
False
```

The second delete does not fail — it simply does not find a row (`rowcount` = 0).
{{< /solution >}}

### Exercise 5: What happens to a deleted id?

With three students (1, 2, 3): delete 3, add Tom, and print his id and all the rows.

**Expected output:**

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

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

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

With `AUTO_INCREMENT`, **an id is not reused**. This is important: if something else still points to id 3 (for example an old order), it will not point to Tom by mistake.
{{< /solution >}}

<!-- exercises:end -->