CRUD Examples — Python and SQLite

Common create, read, update and delete tasks for your NEA, written as methods on the Database class from the App Skeleton. Each example is labelled with the kind of system it comes from — [Rugby], [Theatre school], [Record shop], [Progress tracker], [Library] — so change the table and column names to match your own design.

Each example has pseudo-code first, showing the steps without any Python or SQLite detail, and then the Python that does it. The pseudo-code is a good model for the algorithm designs in your NEA write-up.

1. How every method works

Each method follows the same steps. Here they are using update_user_email from the skeleton:

Pseudo-code:

FUNCTION updateUserEmail(userID, newEmail)
    OPEN connection TO database
    EXECUTE "UPDATE users SET email = newEmail WHERE id = userID"
    COMMIT changes
    SET success TO (rows changed > 0)
    CLOSE connection
    RETURN success
END FUNCTION

Python:

def update_user_email(self, user_id, new_email):
    connection = self._connect()                 # 1. open the database
    cursor = connection.cursor()                 # 2. get a cursor to run SQL

    cursor.execute(                              # 3. run the SQL, with the values
        "UPDATE users SET email = :new_email "   #    passed separately in a dict
        "WHERE id = :user_id",
        {"new_email": new_email, "user_id": user_id},
    )

    connection.commit()                          # 4. save the change
    success = cursor.rowcount > 0                # 5. check what happened
    connection.close()                           # 6. close the database
    return success                               # 7. tell the form

A SELECT doesn’t change anything, so it skips the commit() and uses fetchone() or fetchall() at step 5 instead.

Putting values into the SQL

Rule	Example
Every value goes in as a named placeholder, never joined into the string.	"WHERE name = :name" with {"name": name}
Placeholders have no quotes around them, even for text.	:name, not ':name'
Numbers don’t need str(). SQLite gets the real number.	{"user_id": 5}
Dates are stored as text in YYYY-MM-DD format.	'2026-09-22'
date('now') gives today’s date inside the SQL.	"SET DateReturned = date('now')"
A fixed value that never comes from the user can be typed straight into the SQL.	"VALUES (..., 'Booked')"
Each piece of the string needs a space at the end, or words run together.	"FROM users " "WHERE id = :id"

What to return to the form

After	Use	Gives you
INSERT	cursor.lastrowid	the ID of the new row
UPDATE or DELETE	cursor.rowcount	how many rows changed (0 means nothing matched)
SELECT, one row	cursor.fetchone()	one row, or None if nothing matched
SELECT, many rows	cursor.fetchall()	a list of rows (empty if nothing matched)

Because the skeleton’s _connect() sets row_factory = sqlite3.Row, every row can be read by column name (row["Title"]) or by position (row[0]).

2. Create

[Rugby] Add a row and return its new ID

Pseudo-code:

FUNCTION addTeam(name, leagueID)
    OPEN connection TO database
    EXECUTE "INSERT INTO team (name, league_id) VALUES (name, leagueID)"
    COMMIT changes
    SET newID TO ID of the new row
    CLOSE connection
    RETURN newID
END FUNCTION

Python:

def add_team(self, name, league_id):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "INSERT INTO team (name, league_id) "
        "VALUES (:name, :league_id)",
        {"name": name, "league_id": league_id},
    )

    connection.commit()
    new_id = cursor.lastrowid
    connection.close()
    return new_id

Every column in the INSERT gets its own placeholder, and the dictionary has a key with the same name.

[Theatre school] Stop duplicates with try and except

The Booking table has a UNIQUE rule on (StudentID, ClassID), so booking the same student onto the same class twice raises an IntegrityError. This uses the same try/except/finally shape as create_user:

Pseudo-code:

FUNCTION bookClass(studentID, classID)
    OPEN connection TO database
    TRY
        EXECUTE "INSERT INTO Booking (StudentID, ClassID, DateBooked, BookingStatus)
                 VALUES (studentID, classID, today, 'Booked')"
        COMMIT changes
        RETURN ID of the new row
    CATCH duplicate error                    // already booked on this class
        RETURN NULL
    FINALLY
        CLOSE connection                     // runs whichever way it ends
    END TRY
END FUNCTION

Python:

