40.1 Introduction to Databases
A database is an organized collection of information that can be stored, searched, updated, and deleted. Applications use databases to remember information after the program closes. Examples include customer accounts, products, orders, school records, messages, and payments. Databases are normally more reliable and searchable than storing large amounts of information in ordinary text files.
Example: Information that could be stored in a database
# A Python list can temporarily hold customer information.
customers = [
{"id": 1, "name": "Sara", "email": "sara@example.com"},
{"id": 2, "name": "Ali", "email": "ali@example.com"}
]
# Display every customer.
for customer in customers:
print(customer["id"], customer["name"], customer["email"])
Output:
1 Sara sara@example.com
2 Ali ali@example.com
Explanation:
The list stores information only while the program runs unless it is saved elsewhere. A database can store the same records permanently and provide tools for searching and changing them.
40.2 Relational Databases
A relational database stores information in tables and connects related tables using keys. For example, one table can store customers while another stores their orders. Each order can point to the customer who created it. This design reduces repeated information and makes large collections of data easier to organize, search, validate, and maintain.
Example: Related customer and order information
customers
+----+--------+
| id | name |
+----+--------+
| 1 | Sara |
| 2 | Ali |
+----+--------+
orders
+----+-------------+--------+
| id | customer_id | total |
+----+-------------+--------+
| 1 | 1 | 45.00 |
| 2 | 1 | 25.50 |
| 3 | 2 | 80.00 |
+----+-------------+--------+
Output:
Customer Sara has orders 1 and 2.
Customer Ali has order 3.
Explanation:
The customer_id value in the orders table connects each order to a customer. The customer name does not need to be repeated in every order row.
40.3 Tables, Rows, and Columns
A database table is similar to a spreadsheet. Columns describe the type of information stored, such as a name, email address, or price. Each row represents one complete record. A products table may have columns for ID, name, price, and quantity, while every row represents one individual product stored by the application.
Example: Products table
+----+----------+-------+----------+
| id | name | price | quantity |
+----+----------+-------+----------+
| 1 | Keyboard | 49.99 | 10 |
| 2 | Mouse | 24.50 | 25 |
| 3 | Monitor | 189.00| 8 |
+----+----------+-------+----------+
Output:
Columns: id, name, price, quantity
Rows: 3 product records
Explanation:
The table contains four columns. Each horizontal row stores one product. The first row represents a keyboard with an ID, price, and available quantity.
40.4 Primary Keys
A primary key is a column, or group of columns, that uniquely identifies every row in a table. Two rows cannot use the same primary-key value. An integer ID is commonly used because it gives each record a simple identifier. Primary keys help applications find, update, delete, and connect exact records without confusion.
Example: A table with a primary key
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
grade INTEGER
);
Example Records:
1 | Michael | 7
2 | Sara | 8
3 | Ali | 6
Explanation:
The id column is the primary key. Every student receives a different ID, even when two students have the same name or grade.
40.5 Foreign Keys
A foreign key is a column that refers to a primary key in another table. It creates a relationship between records. For example, an orders table can contain a customer_id column that points to the id column in the customers table. Foreign keys help protect relationships and reduce invalid references.
Example: Connect orders to customers
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
total REAL NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
Output:
orders.customer_id is connected to customers.id
Explanation:
Every order stores the ID of its customer. The foreign-key declaration explains that customer_id must refer to an ID from the customers table.
40.6 SQL Introduction
SQL means Structured Query Language. It is used to communicate with relational databases. SQL commands can create tables, insert records, retrieve information, update values, and delete rows. Common SQL keywords include CREATE, INSERT, SELECT, UPDATE, and DELETE. SQL keywords are commonly written in uppercase for readability.
Example: Basic SQL commands
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL
);
INSERT INTO books (title)
VALUES ('Learning Python');
SELECT * FROM books;
Output:
1 | Learning Python
Explanation:
The first statement creates a table. The second inserts a book. The final statement retrieves every column and row from the books table.
40.7 Creating Databases
Creating a database prepares a place where tables and records can be stored. The exact process depends on the database system. SQLite creates a database as a local file, while database servers such as PostgreSQL and MySQL use server commands and user permissions. Python can create an SQLite database automatically when it connects to a missing file.
Example: Create an SQLite database file
# Import Python's SQLite module.
import sqlite3
# Connect to the file.
# SQLite creates it when it does not already exist.
connection = sqlite3.connect("school.db")
# Confirm that the connection was created.
print("Database created successfully.")
# Close the connection.
connection.close()
Output:
Database created successfully.
Explanation:
After this program runs, a file named school.db appears in the project folder. The file can hold tables and data.
40.8 SQLite
SQLite is a lightweight relational database stored in a single file. It does not require a separate database server, account, or network connection. SQLite is useful for learning, desktop software, mobile applications, prototypes, testing, and small websites. Python includes the sqlite3 module, so beginners can use SQLite without installing an additional database driver.
Example: Display the SQLite version
# Import SQLite support.
import sqlite3
# Display the SQLite library version.
print(sqlite3.sqlite_version)
Explanation:
The exact version depends on the Python installation. The output confirms that SQLite support is available and shows which SQLite library version Python is using.
40.9 Connecting Python to SQLite
Python connects to SQLite using sqlite3.connect(). The returned connection object represents the open database. A cursor is commonly created from the connection and used to execute SQL statements. When the program finishes, it should close both the cursor and connection so that operating-system resources are released correctly.
Example: Open and close a connection
# Import the SQLite module.
import sqlite3
# Open the database connection.
connection = sqlite3.connect("library.db")
# Create a cursor for executing SQL.
cursor = connection.cursor()
# Confirm the connection.
print("Connected to library.db")
# Close the cursor.
cursor.close()
# Close the database connection.
connection.close()
Output:
Connected to library.db
Explanation:
The connection opens the database file. The cursor is the object that will execute SQL commands. Both objects are closed after use.
40.10 Creating Tables
A table is created with the SQL CREATE TABLE statement. Each column needs a name and data type. SQLite commonly uses INTEGER, REAL, TEXT, BLOB, and NULL. Constraints such as PRIMARY KEY, NOT NULL, UNIQUE, and DEFAULT control which values are accepted.
Example: Create a products table
# Import SQLite support.
import sqlite3
# Connect to the database.
connection = sqlite3.connect("store.db")
# Create a cursor.
cursor = connection.cursor()
# Create the products table when it does not exist.
cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
price REAL NOT NULL,
quantity INTEGER NOT NULL DEFAULT 0
)
""")
# Save the table creation.
connection.commit()
print("Products table created.")
# Close the database.
connection.close()
Output:
Products table created.
Explanation:
The table contains four columns. SQLite automatically creates each ID. The IF NOT EXISTS clause prevents an error when the table already exists.
40.11 Inserting Data
New rows are added with the SQL INSERT INTO statement. The statement identifies the table, columns, and values. Python programs should use placeholders instead of joining values directly into SQL strings. After inserting data, the connection must normally commit the transaction so that the changes are permanently saved.
Example: Insert a product
# Import SQLite support.
import sqlite3
# Connect to the store database.
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
# Insert one product using placeholders.
cursor.execute(
"""
INSERT INTO products (name, price, quantity)
VALUES (?, ?, ?)
""",
("Keyboard", 49.99, 10)
)
# Save the inserted row.
connection.commit()
# Display the generated product ID.
print("Product ID:", cursor.lastrowid)
connection.close()
Explanation:
The three question marks are placeholders for the product values. SQLite generates the ID automatically, and lastrowid returns the new row’s ID.
40.12 Reading Data
Data is retrieved with the SQL SELECT statement. A query can select every column with an asterisk or request only specific columns. Python cursor methods include fetchone(), fetchmany(), and fetchall(). Each database row is normally returned as a tuple unless a custom row format is configured.
Example: Read all products
# Import SQLite support.
import sqlite3
# Connect to the database.
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
# Select the product information.
cursor.execute("""
SELECT id, name, price, quantity
FROM products
""")
# Retrieve all matching rows.
products = cursor.fetchall()
# Display every row.
for product in products:
print(product)
connection.close()
Example Output:
(1, 'Keyboard', 49.99, 10)
(2, 'Mouse', 24.5, 25)
(3, 'Monitor', 189.0, 8)
Explanation:
Each tuple represents one database row. The values appear in the same order as the columns listed in the SELECT statement.
40.13 Updating Data
Existing rows are changed with the SQL UPDATE statement. The SET clause identifies which columns receive new values, while the WHERE clause selects the rows to change. Without a WHERE clause, every row may be updated, so beginners should carefully check update conditions before committing.
Example: Update a product quantity
# Import SQLite support.
import sqlite3
# Connect to the database.
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
# Update the quantity of product ID 1.
cursor.execute(
"""
UPDATE products
SET quantity = ?
WHERE id = ?
""",
(15, 1)
)
# Save the update.
connection.commit()
# Display how many rows changed.
print("Rows updated:", cursor.rowcount)
connection.close()
Explanation:
Only the product with ID 1 is updated. Its quantity becomes 15. The rowcount property reports that one row was changed.
40.14 Deleting Data
Rows are removed with the SQL DELETE FROM statement. A WHERE condition should usually be included so that only the intended records are deleted. Running DELETE FROM products without a condition removes every product. Important applications may use confirmation steps, backups, transactions, or soft deletion before permanently removing information.
Example: Delete one product
# Import SQLite support.
import sqlite3
# Connect to the database.
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
# Delete the product with ID 3.
cursor.execute(
"""
DELETE FROM products
WHERE id = ?
""",
(3,)
)
# Save the deletion.
connection.commit()
# Display the number of deleted rows.
print("Rows deleted:", cursor.rowcount)
connection.close()
Explanation:
The one-element tuple must contain a trailing comma. Only the row whose ID equals 3 is removed from the products table.
40.15 Filtering Data
Filtering limits query results to rows that meet specific conditions. SQL uses the WHERE clause with operators such as equals, greater than, less than, LIKE, IN, and BETWEEN. Conditions can be combined with AND, OR, and NOT to create more precise searches.
Example: Find products with low inventory
# Import SQLite support.
import sqlite3
# Connect to the database.
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
# Find products whose quantity is below the limit.
cursor.execute(
"""
SELECT name, quantity
FROM products
WHERE quantity < ?
""",
(12,)
)
# Display matching products.
for name, quantity in cursor.fetchall():
print(name, quantity)
connection.close()
Example Output:
Keyboard 10
Monitor 8
Explanation:
The query returns only products whose quantity is less than 12. Products with quantities of 12 or more are excluded.
40.16 Sorting Data
SQL sorts query results with the ORDER BY clause. Ascending order uses ASC and is usually the default. Descending order uses DESC. Results can be sorted by text, numbers, dates, or multiple columns. Sorting occurs inside the database before the rows are returned to Python.
Example: Sort products by highest price
# Import SQLite support.
import sqlite3
# Connect to the database.
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
# Select products from highest to lowest price.
cursor.execute("""
SELECT name, price
FROM products
ORDER BY price DESC
""")
# Display the sorted records.
for name, price in cursor.fetchall():
print(f"{name}: ${price:.2f}")
connection.close()
Example Output:
Monitor: $189.00
Keyboard: $49.99
Mouse: $24.50
Explanation:
The DESC keyword places the most expensive product first. Using ASC would place the lowest-priced product first.
40.17 SQL Joins
A SQL join combines related rows from two or more tables. An INNER JOIN returns records that match in both tables. A LEFT JOIN keeps every row from the left table even when no related row exists. Joins allow applications to retrieve connected information without storing all details in one large table.
Example: Join customers and orders
# Import SQLite support.
import sqlite3
# Connect to the database.
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
# Combine customer names with their order totals.
cursor.execute("""
SELECT customers.name, orders.total
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id
ORDER BY customers.name
""")
# Display the combined information.
for customer_name, order_total in cursor.fetchall():
print(f"{customer_name}: ${order_total:.2f}")
connection.close()
Example Output:
Ali: $80.00
Sara: $25.50
Sara: $45.00
Explanation:
The join compares customers.id with orders.customer_id. Each matching order is displayed beside the correct customer name.
40.18 Transactions
A transaction groups database changes into one complete operation. If every step succeeds, the transaction is committed. If a step fails, the transaction can be rolled back so that partial changes are not saved. Transactions are important for payments, account transfers, inventory updates, and other tasks where several related changes must succeed together.
Example: Transfer inventory between locations
# Import SQLite support.
import sqlite3
# Connect to the database.
connection = sqlite3.connect("inventory.db")
cursor = connection.cursor()
try:
# Remove five items from location 1.
cursor.execute(
"""
UPDATE stock
SET quantity = quantity - ?
WHERE product_id = ? AND location_id = ?
""",
(5, 1, 1)
)
# Add five items to location 2.
cursor.execute(
"""
UPDATE stock
SET quantity = quantity + ?
WHERE product_id = ? AND location_id = ?
""",
(5, 1, 2)
)
# Save both updates together.
connection.commit()
print("Transfer completed.")
except sqlite3.Error:
# Cancel all changes when an error occurs.
connection.rollback()
print("Transfer cancelled.")
finally:
connection.close()
Possible Output:
Transfer completed.
Explanation:
Both inventory updates belong to one transaction. If either update fails, rollback() cancels both changes and protects the database from an incomplete transfer.
40.19 Parameterized Queries
A parameterized query keeps SQL instructions separate from data values. SQLite uses question marks as placeholders, and the actual values are passed separately. The database driver safely handles quotation marks and special characters. Parameterized queries are easier to read, safer than string building, and should be used whenever values come from variables or users.
Example: Search by a parameter
# Import SQLite support.
import sqlite3
# Connect to the database.
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
# Store the value separately from the SQL statement.
product_name = "Keyboard"
# Use a placeholder for the value.
cursor.execute(
"""
SELECT id, name, price
FROM products
WHERE name = ?
""",
(product_name,)
)
# Retrieve one matching record.
product = cursor.fetchone()
print(product)
connection.close()
Output:
(1, 'Keyboard', 49.99)
Explanation:
The product name is passed separately from the SQL code. SQLite handles the value safely and returns the matching product row.
40.20 Preventing SQL Injection
SQL injection happens when untrusted input changes the meaning of an SQL statement. Building queries with string concatenation or formatted strings can allow malicious input to insert extra SQL instructions. Parameterized queries prevent values from being treated as SQL code. Applications should also validate input, limit database permissions, and avoid revealing detailed database errors.
Unsafe and safe query examples
# UNSAFE EXAMPLE:
# Never place user input directly inside SQL text.
username = input("Username: ")
unsafe_query = (
"SELECT * FROM users "
"WHERE username = '" + username + "'"
)
# SAFE EXAMPLE:
# Use a placeholder and pass the value separately.
cursor.execute(
"""
SELECT id, username
FROM users
WHERE username = ?
""",
(username,)
)
Safe Output:
The input is treated only as a username value.
It cannot become part of the SQL command.
Explanation:
In the safe version, SQLite receives the SQL structure and username separately. Special characters inside the username cannot change the query’s instructions.
40.21 Database Error Handling
Database operations can fail because of invalid SQL, missing tables, duplicate values, unavailable files, broken constraints, or incorrect data types. Python’s sqlite3 module provides exception classes such as Error, IntegrityError, and OperationalError. Programs should catch expected errors, roll back unfinished work, report helpful messages, and always close resources.
Example: Handle duplicate email addresses
# Import SQLite support.
import sqlite3
connection = None
try:
# Open the database.
connection = sqlite3.connect("users.db")
cursor = connection.cursor()
# Insert a user.
cursor.execute(
"""
INSERT INTO users (name, email)
VALUES (?, ?)
""",
("Sara", "sara@example.com")
)
# Save the new user.
connection.commit()
print("User created successfully.")
except sqlite3.IntegrityError as error:
# Cancel the unfinished change.
if connection:
connection.rollback()
print("The email address already exists.")
print("Database message:", error)
except sqlite3.Error as error:
# Handle other SQLite errors.
if connection:
connection.rollback()
print("A database error occurred:", error)
finally:
# Close the connection when it was opened.
if connection:
connection.close()
Possible Output:
The email address already exists.
Database message: UNIQUE constraint failed: users.email
Explanation:
A duplicate email violates a unique constraint. The program catches the specific integrity error, rolls back the operation, and displays a clearer message.
40.22 Context Managers and Databases
A context manager uses the with statement to manage resources and transactions. When an SQLite connection is used in a with block, successful work is committed automatically, while an exception causes a rollback. The connection should still be closed when finished. Context managers reduce repeated transaction code and make database operations easier to read.
Example: Use a connection as a context manager
# Import SQLite support.
import sqlite3
# Open the database connection.
connection = sqlite3.connect("tasks.db")
try:
# Commit automatically when the block succeeds.
# Roll back automatically when it fails.
with connection:
connection.execute(
"""
INSERT INTO tasks (title, completed)
VALUES (?, ?)
""",
("Study SQLite", 0)
)
print("Task saved successfully.")
finally:
# Close the connection after all work.
connection.close()
Output:
Task saved successfully.
Explanation:
The insert is committed when no exception occurs. If the insert fails, the connection rolls back the transaction before leaving the with block.
40.23 Practical Database Application
A practical database application separates connection setup, table creation, data operations, and user interaction into clear functions. This example builds a small contact manager. It creates a table, adds contacts, lists saved contacts, and searches by name. Every user value is passed through parameterized queries instead of being added directly to SQL text.
Example: Simple contact manager
# Import SQLite support.
import sqlite3
# Create a connection helper.
def connect_database():
return sqlite3.connect("contacts.db")
# Create the contacts table.
def create_table():
with connect_database() as connection:
connection.execute("""
CREATE TABLE IF NOT EXISTS contacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
phone TEXT NOT NULL,
email TEXT UNIQUE
)
""")
# Add a contact.
def add_contact(name, phone, email):
with connect_database() as connection:
connection.execute(
"""
INSERT INTO contacts (name, phone, email)
VALUES (?, ?, ?)
""",
(name, phone, email)
)
# Return all contacts.
def get_contacts():
with connect_database() as connection:
cursor = connection.execute("""
SELECT id, name, phone, email
FROM contacts
ORDER BY name
""")
return cursor.fetchall()
# Search contacts by part of a name.
def search_contacts(search_text):
with connect_database() as connection:
cursor = connection.execute(
"""
SELECT id, name, phone, email
FROM contacts
WHERE name LIKE ?
ORDER BY name
""",
(f"%{search_text}%",)
)
return cursor.fetchall()
# Prepare the database.
create_table()
# Add sample contacts.
try:
add_contact(
"Sara Smith",
"416-555-1000",
"sara@example.com"
)
add_contact(
"Ali Johnson",
"905-555-2000",
"ali@example.com"
)
except sqlite3.IntegrityError:
print("One or more contacts already exist.")
# Display every contact.
for contact in get_contacts():
print(contact)
# Search for contacts containing Sara.
print("Search results:")
for contact in search_contacts("Sara"):
print(contact)
Example Output:
(2, 'Ali Johnson', '905-555-2000', 'ali@example.com')
(1, 'Sara Smith', '416-555-1000', 'sara@example.com')
Search results:
(1, 'Sara Smith', '416-555-1000', 'sara@example.com')
Explanation:
The functions hide the SQL details from the main program. The application safely inserts contacts, sorts them by name, and searches using a parameterized LIKE query.
40.24 Chapter Practice Exercises
These exercises help you practise database design, SQL commands, SQLite connections, parameterized queries, filtering, sorting, joins, transactions, and error handling. Complete each exercise in a separate Python file. Run the program more than once, inspect the generated database file, and test valid as well as invalid values to understand how the database responds.
Exercise 1: Student database
# Create a database named school.db.
# Create a students table with:
# id
# name
# grade
# email
#
# Make id the primary key.
# Make email unique.
Exercise 2: Insert and read books
# Create a books table.
# Insert at least five books.
# Display:
# title
# author
# price
Exercise 3: Filter and sort products
# Find products that:
# cost less than $100
# have more than 5 items available
#
# Sort the results from highest price to lowest price.
Exercise 4: Update and delete records
# Update the quantity of one product.
# Delete a product using its ID.
# Display the number of affected rows.
Exercise 5: Create a join
# Create authors and books tables.
# Add a foreign key from books to authors.
# Display each book title beside its author name.
Exercise 6: Safe user search
# Ask the user for part of a product name.
# Use a parameterized LIKE query.
# Display every matching product.
Exercise 7: Transaction practice
# Create two bank accounts.
# Transfer an amount from one account to another.
# Commit only when both updates succeed.
# Roll back when either update fails.
Possible Output:
Database created successfully.
5 records inserted.
3 matching records found.
Transaction completed successfully.
Explanation:
Your exact output depends on your database contents. A correct solution should use placeholders for variable values, commit successful changes, and handle errors without leaving partial updates.
40.25 Chapter Project
In this chapter project, you will create a complete library-management application using SQLite. The application will store authors, books, and loans in related tables. It will add books, register members, lend books, return books, search records, and display joined information. Transactions and parameterized queries will protect the accuracy and safety of the data.
Step 1: Create the project structure
library_manager/
│
├── app.py
├── database.py
├── library_service.py
└── library.db
Step 2: Create database.py
# database.py
# Import SQLite support.
import sqlite3
# Store the database filename.
DATABASE_NAME = "library.db"
# Create a database connection.
def get_connection():
# Open the SQLite database.
connection = sqlite3.connect(DATABASE_NAME)
# Return rows that support column names.
connection.row_factory = sqlite3.Row
# Turn on foreign-key enforcement.
connection.execute("PRAGMA foreign_keys = ON")
# Return the prepared connection.
return connection
# Create all required tables.
def create_tables():
# Open the connection.
connection = get_connection()
try:
# Use one transaction for all table creation.
with connection:
# Create the authors table.
connection.execute("""
CREATE TABLE IF NOT EXISTS authors (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
)
""")
# Create the books table.
connection.execute("""
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
author_id INTEGER NOT NULL,
isbn TEXT NOT NULL UNIQUE,
available INTEGER NOT NULL DEFAULT 1,
FOREIGN KEY (author_id)
REFERENCES authors(id)
)
""")
# Create the members table.
connection.execute("""
CREATE TABLE IF NOT EXISTS members (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)
""")
# Create the loans table.
connection.execute("""
CREATE TABLE IF NOT EXISTS loans (
id INTEGER PRIMARY KEY AUTOINCREMENT,
book_id INTEGER NOT NULL,
member_id INTEGER NOT NULL,
loan_date TEXT NOT NULL,
return_date TEXT,
FOREIGN KEY (book_id)
REFERENCES books(id),
FOREIGN KEY (member_id)
REFERENCES members(id)
)
""")
finally:
# Close the database connection.
connection.close()
Step 3: Create library_service.py
# library_service.py
# Import date support.
from datetime import date
# Import SQLite exceptions.
import sqlite3
# Import the connection helper.
from database import get_connection
# Add an author and return the author's ID.
def add_author(name):
connection = get_connection()
try:
with connection:
cursor = connection.execute(
"""
INSERT INTO authors (name)
VALUES (?)
""",
(name,)
)
return cursor.lastrowid
finally:
connection.close()
# Add a book.
def add_book(title, author_id, isbn):
connection = get_connection()
try:
with connection:
cursor = connection.execute(
"""
INSERT INTO books (
title,
author_id,
isbn
)
VALUES (?, ?, ?)
""",
(title, author_id, isbn)
)
return cursor.lastrowid
finally:
connection.close()
# Add a library member.
def add_member(name, email):
connection = get_connection()
try:
with connection:
cursor = connection.execute(
"""
INSERT INTO members (name, email)
VALUES (?, ?)
""",
(name, email)
)
return cursor.lastrowid
finally:
connection.close()
# Return books with their author names.
def get_books():
connection = get_connection()
try:
cursor = connection.execute("""
SELECT
books.id,
books.title,
authors.name AS author_name,
books.isbn,
books.available
FROM books
INNER JOIN authors
ON books.author_id = authors.id
ORDER BY books.title
""")
return cursor.fetchall()
finally:
connection.close()
# Search for books by title.
def search_books(search_text):
connection = get_connection()
try:
cursor = connection.execute(
"""
SELECT
books.id,
books.title,
authors.name AS author_name,
books.isbn,
books.available
FROM books
INNER JOIN authors
ON books.author_id = authors.id
WHERE books.title LIKE ?
ORDER BY books.title
""",
(f"%{search_text}%",)
)
return cursor.fetchall()
finally:
connection.close()
# Lend a book to a member.
def lend_book(book_id, member_id):
connection = get_connection()
try:
# Begin one transaction for checking and lending.
with connection:
# Find the selected book.
cursor = connection.execute(
"""
SELECT available
FROM books
WHERE id = ?
""",
(book_id,)
)
book = cursor.fetchone()
# Confirm that the book exists.
if book is None:
raise ValueError("Book not found.")
# Confirm that the book is available.
if book["available"] == 0:
raise ValueError(
"The book is already on loan."
)
# Create the loan record.
connection.execute(
"""
INSERT INTO loans (
book_id,
member_id,
loan_date
)
VALUES (?, ?, ?)
""",
(
book_id,
member_id,
date.today().isoformat()
)
)
# Mark the book as unavailable.
connection.execute(
"""
UPDATE books
SET available = 0
WHERE id = ?
""",
(book_id,)
)
finally:
connection.close()
# Return a borrowed book.
def return_book(book_id):
connection = get_connection()
try:
with connection:
# Find the active loan.
cursor = connection.execute(
"""
SELECT id
FROM loans
WHERE book_id = ?
AND return_date IS NULL
""",
(book_id,)
)
loan = cursor.fetchone()
# Confirm that an active loan exists.
if loan is None:
raise ValueError(
"No active loan was found."
)
# Add the return date.
connection.execute(
"""
UPDATE loans
SET return_date = ?
WHERE id = ?
""",
(
date.today().isoformat(),
loan["id"]
)
)
# Mark the book as available.
connection.execute(
"""
UPDATE books
SET available = 1
WHERE id = ?
""",
(book_id,)
)
finally:
connection.close()
# Return active loans with joined information.
def get_active_loans():
connection = get_connection()
try:
cursor = connection.execute("""
SELECT
loans.id,
books.title,
members.name AS member_name,
loans.loan_date
FROM loans
INNER JOIN books
ON loans.book_id = books.id
INNER JOIN members
ON loans.member_id = members.id
WHERE loans.return_date IS NULL
ORDER BY loans.loan_date
""")
return cursor.fetchall()
finally:
connection.close()
Step 4: Create app.py
# app.py
# Import SQLite error support.
import sqlite3
# Import database preparation.
from database import create_tables
# Import library operations.
from library_service import (
add_author,
add_book,
add_member,
get_books,
search_books,
lend_book,
return_book,
get_active_loans
)
# Display every book.
def display_books(books):
# Handle an empty result.
if not books:
print("No books were found.")
return
# Display each book.
for book in books:
status = (
"Available"
if book["available"]
else "On loan"
)
print(
f'{book["id"]}: '
f'{book["title"]} by '
f'{book["author_name"]} | '
f'ISBN: {book["isbn"]} | '
f'{status}'
)
# Display the menu.
def show_menu():
print("\nLibrary Manager")
print("1. Add author")
print("2. Add book")
print("3. Add member")
print("4. List books")
print("5. Search books")
print("6. Lend book")
print("7. Return book")
print("8. Show active loans")
print("9. Exit")
# Run the application.
def main():
# Create the database tables.
create_tables()
while True:
# Display the available actions.
show_menu()
# Read the selected option.
choice = input("Choose an option: ").strip()
try:
if choice == "1":
# Read the author name.
name = input("Author name: ").strip()
# Add the author.
author_id = add_author(name)
print(
f"Author created with ID {author_id}."
)
elif choice == "2":
# Read the book details.
title = input("Book title: ").strip()
author_id = int(
input("Author ID: ")
)
isbn = input("ISBN: ").strip()
# Add the book.
book_id = add_book(
title,
author_id,
isbn
)
print(
f"Book created with ID {book_id}."
)
elif choice == "3":
# Read the member details.
name = input("Member name: ").strip()
email = input("Member email: ").strip()
# Add the member.
member_id = add_member(name, email)
print(
f"Member created with ID {member_id}."
)
elif choice == "4":
# Display every book.
display_books(get_books())
elif choice == "5":
# Read the title search text.
search_text = input(
"Search title: "
).strip()
# Display matching books.
display_books(
search_books(search_text)
)
elif choice == "6":
# Read the selected IDs.
book_id = int(input("Book ID: "))
member_id = int(input("Member ID: "))
# Lend the book.
lend_book(book_id, member_id)
print("Book loan created.")
elif choice == "7":
# Read the book ID.
book_id = int(input("Book ID: "))
# Return the book.
return_book(book_id)
print("Book returned successfully.")
elif choice == "8":
# Retrieve active loans.
loans = get_active_loans()
# Display every active loan.
if not loans:
print("There are no active loans.")
else:
for loan in loans:
print(
f'{loan["id"]}: '
f'{loan["title"]} borrowed by '
f'{loan["member_name"]} on '
f'{loan["loan_date"]}'
)
elif choice == "9":
# End the application.
print("Goodbye.")
break
else:
print("Invalid option.")
except ValueError as error:
# Handle validation and conversion errors.
print("Error:", error)
except sqlite3.IntegrityError as error:
# Handle duplicate and foreign-key errors.
print("The information could not be saved.")
print("Database message:", error)
except sqlite3.Error as error:
# Handle other database problems.
print("A database error occurred:", error)
# Start the program.
if __name__ == "__main__":
main()
Step 5: Run the project
python app.py
Example Output:
Library Manager
1. Add author
2. Add book
3. Add member
4. List books
5. Search books
6. Lend book
7. Return book
8. Show active loans
9. Exit
Choose an option: 1
Author name: George Orwell
Author created with ID 1.
Choose an option: 2
Book title: Animal Farm
Author ID: 1
ISBN: 9780451526342
Book created with ID 1.
Choose an option: 3
Member name: Sara Smith
Member email: sara@example.com
Member created with ID 1.
Choose an option: 6
Book ID: 1
Member ID: 1
Book loan created.
Choose an option: 8
1: Animal Farm borrowed by Sara Smith on 2026-07-19
Explanation:
This project uses four related database tables. It creates primary and foreign keys, performs safe parameterized queries, uses joins to combine records, and protects multi-step lending and return operations with transactions. The project also handles duplicate values, invalid IDs, unavailable books, and other database errors.
