PYTHON PROGRAMMING:Day 23: Database Operations

On Day 22 you fetched data from APIs over the internet. That data lives on someone else's server, in their database. But what about your application's data — user accounts, blog posts, shopping cart items, quiz scores? Those need a permanent home too.

A database is organized storage for your application's data. Unlike a plain text file or a Python dictionary that disappears when your program closes, a database keeps data safe, searchable, and consistent — even when thousands of users access it at the same time.

Think of a database like a digital filing cabinet: each drawer is a table, each folder is a row, and each piece of paper in the folder is a field (name, email, age). You can add folders, find specific ones, update their contents, or remove them — without shuffling through everything manually.

What You Will Learn in This Chapter

By the end of this tutorial you will be able to:

• Explain what databases are and why applications need them
• Distinguish SQL databases from NoSQL at a high level
• Connect to SQLite using Python's built-in sqlite3 module
• Create tables with SQL
• Perform CRUD operations (Create, Read, Update, Delete)
• Use parameterized queries to prevent SQL injection
• Manage database connections safely
• Avoid common database mistakes

Estimated time: 60 minutes reading + 30 minutes practice

Why Not Just Use Files?

You already know file handling from Day 14. Files work for simple storage — a config file, a log, a CSV export. But they fall short when your app grows:

For anything beyond a personal script, a database is the right tool.

Types of Databases

SQL Databases (Relational)

Data is stored in tables with rows and columns. Tables can relate to each other through keys. SQL (Structured Query Language) is the standard language for querying them.

NoSQL Databases (Non-Relational)

Flexible storage for documents, key-value pairs, or graphs. Examples: MongoDB (documents), Redis (in-memory key-value). Useful for unstructured data or extreme scale — but SQL is the better starting point for learning.

This chapter focuses on SQLite because it is built into Python, requires zero setup, and teaches SQL fundamentals that transfer directly to PostgreSQL and MySQL.

SQLite with Python — Zero Setup

SQLite stores the entire database in a single file on disk. No server to install, no passwords to configure:

import sqlite3

# Connect — creates the file if it does not exist
conn = sqlite3.connect('example.db')
print("Connected to database")
conn.close()

Output:

Connected to database

That is it. One file called example.db now exists in your project folder, ready to hold tables and data.

Creating Tables with SQL

Before inserting data, you define the structure with CREATE TABLE:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        age INTEGER,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')

conn.commit()
conn.close()
print("Table created")

Output:

Table created

Walkthrough of each column:

• id INTEGER PRIMARY KEY AUTOINCREMENT — unique ID, auto-generated (1, 2, 3, ...)
• name TEXT NOT NULL — required text field
• email TEXT UNIQUE NOT NULL — required, no duplicates allowed
• age INTEGER — optional integer
• created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP — auto-set to current time on insert

CREATE TABLE IF NOT EXISTS is safe to run multiple times — it skips creation if the table already exists.

CRUD Operations — The Four Essentials

Every database application performs these four operations. You already met CRUD on Day 21 in the web context — here is how they look in SQL.

Create — INSERT New Records

def insert_user(conn, name, email, age):
    cursor = conn.cursor()
    try:
        cursor.execute(
            "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
            (name, email, age)
        )
        conn.commit()
        return cursor.lastrowid
    except sqlite3.IntegrityError:
        return None

The ? placeholders are parameterized queries — Python fills them in safely. Never use f-strings or string concatenation for SQL values:

# DANGEROUS — SQL injection vulnerability
cursor.execute(f"INSERT INTO users (name) VALUES ('{name}')")

# SAFE — parameterized
cursor.execute("INSERT INTO users (name) VALUES (?)", (name,))

conn.commit() saves the change permanently. Without it, the insert is rolled back when the connection closes.

cursor.lastrowid returns the auto-generated ID of the row you just inserted.

Read — SELECT Data