def book_class(self, student_id, class_id):
    connection = self._connect()
    cursor = connection.cursor()

    try:
        cursor.execute(
            "INSERT INTO Booking (StudentID, ClassID, DateBooked, BookingStatus) "
            "VALUES (:student_id, :class_id, date('now'), 'Booked')",
            {"student_id": student_id, "class_id": class_id},
        )
        connection.commit()
        return cursor.lastrowid

    except sqlite3.IntegrityError:
        # Student already booked on this class
        return None

    finally:
        connection.close()

The form can check for None and tell the user the student is already booked. The finally block runs whichever way the method ends, so the connection is always closed.

[Rugby] Two inserts saved together: recording a result

A result is two rows in the matchteam link table, one home and one away. Both are run before a single commit(). If the second insert fails, the commit is never reached and rollback() throws the first row away too, so the database never holds half a result.

Pseudo-code:

FUNCTION recordResult(matchID, homeID, homePoints, homeTries,
                      awayID, awayPoints, awayTries)
    OPEN connection TO database
    TRY
        EXECUTE "INSERT INTO matchteam VALUES (matchID, homeID, 'H', homePoints, homeTries)"
        EXECUTE "INSERT INTO matchteam VALUES (matchID, awayID, 'A', awayPoints, awayTries)"
        COMMIT changes                       // both rows saved together
        RETURN TRUE
    CATCH duplicate error                    // result already entered
        ROLLBACK changes                     // undo the home row too
        RETURN FALSE
    FINALLY
        CLOSE connection
    END TRY
END FUNCTION

Python:

def record_result(self, match_id, home_id, home_points, home_tries,
                  away_id, away_points, away_tries):
    connection = self._connect()
    cursor = connection.cursor()

    sql = (
        "INSERT INTO matchteam (match_id, team_id, home_away, points, tries) "
        "VALUES (:match_id, :team_id, :home_away, :points, :tries)"
    )

    try:
        cursor.execute(sql, {"match_id": match_id, "team_id": home_id,
                             "home_away": "H", "points": home_points,
                             "tries": home_tries})
        cursor.execute(sql, {"match_id": match_id, "team_id": away_id,
                             "home_away": "A", "points": away_points,
                             "tries": away_tries})
        connection.commit()                   # both rows are saved together
        return True

    except sqlite3.IntegrityError:
        # Result already entered for this match
        connection.rollback()                 # undo the home row if it went in
        return False

    finally:
        connection.close()

Because the values are kept separate from the SQL, the same sql string is used for both rows, with a different dictionary each time.

[Record shop] Using the new ID in a second table: adding a vinyl

The Stock row is added first. Its lastrowid then becomes the StockID of the MusicItem row, which links the two.

Pseudo-code:

FUNCTION addVinyl(artistID, album, price, quantity, condition)
    OPEN connection TO database
    EXECUTE "INSERT INTO Stock (Category, Price, Quantity) VALUES ('Vinyl', price, quantity)"
    SET stockID TO ID of the new Stock row
    EXECUTE "INSERT INTO MusicItem (StockID, ArtistID, Album, Format, Condition)
             VALUES (stockID, artistID, album, 'LP', condition)"
    COMMIT changes
    CLOSE connection
    RETURN stockID
END FUNCTION

Python:

def add_vinyl(self, artist_id, album, price, quantity, condition):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "INSERT INTO Stock (Category, Price, Quantity) "
        "VALUES ('Vinyl', :price, :quantity)",
        {"price": price, "quantity": quantity},
    )
    stock_id = cursor.lastrowid          # the ID of the Stock row just added

    cursor.execute(
        "INSERT INTO MusicItem (StockID, ArtistID, Album, Format, Condition) "
        "VALUES (:stock_id, :artist_id, :album, 'LP', :condition)",
        {"stock_id": stock_id, "artist_id": artist_id,
         "album": album, "condition": condition},
    )

    connection.commit()
    connection.close()
    return stock_id

[Progress tracker] Adding several rows with a loop

INSERT OR IGNORE skips any pupil already in the class instead of raising an error, so running it twice is safe. The same idea works for adding several copies of a book in a library system.

Pseudo-code:

PROCEDURE addPupilsToClass(classID, pupilIDs)
    OPEN connection TO database
    FOR EACH pupilID IN pupilIDs
        EXECUTE "INSERT OR IGNORE INTO ClassPupil (ClassID, PupilID)
                 VALUES (classID, pupilID)"   // skips pupils already in the class
    END FOR
    COMMIT changes                           // one commit after the loop
    CLOSE connection
