C C++ CSS Full Stack HTML JavaScript Node PHP PYTHON React

PYTHON · LESSON 53

SQLite and Relational Databases

Persist relational data safely with parameterised SQL and transactions.

Level: Beginner to Advanced

Duration: ~60 Mins Deep-Dive

Updated: 26 Jul 2026

Lesson 53: SQLite and Relational Databases

1. Introduction to Enterprise Database Persistence

Welcome to Module 9 of our Python Mastery Series: Database Persistence and ORMs! Up to this point in our comprehensive course, we managed persistent data using raw text streams, structured JSON files, and CSV spreadsheets. While file-based persistence works well for small datasets, enterprise application engineering requires querying millions of records efficiently, enforcing strict data schemas, running complex joins across related entities, and guaranteeing transactional ACID compliance.

A Relational Database Management System (RDBMS) organizes structured data into logical tables comprised of rows and columns, linked through mathematical relational keys. The industry standard language used to create, query, update, and delete relational data is Structured Query Language (SQL).

Python includes a lightweight, serverless, self-contained relational database engine directly in its standard library: `sqlite3`. SQLite stores entire relational databases in a single cross-platform disk file (or inside system RAM via :memory:), making it ideal for embedded applications, testing suites, desktop tools, and local data persistence.

In this lesson, we will master Python's standard `sqlite3` module, construct relational database tables with constraints, execute core CRUD (Create, Read, Update, Delete) statements, prevent critical SQL Injection Vulnerabilities using parameterized queries, manage database transactions (`commit` and `rollback`), utilize custom row factories (`sqlite3.Row`), and route database query record streams using Structural Pattern Matching (`match-case`).

2. Core Relational Database Concepts & SQL Fundamentals

Before writing Python database code, developers must understand four foundational relational database concepts:

Tables & Schemas: A table is a named collection of related data records structured into explicit columns (attributes) and rows (tuples).
Primary Key (PK): A column (or combination of columns) that uniquely identifies every distinct row in a table (e.g., user_id INTEGER PRIMARY KEY).
Foreign Key (FK): A column referencing the Primary Key of another table, establishing a relational association (e.g., account_id REFERENCES accounts(id)).
ACID Properties: Enterprise database transactions guarantee **A**tomaticity (all-or-nothing execution), **C**onsistency (schema rule enforcement), **I**solation (concurrent transaction protection), and **D**urability (persisted committed state).

1. Essential SQL Commands Matrix

CREATE TABLE
DDL (Data Definition)
CREATE TABLE users (id INT PRIMARY KEY, name TEXT);
Defines a new table structure with data types and column constraints.
INSERT INTO
DML (Data Manipulation)
INSERT INTO users (id, name) VALUES (1, 'Alice');
Adds new data rows into a target table.
SELECT
DQL (Data Query)
SELECT * FROM users WHERE id = 1;
Queries and retrieves data rows matching filter criteria.
UPDATE
DML (Data Manipulation)
UPDATE users SET name = 'Bob' WHERE id = 1;
Modifies existing column values in matching table rows.
DELETE
DML (Data Manipulation)
DELETE FROM users WHERE id = 1;
Removes matching rows permanently from a table.

SQL Command	Operation Type	SQL Syntax Pattern	Purpose / Description

3. The Standard `sqlite3` Workflow: Connections, Cursors & Tables

Interacting with SQLite in Python follows a deterministic 4-step sequence:

Establish a Connection object to a database file (or ":memory:").
Create a Cursor object from the connection to execute SQL queries.
Execute SQL statements via cursor.execute() or cursor.executemany().
Commit transaction state via connection.commit() and close handles via connection.close().

SQLITE3_BASIC_WORKFLOW.PY

import sqlite3

# 1. Connecting to an In-Memory Database (Ideal for tests and rapid prototypes!)
conn = sqlite3.connect(":memory:")

# 2. Creating a Cursor handle
cursor = conn.cursor()

# 3. Executing DDL: Creating accounts table
cursor.execute("""
CREATE TABLE IF NOT EXISTS accounts (
    account_id TEXT PRIMARY KEY,
    owner_name TEXT NOT NULL,
    balance REAL CHECK(balance >= 0.0),
    status TEXT DEFAULT 'ACTIVE'
);
""")

# 4. Executing DML: Inserting initial records
cursor.execute("""
INSERT INTO accounts (account_id, owner_name, balance)
VALUES ('ACC_1001', 'Alice Developer', 1250.50);
""")