def get_users(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    return cursor.fetchall()

def get_user_by_email(conn, email):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE email = ?", (email,))
    return cursor.fetchone()

• fetchall() — returns a list of all matching rows (each row is a tuple)
• fetchone() — returns a single row or None

users = get_users(conn)
for user in users:
    print(f"ID: {user[0]}, Name: {user[1]}, Email: {user[2]}, Age: {user[3]}")

Output:

ID: 1, Name: Priya, Email: priya@schoolabe.com, Age: 25
ID: 2, Name: Marcus, Email: marcus@schoolabe.com, Age: 30
ID: 3, Name: Sofia, Email: sofia@schoolabe.com, Age: 35

Update — MODIFY Existing Records

def update_user_age(conn, email, new_age):
    cursor = conn.cursor()
    cursor.execute(
        "UPDATE users SET age = ? WHERE email = ?",
        (new_age, email)
    )
    conn.commit()
    return cursor.rowcount

cursor.rowcount tells you how many rows were affected. If it returns 0, no record matched the email.

Delete — REMOVE Records

def delete_user(conn, email):
    cursor = conn.cursor()
    cursor.execute("DELETE FROM users WHERE email = ?", (email,))
    conn.commit()
    return cursor.rowcount

Always use a WHERE clause with DELETE. Without it, DELETE FROM users removes every row in the table.

Complete Working Example

Here is the full program tying everything together:

import sqlite3

def create_database():
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            age INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    conn.commit()
    return conn

def insert_user(conn, name, email, age):
    cursor = conn.cursor()
    try:
        cursor.execute(
            "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
            (name, email, age)
        )
        conn.commit()
        return cursor.lastrowid
    except sqlite3.IntegrityError:
        return None

def get_users(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    return cursor.fetchall()

def get_user_by_email(conn, email):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE email = ?", (email,))
    return cursor.fetchone()

def update_user_age(conn, email, new_age):
    cursor = conn.cursor()
    cursor.execute(
        "UPDATE users SET age = ? WHERE email = ?",
        (new_age, email)
    )
    conn.commit()
    return cursor.rowcount

def test_database():
    conn = create_database()

    print("Inserting users...")
    insert_user(conn, "Priya", "priya@schoolabe.com", 25)
    insert_user(conn, "Marcus", "marcus@schoolabe.com", 30)
    insert_user(conn, "Sofia", "sofia@schoolabe.com", 35)

    print("\nAll users:")
    users = get_users(conn)
    for user in users:
        print(f"ID: {user[0]}, Name: {user[1]}, Email: {user[2]}, Age: {user[3]}")

    print("\nFinding user by email:")
    user = get_user_by_email(conn, "priya@schoolabe.com")
    if user:
        print(f"Found: {user[1]} ({user[2]})")

    print("\nUpdating user age...")
    updated = update_user_age(conn, "priya@schoolabe.com", 26)
    print(f"Updated {updated} record(s)")

    conn.close()

test_database()

Output:

Inserting users...

All users:
ID: 1, Name: Priya, Email: priya@schoolabe.com, Age: 25
ID: 2, Name: Marcus, Email: marcus@schoolabe.com, Age: 30
ID: 3, Name: Sofia, Email: sofia@schoolabe.com, Age: 35

Finding user by email:
Found: Priya (priya@schoolabe.com)

Updating user age...
Updated 1 record(s)

Run this once and example.db appears in your folder. Run it again — the UNIQUE constraint on email prevents duplicate inserts (the second run's inserts silently return None).

SQL Quick Reference

Managing Connections Safely

Always close connections when you are done. Better yet, use a context manager:

import sqlite3

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM users")
    count = cursor.fetchone()[0]
    print(f"Total users: {count}")
# Connection automatically closed here

The with statement commits on success and rolls back on exception — the same pattern you learned with file handling on Day 14.

For Flask web apps (Day 21), a common pattern is to open a connection per request and close it when the response is sent. Frameworks like Django handle this automatically.

Row Factories — Nicer Output

By default, SQLite returns rows as tuples — user[1] for the name is hard to read. Enable named column access:

conn = sqlite3.connect('example.db')
conn.row_factory = sqlite3.Row

cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE email = ?", ("priya@schoolabe.com",))
user = cursor.fetchone()

print(user['name'])    # Priya
print(user['email'])   # priya@schoolabe.com

Now you access columns by name instead of index — much clearer in real projects.

Python Database Libraries Overview

The SQL syntax and Python patterns you learn with sqlite3 transfer directly to PostgreSQL and MySQL — only the connection setup changes.

Common Mistakes

Mistake 1: Forgetting conn.commit()

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Test", "test@test.com"))
# Missing commit — data is lost when connection closes!

conn.commit()   # Always commit after INSERT, UPDATE, DELETE

Mistake 2: SQL injection via string formatting

# NEVER do this — attacker can run arbitrary SQL
email = "'; DROP TABLE users; --"
cursor.execute(f"SELECT * FROM users WHERE email = '{email}'")

# ALWAYS use parameterized queries
cursor.execute("SELECT * FROM users WHERE email = ?", (email,))

Mistake 3: DELETE or UPDATE without WHERE

# Deletes EVERY user in the table
cursor.execute("DELETE FROM users")

# Deletes only one specific user
cursor.execute("DELETE FROM users WHERE email = ?", (email,))

Double-check your WHERE clause before running destructive queries.

Mistake 4: Not closing connections

Open connections consume resources. In a web app serving hundreds of requests, leaked connections can crash the server. Use with blocks or ensure conn.close() in a finally block.

Mistake 5: Ignoring IntegrityError on duplicates

# Crashes the program
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Priya", "priya@schoolabe.com"))

# Handles duplicate gracefully
try:
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Priya", "priya@schoolabe.com"))
    conn.commit()
except sqlite3.IntegrityError:
    print("Email already exists")

Practice Exercises

Exercise 1: Create a books table with columns: id, title, author, year, and price. Insert three books and print them all.

Exercise 2: Write a function find_books_by_author(conn, author) that returns all books by a given author.

Exercise 3: Update the price of a book by title. Print how many rows were affected.

Exercise 4: Write a function that safely inserts a user and returns either the new ID or a friendly error message if the email already exists.

→ See all Python practice exercises with solutions

Putting It All Together

You now have the core skills of a Python developer:

• Days 1–15: Language fundamentals — variables, data structures, functions, classes, files, exceptions
• Days 16–17: Advanced structures and algorithms
• Days 21–23: Web development, APIs, and databases

These three backend topics connect directly: a Flask route (Day 21) receives a request, calls an external API (Day 22) or reads from a database (Day 23), and returns the result as JSON or HTML. That is the architecture of most real-world Python applications.

Chapter navigation

• Previous: Day 22: Working with APIs
• Python Quiz: Take the Python quiz
• All Python exercises: Explore Python exercises