END PROCEDURE

Python:

def add_pupils_to_class(self, class_id, pupil_ids):
    connection = self._connect()
    cursor = connection.cursor()

    for pupil_id in pupil_ids:
        cursor.execute(
            "INSERT OR IGNORE INTO ClassPupil (ClassID, PupilID) "
            "VALUES (:class_id, :pupil_id)",
            {"class_id": class_id, "pupil_id": pupil_id},
        )

    connection.commit()                       # one commit after the loop
    connection.close()

3. Read

[All projects] Using the rows in a form

Rows come back in the same order as the columns in the SELECT. For SELECT id, username, email, created_at, user["email"] and user[2] are the same value. Reading by name is clearer and won’t break if you change the column order later.

Pseudo-code:

SET user TO db.getUserByUsername("jstephenson")
IF user = NULL THEN
    OUTPUT "No user with that username."
ELSE
    SET emailBox TO user["email"]
END IF

FOR EACH user IN db.getAllUsers()
    ADD (user["id"], user["username"], user["email"]) TO table
END FOR

Python:

user = self.db.get_user_by_username("jstephenson")
if user is None:
    messagebox.showerror("Users", "No user with that username.", parent=self.win)
else:
    self.email_entry.insert(0, user["email"])

for user in self.db.get_all_users():           # filling a ttk.Treeview
    self.tree.insert("", "end", values=(user["id"], user["username"], user["email"]))

[All projects] Joins and column names

Reading rows by name still works when a query joins tables, but there are three things to watch for.

1. The name has no table in front. SELECT Pupil.FirstName ... is read as row["FirstName"], not row["Pupil.FirstName"].

2. Two columns with the same name clash. In a theatre school, Student and Parent might both have a FirstName column. This query gives a row with two keys called FirstName:

cursor.execute(
    "SELECT Student.FirstName, Parent.FirstName "
    "FROM Student "
    "JOIN Parent ON Parent.ParentID = Student.ParentID"
)
row = cursor.fetchone()
row["FirstName"]          # always the FIRST one: the student's name

There’s no error. You just get the wrong value for the second column without noticing. Rename each one with AS, and the names are different:

cursor.execute(
    "SELECT Student.FirstName AS StudentName, "
    "Parent.FirstName AS ParentName "
    "FROM Student "
    "JOIN Parent ON Parent.ParentID = Student.ParentID"
)
row = cursor.fetchone()
row["StudentName"]        # the student's name
row["ParentName"]         # the parent's name

The same thing happens with ID columns. If you SELECT Loan.CustomerID, Customer.CustomerID, only select one of them, since they hold the same value.

3. Worked-out columns need a name too. Without AS, the key is the whole expression, so COUNT(*) would have to be read as row["COUNT(*)"]. Write COUNT(*) AS played and read row["played"], as in the rugby totals example below.

[Record shop] Searching with LIKE

The % signs mean “anything before or after”, so searching for thr finds “Thriller”. They’re added to the value, not the SQL. LIKE ignores upper and lower case for ordinary letters.

Pseudo-code:

FUNCTION searchMusic(text)
    OPEN connection TO database
    SET pattern TO "%" + text + "%"          // % = anything before or after
    EXECUTE "SELECT StockID, artist name, album, price, quantity
             FROM Stock JOIN MusicItem JOIN Artist
             WHERE artist name LIKE pattern OR album LIKE pattern
             ORDER BY artist name, album"
    SET results TO all matching rows
    CLOSE connection
    RETURN results
END FUNCTION

Python:

def search_music(self, text):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "SELECT Stock.StockID, Artist.Name, MusicItem.Album, "
        "Stock.Price, Stock.Quantity "
        "FROM Stock "
        "JOIN MusicItem ON MusicItem.StockID = Stock.StockID "
        "JOIN Artist ON Artist.ArtistID = MusicItem.ArtistID "
        "WHERE Artist.Name LIKE :pattern "
        "OR MusicItem.Album LIKE :pattern "
        "ORDER BY Artist.Name, MusicItem.Album",
        {"pattern": "%" + text + "%"},
    )

    results = cursor.fetchall()
    connection.close()
    return results

