Updating and Deleting: UPDATE and DELETE by id — from SQL and from Python

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#

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:

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

A value calculated from the existing value:

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!#

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.)

rowcount — how many rows changed?#

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

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#

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

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:

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;

Result:

id | name | age
 2 | Avi  |  31

id | name | age
 2 | Avi  |  32

Breaking down the code line by line#

Another example#

Updating from Python, with rowcount, and a row that does not exist. 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()

Output:

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.

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.

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.

Official documentation#

• MySQL — UPDATE
• MySQLConnection.commit()

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#

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#

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#

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.

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).

In 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:

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#

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

First code example#

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

Result:

id | name
 3 | Noa

id | name
 1 | Dana
 2 | Avi

Breaking down the code line by line#

Another example#

Deleting from Python: rowcount, a double delete, and an id that is not reused. 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()

Output:

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.

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.

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.

Official documentation#

• MySQL — DELETE
• MySQLCursor.rowcount

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?

UPDATE students SET age = 32 WHERE id = 2;

Exercise 2: UPDATE from Python and rowcount#

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

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).

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

Expected output:

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

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.

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

Expected output:

{'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

Exercise 4: delete_student#

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

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.

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

Expected output:

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

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:

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