# 5. Committing transaction to persist changes
conn.commit()

print("=== In-Memory SQLite Table Creation & Initial Insertion Successful ===")

# Querying inserted row to verify
cursor.execute("SELECT * FROM accounts WHERE account_id = 'ACC_1001';")
row = cursor.fetchone()
print("Retrieved Tuple Row:", row)

conn.close()

OUTPUT

=== In-Memory SQLite Table Creation & Initial Insertion Successful ===
Retrieved Tuple Row: ('ACC_1001', 'Alice Developer', 1250.5, 'ACTIVE')

4. Critical Security Rule: Preventing SQL Injection via Parameterized Queries

In Lesson 52, we discussed command injection vulnerabilities in shell execution. In database engineering, the equivalent catastrophic security threat is SQL Injection (SQLi).

SQL Injection occurs when developers construct SQL query strings using raw Python string concatenation or f-string interpolation (e.g., f"SELECT * FROM users WHERE name = '{user_input}';").

The SQL Injection Vulnerability: Consider a login input string: admin' OR '1'='1. If passed into an f-string query:

f"SELECT * FROM users WHERE username = '{user_input}' AND pass = '{pass_input}';"

The resulting SQL statement becomes:
SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND pass = '...';
Because '1'='1' is always True, the SQL engine bypasses authentication completely and logs the attacker in as administrator!

1. The Defense Solution: Parameterized Queries (`?` Placeholders)

NEVER interpolate variables into SQL strings! ALWAYS use Parameterized Queries with ? placeholders (or named placeholders :key). Parameterized queries send SQL logic and user data parameters to the SQLite engine separately, guaranteeing that user input parameters are treated strictly as literal data constants, rendering SQL injection impossible!

PARAMETERIZED_QUERIES_DEMO.PY

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, role TEXT);")

# Sample dataset for bulk insertion
user_batch = [
    (101, "alice_dev", "ADMIN"),
    (102, "bob_tester", "MEMBER"),
    (103, "charlie_guest", "GUEST")
]

# 1. SECURE BULK INSERTION: Using cursor.executemany with ? placeholders
cursor.executemany("INSERT INTO users (id, username, role) VALUES (?, ?, ?);", user_batch)
conn.commit()

# 2. SECURE PARAMETERIZED QUERY: Prevents SQL Injection
untrusted_search_input = "alice_dev"
search_query = "SELECT * FROM users WHERE username = ? AND role = ?;"

# Passing data tuple as second argument to .execute()
cursor.execute(search_query, (untrusted_search_input, "ADMIN"))
result = cursor.fetchone()

print("=== Parameterized Query Result Inspection ===")
print("Query Result Row:", result)

conn.close()

OUTPUT

=== Parameterized Query Result Inspection ===
Query Result Row: (101, 'alice_dev', 'ADMIN')

5. Advanced Result Handling: `sqlite3.Row` Factory & Transactions

1. Friendly Field Access via `sqlite3.Row`

By default, sqlite3 query fetches return raw tuples (e.g., (101, 'alice_dev', 'ADMIN')), forcing code to rely on obscure integer array indices (row[0], row[1]).

By setting conn.row_factory = sqlite3.Row, returned rows behave like dictionary objects! Developers can access database columns cleanly using string keys (row["username"] or row["role"]).

2. Transaction Management and Context Managers

Connection objects in sqlite3 act as context managers (with conn:). Wrapping database mutations inside a with conn: block commits transactions automatically if execution succeeds, or issues an automatic conn.rollback() if an unhandled exception occurs during execution!

ROW_FACTORY_TRANSACTIONS_DEMO.PY

import sqlite3

conn = sqlite3.connect(":memory:")
# Enforcing Dictionary-like Row Factory
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("CREATE TABLE bank (acc_id TEXT PRIMARY KEY, balance REAL);")
cursor.execute("INSERT INTO bank VALUES ('A', 500.0);")
cursor.execute("INSERT INTO bank VALUES ('B', 200.0);")
conn.commit()

# Transaction Function using Context Manager
def transfer_funds(sender: str, receiver: str, amount: float):
    """Executes atomic fund transfer guarded by connection context manager."""
    try:
        with conn:  # Automatically manages commit() or rollback()
            cursor.execute("UPDATE bank SET balance = balance - ? WHERE acc_id = ?;", (amount, sender))
            cursor.execute("UPDATE bank SET balance = balance + ? WHERE acc_id = ?;", (amount, receiver))
            print(f"[TRANSACTION OK] Transferred ${amount:.2f} from '{sender}' to '{receiver}'.")
    except sqlite3.Error as err:
        print(f"[TRANSACTION ROLLED BACK] Error: {err}")