The same placeholder can appear more than once in the SQL. It only needs one entry in the dictionary.

[Progress tracker] Joining through a link table: a class list

ClassPupil only holds IDs, so it is joined to Pupil to get the names:

Pseudo-code:

FUNCTION getClassList(classID)
    OPEN connection TO database
    EXECUTE "SELECT PupilID, FirstName, Surname
             FROM ClassPupil JOIN Pupil
             WHERE ClassPupil.ClassID = classID
             ORDER BY Surname, FirstName"
    SET pupils TO all matching rows
    CLOSE connection
    RETURN pupils
END FUNCTION

Python:

def get_class_list(self, class_id):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "SELECT Pupil.PupilID, Pupil.FirstName, Pupil.Surname "
        "FROM ClassPupil "
        "JOIN Pupil ON Pupil.PupilID = ClassPupil.PupilID "
        "WHERE ClassPupil.ClassID = :class_id "
        "ORDER BY Pupil.Surname, Pupil.FirstName",
        {"class_id": class_id},
    )

    pupils = cursor.fetchall()
    connection.close()
    return pupils

[Library] Comparing dates: overdue loans

This works because dates are stored as YYYY-MM-DD, which puts them in the right order as text. An empty (NULL) DateReturned means the book is still out. There are no values from the user, so there’s no dictionary.

Pseudo-code:

FUNCTION getOverdueLoans()
    OPEN connection TO database
    EXECUTE "SELECT LoanID, Title, FirstName, Surname, DueDate
             FROM Loan JOIN Copy JOIN Book JOIN Customer
             WHERE DateReturned IS NULL       // still out
             AND DueDate < today              // and past its due date
             ORDER BY DueDate"
    SET loans TO all matching rows
    CLOSE connection
    RETURN loans
END FUNCTION

Python:

def get_overdue_loans(self):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "SELECT Loan.LoanID, Book.Title, Customer.FirstName, "
        "Customer.Surname, Loan.DueDate "
        "FROM Loan "
        "JOIN Copy ON Copy.CopyID = Loan.CopyID "
        "JOIN Book ON Book.ISBN = Copy.ISBN "
        "JOIN Customer ON Customer.CustomerID = Loan.CustomerID "
        "WHERE Loan.DateReturned IS NULL "
        "AND Loan.DueDate < date('now') "
        "ORDER BY Loan.DueDate"
    )

    loans = cursor.fetchall()
    connection.close()
    return loans

To show a stored date the British way in the form:

from datetime import datetime

datetime.strptime(loan["DueDate"], "%Y-%m-%d").strftime("%d/%m/%Y")   # '22/09/2026'

[Theatre school] Counting with LEFT JOIN: spaces left in each class

A normal JOIN would leave out classes with no bookings yet. LEFT JOIN keeps every class, and COUNT only counts the bookings that exist. Cancelled bookings are left out by the extra condition on the join.

Pseudo-code:

FUNCTION getUpcomingClasses()
    OPEN connection TO database
    EXECUTE "SELECT ClassID, Type, ClassDate,
                    SpacesAvailable - number of bookings AS SpacesLeft
             FROM Class LEFT JOIN Booking     // keep classes with no bookings
                  (only bookings with status 'Booked')
             WHERE ClassDate >= today
             GROUP BY ClassID
             ORDER BY ClassDate"
    SET classes TO all matching rows
    CLOSE connection
    RETURN classes
END FUNCTION

Python:

def get_upcoming_classes(self):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "SELECT Class.ClassID, Class.Type, Class.ClassDate, "
        "Class.SpacesAvailable - COUNT(Booking.BookingID) AS SpacesLeft "
        "FROM Class "
        "LEFT JOIN Booking ON Booking.ClassID = Class.ClassID "
        "AND Booking.BookingStatus = 'Booked' "
        "WHERE Class.ClassDate >= date('now') "
        "GROUP BY Class.ClassID "
        "ORDER BY Class.ClassDate"
    )

    classes = cursor.fetchall()
    connection.close()
    return classes

AS SpacesLeft names the worked-out column, so the form can read row["SpacesLeft"].

[Rugby] Totals with GROUP BY

GROUP BY makes one row per team, and SUM and COUNT work out the totals for each group:

Pseudo-code:

