Python SQLite

Learning Objectives

In earlier lessons, you worked with variables, lists, dictionaries and files. Databases provide a more structured and powerful way to store application data.

What Is a Database?

A database is an organized system for storing and managing information.

Instead of keeping application data scattered across many text files, a database can organize information into structured tables.

For example, an ecommerce application might store:

• Customers
• Products
• Orders
• Payments
• Inventory

Database
│
├── customers
├── products
├── orders
└── payments

What Is SQLite?

SQLite is a lightweight relational database engine.

Unlike database systems that normally run as separate server processes, SQLite stores the database in a file.

Python Application
       ↓
   sqlite3 module
       ↓
   SQLite database
       ↓
    app.db file

SQLite supports SQL, tables, relationships, indexes, constraints and transactions.

Why Use SQLite?

SQLite is especially convenient for Python learners because Python provides built-in support through the sqlite3 module.

You generally do not need to install a separate SQLite server just to begin working with a database.

The sqlite3 Module

Python provides SQLite functionality through the built-in sqlite3 module.

import sqlite3

No pip installation is normally required.

The module provides functions and classes for connecting to SQLite databases and executing SQL statements.

Connecting to a Database

Use sqlite3.connect() to connect to a database.

import sqlite3

connection = sqlite3.connect("shop.db")

print("Database connected!")

If the database file does not exist, SQLite can create it.

You should close the connection when you are finished.

connection.close()

Connect
   ↓
Execute SQL
   ↓
Commit changes
   ↓
Close connection

Database Cursors

A cursor is used to execute SQL statements and retrieve results.

import sqlite3

connection = sqlite3.connect("shop.db")

cursor = connection.cursor()

You can then execute SQL:

cursor.execute("SELECT 1")

print(cursor.fetchone())

The cursor acts as an interface between your Python program and the database.

Creating a Table

SQL uses CREATE TABLE to create tables.

import sqlite3

connection = sqlite3.connect("shop.db")

cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER DEFAULT 0
)
""")

connection.commit()

connection.close()

The table contains four columns:

• id — unique product identifier
• name — product name
• price — product price
• stock — available quantity

Inserting Data

Use INSERT INTO to add records.

cursor.execute("""
INSERT INTO products (name, price, stock)
VALUES ('Laptop', 1200, 10)
""")

connection.commit()

You can insert multiple records efficiently using executemany().

products = [
    ("Laptop", 1200, 10),
    ("Phone", 800, 20),
    ("Tablet", 500, 15)
]

cursor.executemany("""
INSERT INTO products (name, price, stock)
VALUES (?, ?, ?)
""", products)

connection.commit()

Parameterized Queries

Never construct SQL queries by directly concatenating untrusted user input.

Use placeholders instead.

name = "Laptop"
price = 1200
stock = 10

cursor.execute("""
INSERT INTO products (name, price, stock)
VALUES (?, ?, ?)
""", (name, price, stock))

name = input("Product: ")

query = "SELECT * FROM products WHERE name = '" + name + "'"

cursor.execute(query)

Reading Data

Use SELECT to retrieve records.

cursor.execute("SELECT * FROM products")

rows = cursor.fetchall()

for row in rows:
    print(row)

A returned row is commonly represented as a tuple.

(1, 'Laptop', 1200.0, 10)

You can select specific columns:

cursor.execute(
    "SELECT name, price FROM products"
)

for row in cursor.fetchall():
    print(row)

Filtering with WHERE

The WHERE clause filters records.

cursor.execute(
    "SELECT * FROM products WHERE price > ?",
    (500,)
)

products = cursor.fetchall()

for product in products:
    print(product)

You can combine conditions:

cursor.execute("""
SELECT *
FROM products
WHERE price > ? AND stock > ?
""", (500, 5))

Sorting Results

SQL uses ORDER BY to sort records.

cursor.execute("""
SELECT name, price
FROM products
ORDER BY price ASC
""")

For descending order:

cursor.execute("""
SELECT name, price
FROM products
ORDER BY price DESC
""")

Updating Data

Use UPDATE to modify existing records.

cursor.execute("""
UPDATE products
SET price = ?
WHERE name = ?
""", (1100, "Laptop"))

connection.commit()

The WHERE condition is extremely important.

UPDATE products
SET price = 100

Deleting Data

Use DELETE to remove records.

cursor.execute("""
DELETE FROM products
WHERE name = ?
""", ("Tablet",))

connection.commit()

commit() and Transactions

Database changes are handled through transactions.

After operations such as INSERT, UPDATE and DELETE, you normally commit the changes.

connection.commit()

If something goes wrong before committing, a transaction can be rolled back.

try:

    cursor.execute("""
    UPDATE products
    SET stock = stock - 1
    WHERE id = ?
    """, (1,))

    connection.commit()

except sqlite3.Error:

    connection.rollback()

fetchone() and fetchall()

After a SELECT, you can retrieve results in different ways.

fetchone()

cursor.execute(
    "SELECT * FROM products"
)

product = cursor.fetchone()

print(product)

fetchone() returns one row at a time.

fetchall()

cursor.execute(
    "SELECT * FROM products"
)

products = cursor.fetchall()

for product in products:
    print(product)

fetchall() retrieves all remaining rows.

Working with sqlite3.Row

By default, SQLite rows behave like tuples. You can configure the connection to return sqlite3.Row objects.

import sqlite3

connection = sqlite3.connect("shop.db")

connection.row_factory = sqlite3.Row

cursor = connection.cursor()

cursor.execute(
    "SELECT * FROM products"
)

product = cursor.fetchone()

print(product["name"])
print(product["price"])

This can make database code easier to read because you can access columns by name.

Database Constraints

Constraints enforce rules on data.

PRIMARY KEY

Uniquely identifies a record.

NOT NULL

Prevents a column from containing NULL.

UNIQUE

Prevents duplicate values.

DEFAULT

Provides a default value.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    active INTEGER DEFAULT 1
)

Relationships & JOINs

Relational databases often contain multiple related tables.

customers
    │
    │ customer_id
    ↓
orders

For example:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount REAL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(id)
)

A JOIN can combine related information.

SELECT
    customers.name,
    orders.amount
FROM customers
JOIN orders
    ON customers.id = orders.customer_id

Handling Database Errors

Database operations can fail for many reasons.

import sqlite3

try:

    connection = sqlite3.connect("shop.db")

    cursor = connection.cursor()

    cursor.execute("""
    INSERT INTO products (name, price)
    VALUES (?, ?)
    """, ("Laptop", 1200))

    connection.commit()

except sqlite3.Error as error:

    print("Database error:", error)

finally:

    if connection:
        connection.close()

The sqlite3.Error family can be used to catch SQLite-related exceptions.

Database Best Practices

1. Use Parameterized Queries

Keep SQL and user-provided data separate.

2. Use Transactions

Commit related changes together when appropriate.

3. Close Connections

Release database resources when you are finished.

4. Validate Application Input

Database constraints are important, but application validation is also useful.

5. Design Tables Carefully

Think about identifiers, relationships, constraints and indexes before the application becomes large.

6. Back Up Important Data

A database file is still data that can be lost or corrupted. Important applications need an appropriate backup strategy.

Common SQLite Mistakes

1. Forgetting commit()

Changes may not be persisted as intended if the transaction is not committed.

2. Forgetting to Close Connections

Connections should be managed carefully.

3. Building SQL with String Concatenation

Use parameterized queries instead.

4. Forgetting WHERE

An UPDATE or DELETE without an appropriate condition can affect every record.

5. Ignoring Database Design

Poorly designed tables can create duplicate, inconsistent or difficult-to-query data.

6. Loading Huge Results Unnecessarily

Consider how much data you actually need before using fetchall().

Practice Exercises

Mini Challenge — Ecommerce Product Database

Build a small SQLite database for an ecommerce application.

Starter Code

import sqlite3

connection = sqlite3.connect("store.db")

cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER DEFAULT 0
)
""")

products = [
    ("Laptop", "Electronics", 1200, 10),
    ("Phone", "Electronics", 800, 15),
    ("Desk", "Furniture", 300, 5),
    ("Chair", "Furniture", 150, 12)
]

cursor.executemany("""
INSERT INTO products
(name, category, price, stock)
VALUES (?, ?, ?, ?)
""", products)

connection.commit()

Challenge Tasks

1. Display every product.
2. Find products costing more than 500.
3. Display only Electronics products.
4. Sort products from highest to lowest price.
5. Increase the laptop stock by 5.
6. Delete one product.
7. Calculate the total inventory value.

Inventory Value Formula

inventory_value = price * stock

Knowledge Check

Lesson Summary

SQLite gives Python applications a practical way to store structured data using a relational database.

You have now moved from storing data in files to working with a real relational database. In the next lesson, you will learn how Python communicates with the outside world using HTTP requests.