# Executing Fund Transfer
transfer_funds("A", "B", 150.0)

# Fetching rows using Dictionary Row Factory key indexing
cursor.execute("SELECT acc_id, balance FROM bank;")
rows = cursor.fetchall()

print("\n=== Row Factory Dictionary-Style Inspection ===")
for r in rows:
    print(f"Account: {r['acc_id']} | Current Balance: ${r['balance']:.2f}")

conn.close()

OUTPUT

[TRANSACTION OK] Transferred $150.00 from 'A' to 'B'.

=== Row Factory Dictionary-Style Inspection ===
Account: A | Current Balance: $350.00
Account: B | Current Balance: $350.00

6. Combining Database Streams with `match-case` Pattern Matching

Building scalable database record dispatchers involves querying table rows using sqlite3.Row and passing row dictionary structures or tuple payloads into Python 3.10+ `match-case` Structural Pattern Matching engines.

MATCH_CASE_DB_DISPATCHER.PY

import sqlite3

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE orders (
    order_id TEXT PRIMARY KEY,
    amount REAL,
    status TEXT,
    flagged INT
);
""")

cursor.executemany(
    "INSERT INTO orders VALUES (?, ?, ?, ?);",
    [
        ("ORD_801", 15000.0, "COMPLETED", 0),
        ("ORD_802", 250.0, "PENDING", 0),
        ("ORD_803", 5000.0, "REJECTED", 1),
        ("ORD_804", 1200.0, "COMPLETED", 0)
    ]
)
conn.commit()

def process_database_order_stream(cursor_obj: sqlite3.Cursor):
    """
    Queries database rows and routes record payloads using match-case.
    Demonstrates evaluating database rows dynamically.
    """
    cursor_obj.execute("SELECT order_id, amount, status, flagged FROM orders;")
    rows = cursor_obj.fetchall()

    print("=== Pattern Matched Database Order Audit ===")
    for row in rows:
        row_dict = dict(row)  # Convert sqlite3.Row to standard dict for pattern matching
        
        match row_dict:
            case {"flagged": 1, "order_id": oid}:
                print(f"[AUDIT ALERT] Order #{oid} is FLAGGED for compliance audit!")

            case {"status": "COMPLETED", "amount": amt, "order_id": oid} if amt >= 10000.0:
                print(f"[HIGH VALUE] High-value completed order #{oid} (${amt:,.2f}) logged.")

            case {"status": "COMPLETED", "amount": amt, "order_id": oid}:
                print(f"[STANDARD SALE] Order #{oid} cleared (${amt:,.2f}).")

            case {"status": "PENDING", "order_id": oid}:
                print(f"[IN PROGRESS] Order #{oid} awaiting settlement.")

            case _:
                print(f"[UNHANDLED] Record: {row_dict}")

process_database_order_stream(cursor)
conn.close()

OUTPUT

=== Pattern Matched Database Order Audit ===
[HIGH VALUE] High-value completed order #ORD_801 ($15,000.00) logged.
[IN PROGRESS] Order #ORD_802 awaiting settlement.
[AUDIT ALERT] Order #ORD_803 is FLAGGED for compliance audit!
[STANDARD SALE] Order #ORD_804 cleared ($1,200.00).

7. Frequently Asked Interview Questions with Answers

Q1: What is the technical difference between an In-Memory SQLite Database and a Disk-Based Database?

Answer: An In-Memory database (created via sqlite3.connect(":memory:")) resides entirely within process system RAM, delivering lightning-fast speeds but discarding all data when the Python process exits. A Disk-Based database (e.g., sqlite3.connect("app.db")) persists table structures and rows permanently to an external disk file.

Q2: What is SQL Injection, and how do Parameterized Queries protect against it?

Answer: SQL Injection occurs when untrusted user strings are concatenated directly into SQL queries, altering command logic. Parameterized Queries use ? placeholders to pass user input parameters separately from SQL logic, ensuring the database engine treats input parameters strictly as literal data values.

Q3: Why is `sqlite3.Row` preferred over standard tuple rows in Python database queries?

Answer: Setting conn.row_factory = sqlite3.Row configures fetch results to behave like dictionary objects, allowing clean column access by attribute name (row["username"]) rather than obscure integer position indices (row[1]).

Q4: How do context managers (`with conn:`) simplify transaction management in `sqlite3`?

Answer: Wrapping database modification statements inside with conn: commits transactions automatically upon successful block exit. If an unhandled exception occurs within the block, the connection issues an automatic rollback() to revert database state to its pre-transaction state.

Q5: What is the difference between `cursor.fetchone()`, `cursor.fetchall()`, and `cursor.fetchmany(n)`?

Answer: fetchone() returns the single next matching row tuple (or None). fetchall() retrieves all remaining matching rows as a list. fetchmany(n) retrieves the next n matching rows, making it useful for chunked batch pagination.

8. Homework & Practical Assignments

Task 1: Relational Customer & Order Database Engine

Create a script named relational_db_task.py inside your lesson_53 folder:

Connect to an in-memory SQLite database and enable sqlite3.Row factory.
Create table customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT).
Create table orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL, FOREIGN KEY(customer_id) REFERENCES customers(id)).
Insert 2 customers and 3 orders using parameterized executemany().
Execute an SQL `INNER JOIN` query joining customers and orders, printing formatted order summaries using f-strings.

Task 2: Pattern-Matched Database Transaction Processor

Create a script named db_transaction_task.py:

Build function audit_account_row(row_dict: dict) -> str parsing account records.
Write a match-case dispatcher:
- case {"balance": float(b)} if b < 0.0 → Return "OVERDRAFT_ALERT".
- case {"status": "FROZEN", "account_id": aid} → Return "FROZEN_ACCOUNT_WARNING".
- case {"balance": float(b)} if b >= 10000.0 → Return "PRIME_ACCOUNT".
- case _ → Return "STANDARD_ACCOUNT".
Execute query fetches on a sample SQLite database and process records through the dispatcher.

Task 3: Master Review Capstone Project — Production Enterprise Relational Database Management OS (`relational_vault_os.py`)

Create a script named relational_vault_os.py inside your lesson_53 folder. This assignment tests and integrates **ALL concepts learned across Lessons 1 through 53**.

Project Architectural Requirements Specification:

SQLite & Relational Database Architecture (Lesson 53):
- Establish an SQLite database managed via parameterized queries, sqlite3.Row factories, primary/foreign key constraints, and with conn: transaction handling.
- Build CRUD persistence drivers replacing raw JSON stores with relational tables.
Security, Performance & Memory Hardening (Lesson 52):
- Strictly enforce parameterized queries to prevent SQL injection.
- Use __slots__ across internal domain objects for memory optimization.
- Read system database secrets dynamically from environment variables using os.environ.
Packaging & Distribution Integration (Lesson 51):
- Include a programmatically generated pyproject.toml package specification string and an argparse CLI entry point structure.
Clean Code, Documentation & Refactoring (Lesson 50):
- Apply SOLID principles, DRY, and Google Style Docstrings across all functions and classes.
Testing & Quality Assurance (Lessons 47-48):
- Include unit tests verified via pytest fixtures and unittest.mock.
Asyncio & Concurrency Integration (Lessons 45-46):
- Incorporate asynchronous execution loops and TaskGroups for parallel service audits.
Temporal & RegEx Text Processing (Lessons 43-44):
- Parse UTC-aware timestamps using datetime and zoneinfo.ZoneInfo.
- Pre-compile RegEx patterns with Named Groups for extracting database IDs from audit logs.
Type Hints, Generics & Static Safety (Lesson 42):
- Apply explicit modern type annotations (X | Y, list[str], dict[str, Any]) throughout all functions and classes.
Metaprogramming, Closures & Decorators (Lessons 40-41):
- Build function factories and 3-level configurable decorators with @functools.wraps.
Iterators & Generators (Lesson 39):
- Implement generator functions streaming database cursor records lazily with $O(1)$ memory.
Full OOP Architecture (Lessons 32-38):
- Define abstract base class BaseRelationalRepository(ABC) and concrete Data Classes DatabaseRecord and TransactionAudit.
- Build composite manager RelationalVaultOS composing database connection drivers and custom context managers.
Observability & Fault Tolerance (Lessons 29-31):
- Set up multi-handler logging to console and relational_os.log file.
- Incorporate developer assertions (assert) verifying invariant bounds.
- Define custom exception hierarchy (RelationalOSError, DatabaseTransactionError).
Context Managers & Persistence Layer (Lessons 27-30):
- Use custom class-based context managers to manage database lock handles.
- Persist relational state to relational_db.sqlite and export CSV audit reports to sql_audit.csv using pathlib.Path.
Nested Collections & Comprehensions (Lessons 21-26):
- Maintain in-memory registry dictionaries mapping IDs to Data Class Instances.
- Use List and Dictionary Comprehensions to clean and transform records.
Advanced Function Parameters & Scope (Lessons 15-17):
- Structure logic into pure modular functions with type hints, docstrings, early return Guard Clauses, and *args / **kwargs logging.
Indefinite & Definite Loops (Lessons 13-14):
- Run the interactive CLI interface inside a continuous while True menu loop.
- Iterate through output reports using for loops with enumerate() and .items().
Pattern Matching CLI Dispatcher (Lesson 12):
- Process user CLI command tokens inside a match-case block with relational match guards:
  - case ["INSERT", "RECORD", rec_id, owner, bal_str] → Execute parameterized SQL insertion.
  - case ["QUERY", "ALL"] → Stream database records lazily via generator pipeline.
  - case ["AUDIT", "SQL"] → Route database records using match-case pattern dispatcher.
  - case ["EXPORT", "CSV"] → Export relational database state to CSV.
  - case ["EXIT" | "QUIT"] → Terminate session safely using a sentinel flag.
  - case _ → Output command error message.
Conditionals, Formatting & Foundations (Lessons 1-11):
- Sanitize all inputs using .strip() and .upper().
- Format tabular reports using f-strings with field width alignment specifiers (:<15, :>10) and currency formatting (:,.2f).

9. File & Workspace Directory Structure

Standard Course Workspace Layout:

Ensure all exercise files are stored inside their corresponding lesson directories:

python_mastery_course/
│
├── lesson_01/ ... lesson_52/
│
└── lesson_53/
    ├── sqlite3_basic_workflow.py
    ├── parameterized_queries_demo.py
    ├── row_factory_transactions_demo.py
    ├── match_case_db_dispatcher.py
    ├── relational_db_task.py         <-- (Task 1)
    ├── db_transaction_task.py        <-- (Task 2)
    └── relational_vault_os.py        <-- (Task 3: Master Review Capstone)

10. What We Will Learn Next

Next Up: Lesson 54 — HTTP APIs and the requests Library

Now that you master Relational Databases and SQL persistence, we will explore communicating with external web services and REST APIs over the network!

In the next lesson, we will cover:

Understanding the HTTP Protocol: Verbs (`GET`, `POST`, `PUT`, `DELETE`), Headers, Query Parameters, and Status Codes.
Making Web Requests using the standard requests library.
Handling JSON API responses (`response.json()`).
API Authentication strategies (Bearer Tokens, API Keys, Basic Auth).
Fault Tolerance & Network Timeouts (`raise_for_status()`, retry logic).
Combining API response JSON payloads with match-case structural pattern matchers.

📝 Live Lesson Practice

HTML/CSS JavaScript Python C++ C PHP

⌨️ Practice Inputs (लाइव इनपुट भरें) (खाली होने पर RED, भरने पर GREEN underline)

💻 Code Editor (Monaco VS Code Engine)

👀 Live Preview

Security, Performance and Memory Basics HTTP APIs and the requests Library

PYTHON Tutorial

SQLite and Relational Databases

1. Introduction to Enterprise Database Persistence

2. Core Relational Database Concepts & SQL Fundamentals

1. Essential SQL Commands Matrix

3. The Standard `sqlite3` Workflow: Connections, Cursors & Tables

4. Critical Security Rule: Preventing SQL Injection via Parameterized Queries

1. The Defense Solution: Parameterized Queries (`?` Placeholders)

5. Advanced Result Handling: `sqlite3.Row` Factory & Transactions

1. Friendly Field Access via `sqlite3.Row`

2. Transaction Management and Context Managers

6. Combining Database Streams with `match-case` Pattern Matching

7. Frequently Asked Interview Questions with Answers

8. Homework & Practical Assignments

Task 1: Relational Customer & Order Database Engine

Task 2: Pattern-Matched Database Transaction Processor

Task 3: Master Review Capstone Project — Production Enterprise Relational Database Management OS (`relational_vault_os.py`)

Project Architectural Requirements Specification:

9. File & Workspace Directory Structure

Standard Course Workspace Layout:

10. What We Will Learn Next

Next Up: Lesson 54 — HTTP APIs and the requests Library

📝 Live Lesson Practice

OnlineCBT

Useful Link

Important Pages

Courses & Practice

Contact

Share