FUNCTION getTeamTotals(leagueID)
    OPEN connection TO database
    EXECUTE "SELECT team name, COUNT matches AS played,
                    SUM points AS points_scored, SUM tries AS tries_scored
             FROM matchteam JOIN team JOIN match
             WHERE match.league_id = leagueID
             GROUP BY team                    // one row per team
             ORDER BY points_scored DESC"
    SET totals TO all matching rows
    CLOSE connection
    RETURN totals
END FUNCTION

Python:

def get_team_totals(self, league_id):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "SELECT team.name, COUNT(*) AS played, "
        "SUM(matchteam.points) AS points_scored, "
        "SUM(matchteam.tries) AS tries_scored "
        "FROM matchteam "
        "JOIN team ON team.team_id = matchteam.team_id "
        "JOIN match ON match.match_id = matchteam.match_id "
        "WHERE match.league_id = :league_id "
        "GROUP BY team.team_id "
        "ORDER BY points_scored DESC",
        {"league_id": league_id},
    )

    totals = cursor.fetchall()
    connection.close()
    return totals

[All projects] Logging in with a username and password

A login needs three parts: storing a hash of the password when the account is made, checking a username and password against it, and a button that calls the check. These use the users table from the skeleton.

1. Hashing a password. A hash turns the password into a fixed string of characters that can’t be turned back. The same password always gives the same hash, so two hashes can be compared without the real password ever being stored. This function sits at the top of database.py, outside the class:

Pseudo-code:

FUNCTION hashPassword(password)
    RETURN SHA-256 hash of password as hex text
END FUNCTION

Python:

import hashlib

def hash_password(password):
    return hashlib.sha256(password.encode()).hexdigest()

2. Creating an account. The password is hashed before it goes into the database. username is UNIQUE, so a taken username raises an IntegrityError:

Pseudo-code:

FUNCTION createUser(username, email, password)
    OPEN connection TO database
    TRY
        EXECUTE "INSERT INTO users (username, email, password_hash)
                 VALUES (username, email, hashPassword(password))"
        COMMIT changes
        RETURN ID of the new row
    CATCH duplicate error                    // username already taken
        RETURN NULL
    FINALLY
        CLOSE connection
    END TRY
END FUNCTION

Python:

def create_user(self, username, email, password):
    connection = self._connect()
    cursor = connection.cursor()

    try:
        cursor.execute(
            "INSERT INTO users (username, email, password_hash) "
            "VALUES (:username, :email, :password_hash)",
            {"username": username, "email": email,
             "password_hash": hash_password(password)},
        )
        connection.commit()
        return cursor.lastrowid

    except sqlite3.IntegrityError:
        # Username already taken
        return None

    finally:
        connection.close()

3. Checking a login. Find the user by username, then hash what they typed and compare it with the stored hash. The method returns the user’s row if both match, or None if either is wrong:

Pseudo-code:

FUNCTION checkLogin(username, password)
    OPEN connection TO database
    EXECUTE "SELECT id, username, password_hash FROM users
             WHERE username = username"
    SET user TO the first matching row
    CLOSE connection

    IF user = NULL THEN
        RETURN NULL                          // no such username
    END IF
    IF hashPassword(password) != user["password_hash"] THEN
        RETURN NULL                          // wrong password
    END IF
    RETURN user                              // login OK
END FUNCTION

Python:

def check_login(self, username, password):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "SELECT id, username, password_hash FROM users "
        "WHERE username = :username",
        {"username": username},
    )

    user = cursor.fetchone()                  # None if no such username
    connection.close()

    if user is None:
        return None
    if hash_password(password) != user["password_hash"]:
        return None                           # wrong password
    return user

The username is found with a placeholder, and the password never appears in the SQL at all, so typing ' OR '1'='1 into either box can’t log anyone in.

4. The login button in forms.py. Check the boxes aren’t empty, ask the database, then open the main window or show an error. After three wrong attempts the button is disabled:

Pseudo-code:

PROCEDURE onLogin()
    SET username TO text in username box (spaces removed)
    SET password TO text in password box

    IF username = "" OR password = "" THEN
        OUTPUT "Please enter a username and a password."
        RETURN
    END IF

    SET user TO db.checkLogin(username, password)
    IF user = NULL THEN
        SET attempts TO attempts + 1
        CLEAR password box
        IF attempts >= 3 THEN
            OUTPUT "Too many attempts. The login is now locked."
            DISABLE login button
        ELSE
            OUTPUT "Wrong username or password."
        END IF
    ELSE
        HIDE login window
        OPEN main window FOR user
    END IF
END PROCEDURE

Python:

def on_login(self):
    username = self.username_entry.get().strip()
    password = self.password_entry.get()

    if not username or not password:
        messagebox.showwarning("Login", "Please enter a username and a password.")
        return

    user = self.db.check_login(username, password)
    if user is None:
        self.attempts += 1                    # set self.attempts = 0 in __init__
        self.password_entry.delete(0, tk.END)
        if self.attempts >= 3:
            messagebox.showerror("Login", "Too many attempts. The login is now locked.")
            self.login_button.config(state="disabled")
        else:
            messagebox.showerror("Login", "Wrong username or password.")
    else:
        self.root.withdraw()
        Main(self.root, self.db, user)        # pass the user so Main can say hello

The skeleton’s Main only takes master and db, so add a user parameter to its __init__ and keep it as self.user. Main can then read self.user["username"] to greet them or self.user["id"] to load their records.

The error message is the same whether the username or the password was wrong, so someone guessing can’t find out which usernames exist. To make the first account, use create_user in the Python shell, as shown on the App Skeleton page.

[Record shop] Checking a login and getting a role

This is the same check as check_login above, using a Staff table with a Role column so the form can open the right menu.

Pseudo-code:

FUNCTION login(username, password)
    OPEN connection TO database
    EXECUTE "SELECT StaffID, Role, PasswordHash FROM Staff WHERE Username = username"
    SET staff TO the first matching row
    CLOSE connection

    IF staff = NULL THEN
        RETURN NULL                          // no such username
    END IF
    IF hash(password) != staff["PasswordHash"] THEN
        RETURN NULL                          // wrong password
    END IF
    RETURN staff
END FUNCTION

Python:

def login(self, username, password):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "SELECT StaffID, Role, PasswordHash FROM Staff "
        "WHERE Username = :username",
        {"username": username},
    )

    staff = cursor.fetchone()
    connection.close()

    if staff is None:
        return None                               # no such username
    if hash_password(password) != staff["PasswordHash"]:
        return None                               # wrong password
    return staff                                  # staff["Role"] picks the menu

In the form:

Pseudo-code:

SET staff TO db.login(username, password)
IF staff = NULL THEN
    OUTPUT "Wrong username or password."
ELSE IF staff["Role"] = "Manager" THEN
    OPEN manager menu
ELSE
    OPEN staff menu
END IF

Python:

staff = self.db.login(username, password)
if staff is None:
    messagebox.showerror("Login failed", "Wrong username or password.")
elif staff["Role"] == "Manager":
    ManagerMenu(self.root, self.db)
else:
    StaffMenu(self.root, self.db)

4. Update

An UPDATE that matches no rows isn’t an error. It just changes nothing, so always check rowcount, as update_user_email does. Adding conditions to the WHERE clause turns this into a useful check.

[Library] Returning a book

Pseudo-code:

FUNCTION returnBook(loanID)
    OPEN connection TO database
    EXECUTE "UPDATE Loan SET DateReturned = today
             WHERE LoanID = loanID AND DateReturned IS NULL"
    COMMIT changes
    SET success TO (rows changed > 0)        // FALSE if already returned
    CLOSE connection
    RETURN success
END FUNCTION

Python:

def return_book(self, loan_id):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "UPDATE Loan SET DateReturned = date('now') "
        "WHERE LoanID = :loan_id AND DateReturned IS NULL",
        {"loan_id": loan_id},
    )

    connection.commit()
    success = cursor.rowcount > 0
    connection.close()
    return success

The DateReturned IS NULL condition stops a book being returned twice.

[Theatre school] Updating several fields

Separate each column = :value pair with a comma:

Pseudo-code:

FUNCTION updateParentContact(parentID, phone, email)
    OPEN connection TO database
    EXECUTE "UPDATE Parent SET PhoneNumber = phone, Email = email
             WHERE ParentID = parentID"
    COMMIT changes
    SET success TO (rows changed > 0)
    CLOSE connection
    RETURN success
END FUNCTION

Python:

def update_parent_contact(self, parent_id, phone, email):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "UPDATE Parent SET PhoneNumber = :phone, "
        "Email = :email "
        "WHERE ParentID = :parent_id",
        {"phone": phone, "email": email, "parent_id": parent_id},
    )

    connection.commit()
    success = cursor.rowcount > 0
    connection.close()
    return success

[Progress tracker] Copying one column into another: accepting a grade

A column can be set from another column in the same row, so accepting the generated grade doesn’t need a SELECT first:

Pseudo-code:

FUNCTION acceptGrade(reviewID, teacherID)
    OPEN connection TO database
    EXECUTE "UPDATE ProgressReview SET FinalGrade = GeneratedGrade,
                    ConfirmedBy = teacherID, ConfirmedDate = today
             WHERE ReviewID = reviewID"
    COMMIT changes
    SET success TO (rows changed > 0)
    CLOSE connection
    RETURN success
END FUNCTION

Python:

def accept_grade(self, review_id, teacher_id):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "UPDATE ProgressReview SET FinalGrade = GeneratedGrade, "
        "ConfirmedBy = :teacher_id, "
        "ConfirmedDate = date('now') "
        "WHERE ReviewID = :review_id",
        {"teacher_id": teacher_id, "review_id": review_id},
    )

    connection.commit()
    success = cursor.rowcount > 0
    connection.close()
    return success

[Record shop] Changing a number: reducing stock

Quantity = Quantity - :quantity lets the database do the sum. The Quantity >= :quantity condition stops stock going below zero: if there isn’t enough, nothing changes and the method returns False.

Pseudo-code:

FUNCTION removeStock(stockID, quantity)
    OPEN connection TO database
    EXECUTE "UPDATE Stock SET Quantity = Quantity - quantity
             WHERE StockID = stockID AND Quantity >= quantity"
    COMMIT changes
    SET success TO (rows changed > 0)        // FALSE if not enough stock
    CLOSE connection
    RETURN success
END FUNCTION

Python:

def remove_stock(self, stock_id, quantity):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "UPDATE Stock SET Quantity = Quantity - :quantity "
        "WHERE StockID = :stock_id "
        "AND Quantity >= :quantity",
        {"quantity": quantity, "stock_id": stock_id},
    )

    connection.commit()
    success = cursor.rowcount > 0             # False if there wasn't enough stock
    connection.close()
    return success

5. Delete

delete_user in the skeleton is the basic pattern: DELETE FROM ... WHERE id = :id, then commit() and check rowcount. Always include a WHERE clause: without one, every row in the table is deleted.

[Progress tracker] Deleting from a link table

A link-table row is identified by both IDs together, so both go in the WHERE clause. Using only PupilID would remove the pupil from every class.

Pseudo-code:

FUNCTION removePupilFromClass(classID, pupilID)
    OPEN connection TO database
    EXECUTE "DELETE FROM ClassPupil
             WHERE ClassID = classID AND PupilID = pupilID"   // both IDs
    COMMIT changes
    SET success TO (rows changed > 0)
    CLOSE connection
    RETURN success
END FUNCTION

Python:

def remove_pupil_from_class(self, class_id, pupil_id):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "DELETE FROM ClassPupil "
        "WHERE ClassID = :class_id "
        "AND PupilID = :pupil_id",
        {"class_id": class_id, "pupil_id": pupil_id},
    )

    connection.commit()
    success = cursor.rowcount > 0
    connection.close()
    return success

[Theatre school] Cancelling instead of deleting

Deleting a booking loses the record of it. Changing its status keeps the history, and the spaces-left query in section 3 already ignores cancelled bookings. A library could do the same with a copy that is withdrawn.

Pseudo-code:

FUNCTION cancelBooking(bookingID)
    OPEN connection TO database
    EXECUTE "UPDATE Booking SET BookingStatus = 'Cancelled'
             WHERE BookingID = bookingID"    // keeps the record
    COMMIT changes
    SET success TO (rows changed > 0)
    CLOSE connection
    RETURN success
END FUNCTION

Python:

def cancel_booking(self, booking_id):
    connection = self._connect()
    cursor = connection.cursor()

    cursor.execute(
        "UPDATE Booking SET BookingStatus = 'Cancelled' "
        "WHERE BookingID = :booking_id",
        {"booking_id": booking_id},
    )

    connection.commit()
    success = cursor.rowcount > 0
    connection.close()
    return success

[Any project] Deleting a row other rows depend on

With PRAGMA foreign_keys = ON (set in the skeleton’s _connect()), SQLite won’t let you delete a parent row that child rows still point at, such as a customer who still has loans. Catch the error and tell the form why:

Pseudo-code:

FUNCTION deleteCustomer(customerID)
    OPEN connection TO database
    TRY
        EXECUTE "DELETE FROM Customer WHERE CustomerID = customerID"
        COMMIT changes
        RETURN (rows changed > 0)
    CATCH foreign key error                  // customer still has loans
        RETURN FALSE
    FINALLY
        CLOSE connection
    END TRY
END FUNCTION

Python:

def delete_customer(self, customer_id):
    connection = self._connect()
    cursor = connection.cursor()

    try:
        cursor.execute(
            "DELETE FROM Customer WHERE CustomerID = :customer_id",
            {"customer_id": customer_id},
        )
        connection.commit()
        return cursor.rowcount > 0

    except sqlite3.IntegrityError:
        # Customer still has loans, so they can't be removed
        return False

    finally:
        connection.close()

[All projects] Asking before deleting

Deletes can’t be undone, so the form should always ask first. This is a button handler in forms.py:

Pseudo-code:

PROCEDURE onDelete()
    SET selected TO the highlighted row in the table
    IF nothing is selected THEN
        RETURN
    END IF
    SET userID TO first value in the selected row
    IF user answers YES to "Delete this user? This can't be undone." THEN
        IF db.deleteUser(userID) THEN
            REMOVE selected row FROM table
        ELSE
            OUTPUT "That user no longer exists."
        END IF
    END IF
END PROCEDURE

Python:

def on_delete(self):
    selected = self.tree.focus()
    if not selected:
        return
    user_id = self.tree.item(selected, "values")[0]
    if messagebox.askyesno("Delete user", "Delete this user? This can't be undone.",
                           parent=self.win):
        if self.db.delete_user(user_id):
            self.tree.delete(selected)
        else:
            messagebox.showerror("Delete user", "That user no longer exists.",
                                 parent=self.win)

6. Common errors

When a query fails, print the SQL and the dictionary together to see exactly what was sent: print(sql, values).

Error	Usual cause	Fix
OperationalError: no such column: …	A misspelt column name, or a placeholder written without its :.	Check the spelling against your CREATE TABLE.
OperationalError: near "…": syntax error	A missing comma, or a missing space between pieces of the string.	print() the SQL and check where the pieces join.
ProgrammingError: You did not supply a value for binding parameter :name	The dictionary has no key matching a placeholder.	Make the key and the placeholder the same name.
IntegrityError: UNIQUE constraint failed	A duplicate value, such as a username or a link-table pair that already exists.	Catch it with try/except and tell the user.
IntegrityError: NOT NULL constraint failed	A required field was left empty.	Add a presence check in the form.
IntegrityError: FOREIGN KEY constraint failed	An ID that doesn’t exist in the parent table, or deleting a parent that still has children.	Check the ID exists, or catch it and explain.
OperationalError: no such table	A misspelt table name, or the wrong database file.	Check the name and the DB_FILE path.
OperationalError: database is locked	A connection was left open without committing.	Make sure every method calls close().
No error, but the change isn’t saved	commit() was missed after an INSERT, UPDATE or DELETE.	Add connection.commit().
A value with an apostrophe (O’Brien) breaks the query	The value was joined into the SQL with +.	Use a :name placeholder instead.

7. Quick reference

Task	SQL	Commit?	Return
Add a row	INSERT INTO table (a, b) VALUES (:a, :b)	Yes	cursor.lastrowid
Get one row	SELECT ... FROM table WHERE id = :id	No	cursor.fetchone()
Get many rows	SELECT ... FROM table ORDER BY ...	No	cursor.fetchall()
Search	SELECT ... WHERE name LIKE :pattern	No	cursor.fetchall()
Change a row	UPDATE table SET a = :a WHERE id = :id	Yes	cursor.rowcount > 0
Remove a row	DELETE FROM table WHERE id = :id	Yes	cursor.rowcount > 0