Database Basics (SQLite)

Learn database fundamentals with SQLite - Python's built-in database engine. Master CRUD operations and connection management to build data-driven applications.

About This Lesson

This lesson covers basic CRUD (Create, Read, Update, Delete) operations with SQLite. For comprehensive database knowledge including advanced SQL, database design, and production databases, check out our dedicated database courses:

Database Fundamentals: SQL basics, normalization, relationships, indexes
Advanced Databases: Optimization, transactions, stored procedures, production deployment

Why Learn SQLite?

What is SQLite?

Lightweight, file-based database
Built into Python (no installation needed)
Self-contained, serverless
Zero configuration required
Perfect for learning and small applications

When to Use SQLite

Development and testing
Small to medium applications
Mobile apps (iOS, Android)
Desktop applications
Embedded systems
Configuration storage

Bridge to Production Databases

SQLite uses standard SQL, so skills learned here transfer directly to production databases like:

PostgreSQL - Enterprise-grade, feature-rich

MySQL/MariaDB - Popular web database

SQL Server - Microsoft's database

Getting Started with SQLite

Creating and Connecting to a Database

SQLite is built into Python via the sqlite3 module. Creating a database is as simple as connecting to a file.

import sqlite3

# Connect to database (creates file if it doesn't exist)
conn = sqlite3.connect('myapp.db')

# Create a cursor to execute SQL commands
cursor = conn.cursor()

print("Database connected successfully!")
# Database connected successfully!

# Don't forget to close when done
conn.close()

In-Memory Database: Use sqlite3.connect(':memory:') for a temporary database that exists only in RAM (perfect for testing).

Understanding Cursors

A cursor is an object that allows you to execute SQL commands and retrieve results.

import sqlite3

# Create connection
conn = sqlite3.connect('example.db')

# Create cursor
cursor = conn.cursor()

# Execute SQL commands using cursor
cursor.execute("SELECT sqlite_version()")

# Fetch result
version = cursor.fetchone()
print(f"SQLite version: {version[0]}")
# SQLite version: 3.37.2

conn.close()

Creating Tables (CREATE)

Tables are the foundation of relational databases. They store data in rows and columns.

Basic CREATE TABLE Syntax

import sqlite3

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

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

# Save changes
conn.commit()

print("Table created successfully!")
# Table created successfully!

conn.close()

Common SQLite Data Types

Type	Description	Example
`INTEGER`	Whole numbers	1, 42, -100
`REAL`	Floating point numbers	3.14, -0.5
`TEXT`	Strings	"Hello", "user@example.com"
`BLOB`	Binary data	Images, files
`NULL`	No value	NULL

PRIMARY KEY AUTOINCREMENT: Automatically generates unique IDs for each row. Perfect for creating unique identifiers.

Inserting Data (CREATE in CRUD)

Single Insert

import sqlite3

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

# Insert a single record
cursor.execute('''
    INSERT INTO users (username, email, age)
    VALUES ('alice', 'alice@example.com', 30)
''')

# Commit the changes
conn.commit()

print(f"Inserted user with ID: {cursor.lastrowid}")
# Inserted user with ID: 1

conn.close()

Using Parameters (IMPORTANT!)

Never use string formatting to insert values - it's vulnerable to SQL injection. Always use parameterized queries with ? placeholders.

DANGEROUS (SQL Injection)

# DON'T DO THIS!
name = "user'; DROP TABLE users--"
cursor.execute(f"INSERT INTO users (username) VALUES ('{name}')")

SAFE (Parameterized)

# DO THIS!
name = "alice"
cursor.execute("INSERT INTO users (username) VALUES (?)", (name,))

# Safe parameterized insert
username = "bob"
email = "bob@example.com"
age = 25

cursor.execute(
    "INSERT INTO users (username, email, age) VALUES (?, ?, ?)",
    (username, email, age)
)

conn.commit()
print(f"Inserted user: {username}")
# Inserted user: bob

Multiple Inserts

# Insert multiple records at once
users_data = [
    ('charlie', 'charlie@example.com', 28),
    ('diana', 'diana@example.com', 32),
    ('eve', 'eve@example.com', 27)
]

cursor.executemany(
    "INSERT INTO users (username, email, age) VALUES (?, ?, ?)",
    users_data
)

conn.commit()
print(f"Inserted {cursor.rowcount} users")
# Inserted 3 users

Reading Data (READ in CRUD)

Basic SELECT Queries

import sqlite3

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

# Select all columns from all rows
cursor.execute("SELECT * FROM users")

# Fetch all results
users = cursor.fetchall()

for user in users:
    print(user)

# Output (example):
# (1, 'alice', 'alice@example.com', 30, '2024-01-15 10:30:00')
# (2, 'bob', 'bob@example.com', 25, '2024-01-15 10:31:00')
# (3, 'charlie', 'charlie@example.com', 28, '2024-01-15 10:32:00')

conn.close()

Fetching Results

cursor.execute("SELECT * FROM users")

# Fetch one row
user = cursor.fetchone()
print(f"First user: {user}")
# First user: (1, 'alice', 'alice@example.com', 30, '2024-01-15 10:30:00')

# Fetch next row
user = cursor.fetchone()
print(f"Second user: {user}")
# Second user: (2, 'bob', 'bob@example.com', 25, '2024-01-15 10:31:00')

cursor.execute("SELECT * FROM users")

# Fetch multiple rows at once
users = cursor.fetchmany(2)
print(f"Fetched {len(users)} users")
# Fetched 2 users

# Fetch all remaining rows
all_users = cursor.fetchall()
print(f"Remaining users: {len(all_users)}")
# Remaining users: 3

Selecting Specific Columns

# Select only specific columns
cursor.execute("SELECT username, email FROM users")

users = cursor.fetchall()
for username, email in users:
    print(f"{username}: {email}")

# Output:
# alice: alice@example.com
# bob: bob@example.com
# charlie: charlie@example.com

Filtering with WHERE

# Find users older than 26
cursor.execute("SELECT * FROM users WHERE age > ?", (26,))

users = cursor.fetchall()
print(f"Users older than 26: {len(users)}")
# Users older than 26: 4

# Find specific user by email
cursor.execute("SELECT * FROM users WHERE email = ?", ('alice@example.com',))

user = cursor.fetchone()
if user:
    print(f"Found user: {user[1]}")  # user[1] is username
    # Found user: alice
else:
    print("User not found")

Ordering Results

# Order by age (ascending)
cursor.execute("SELECT username, age FROM users ORDER BY age ASC")

for username, age in cursor.fetchall():
    print(f"{username}: {age}")

# Output:
# bob: 25
# eve: 27
# charlie: 28
# alice: 30
# diana: 32

# Order by username (descending)
cursor.execute("SELECT username FROM users ORDER BY username DESC")

for (username,) in cursor.fetchall():
    print(username)

# Output:
# eve
# diana
# charlie
# bob
# alice

Limiting Results

# Get only 3 users
cursor.execute("SELECT username FROM users LIMIT 3")

users = cursor.fetchall()
print(f"First 3 users: {users}")
# First 3 users: [('alice',), ('bob',), ('charlie',)]

# Get 2 users, skip first 2
cursor.execute("SELECT username FROM users LIMIT 2 OFFSET 2")

users = cursor.fetchall()
print(f"Users 3-4: {users}")
# Users 3-4: [('charlie',), ('diana',)]

Updating Data (UPDATE in CRUD)

Basic UPDATE

import sqlite3

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

# Update a user's age
cursor.execute(
    "UPDATE users SET age = ? WHERE username = ?",
    (31, 'alice')
)

conn.commit()

print(f"Updated {cursor.rowcount} row(s)")
# Updated 1 row(s)

conn.close()

Update Multiple Columns

# Update multiple columns at once
cursor.execute('''
    UPDATE users
    SET email = ?, age = ?
    WHERE username = ?
''', ('bob.new@example.com', 26, 'bob'))

conn.commit()

print(f"Updated {cursor.rowcount} row(s)")
# Updated 1 row(s)

# Verify the update
cursor.execute("SELECT email, age FROM users WHERE username = ?", ('bob',))
email, age = cursor.fetchone()
print(f"Bob's new email: {email}, age: {age}")
# Bob's new email: bob.new@example.com, age: 26

Warning: Always use a WHERE clause with UPDATE! Without it, you'll update ALL rows in the table.

Update Multiple Rows

# Increase age for all users older than 28
cursor.execute('''
    UPDATE users
    SET age = age + 1
    WHERE age > 28
''')

conn.commit()

print(f"Updated {cursor.rowcount} users")
# Updated 2 users (alice: 30→31, diana: 32→33)

Deleting Data (DELETE in CRUD)

Delete Specific Rows

import sqlite3

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

# Delete a specific user
cursor.execute("DELETE FROM users WHERE username = ?", ('charlie',))

conn.commit()

print(f"Deleted {cursor.rowcount} row(s)")
# Deleted 1 row(s)

conn.close()

Delete with Conditions

# Delete users younger than 26
cursor.execute("DELETE FROM users WHERE age < ?", (26,))

conn.commit()

print(f"Deleted {cursor.rowcount} users")
# Deleted 1 users

# Delete multiple users by IDs
user_ids = (1, 2, 3)
placeholders = ','.join('?' * len(user_ids))
cursor.execute(f"DELETE FROM users WHERE id IN ({placeholders})", user_ids)

conn.commit()
print(f"Deleted {cursor.rowcount} users")
# Deleted 3 users

Danger: DELETE FROM table without WHERE deletes ALL rows! Always double-check your WHERE clause.

Delete All Records (Use with Caution!)

# Delete all users (be careful!)
cursor.execute("DELETE FROM users")

conn.commit()

print(f"Deleted all {cursor.rowcount} users")
# Deleted all 5 users

# The table still exists, but is now empty
cursor.execute("SELECT COUNT(*) FROM users")
count = cursor.fetchone()[0]
print(f"Users remaining: {count}")
# Users remaining: 0

Connection Management Best Practices

Using Context Managers

Use the with statement to ensure connections are properly closed, even if errors occur.

import sqlite3
from contextlib import closing

# closing() ensures conn.close() is called automatically
# with conn: handles transactions (commit on success, rollback on error)
with closing(sqlite3.connect('users.db')) as conn:
    with conn:  # Transaction management
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM users")
        users = cursor.fetchall()

        print(f"Found {len(users)} users")
        # Found 5 users

    # conn.commit() called automatically (no exception)
# conn.close() called automatically by closing()

Commit and Rollback

import sqlite3

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

try:
    # Start transaction
    cursor.execute("INSERT INTO users (username, email, age) VALUES (?, ?, ?)",
                  ('frank', 'frank@example.com', 35))

    cursor.execute("UPDATE users SET age = ? WHERE username = ?",
                  (36, 'alice'))

    # Commit transaction (save changes)
    conn.commit()
    print("Transaction committed successfully")

except sqlite3.Error as e:
    # Rollback transaction (undo changes)
    conn.rollback()
    print(f"Error occurred, rolling back: {e}")

finally:
    conn.close()

Error Handling

import sqlite3

try:
    with sqlite3.connect('users.db') as conn:
        cursor = conn.cursor()

        # This will fail if email already exists (UNIQUE constraint)
        cursor.execute(
            "INSERT INTO users (username, email, age) VALUES (?, ?, ?)",
            ('duplicate', 'alice@example.com', 30)
        )

        conn.commit()

except sqlite3.IntegrityError as e:
    print(f"Integrity error: {e}")
    # Integrity error: UNIQUE constraint failed: users.email

except sqlite3.Error as e:
    print(f"Database error: {e}")

except Exception as e:
    print(f"Unexpected error: {e}")

Row Factories for Better Results

import sqlite3

# Access results as dictionaries instead of tuples
conn = sqlite3.connect('users.db')
conn.row_factory = sqlite3.Row

cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE username = ?", ('alice',))

user = cursor.fetchone()

# Access by column name
print(f"Username: {user['username']}")
print(f"Email: {user['email']}")
print(f"Age: {user['age']}")

# Output:
# Username: alice
# Email: alice@example.com
# Age: 31

# Can still access by index
print(f"ID: {user[0]}")
# ID: 1

conn.close()

Practical Example: TODO App Database

Let's build a complete TODO application database with all CRUD operations.

# todo_app.py
import sqlite3
from datetime import datetime

class TodoDatabase:
    def __init__(self, db_name='todo.db'):
        self.db_name = db_name
        self.init_database()

    def init_database(self):
        """Create the todos table if it doesn't exist"""
        with sqlite3.connect(self.db_name) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS todos (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    description TEXT,
                    completed BOOLEAN DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            conn.commit()

    def create_todo(self, title, description=''):
        """Create a new TODO item"""
        with sqlite3.connect(self.db_name) as conn:
            cursor = conn.cursor()
            cursor.execute(
                "INSERT INTO todos (title, description) VALUES (?, ?)",
                (title, description)
            )
            conn.commit()
            return cursor.lastrowid

    def get_all_todos(self):
        """Get all TODO items"""
        with sqlite3.connect(self.db_name) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM todos ORDER BY created_at DESC")
            return cursor.fetchall()

    def get_todo(self, todo_id):
        """Get a specific TODO item"""
        with sqlite3.connect(self.db_name) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM todos WHERE id = ?", (todo_id,))
            return cursor.fetchone()

    def update_todo(self, todo_id, title=None, description=None, completed=None):
        """Update a TODO item"""
        with sqlite3.connect(self.db_name) as conn:
            cursor = conn.cursor()

            if title is not None:
                cursor.execute("UPDATE todos SET title = ? WHERE id = ?",
                             (title, todo_id))
            if description is not None:
                cursor.execute("UPDATE todos SET description = ? WHERE id = ?",
                             (description, todo_id))
            if completed is not None:
                cursor.execute("UPDATE todos SET completed = ? WHERE id = ?",
                             (completed, todo_id))

            conn.commit()
            return cursor.rowcount > 0

    def delete_todo(self, todo_id):
        """Delete a TODO item"""
        with sqlite3.connect(self.db_name) as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM todos WHERE id = ?", (todo_id,))
            conn.commit()
            return cursor.rowcount > 0


# Usage example
if __name__ == '__main__':
    db = TodoDatabase()

    # Create TODOs
    id1 = db.create_todo("Learn SQLite", "Study database basics")
    id2 = db.create_todo("Build app", "Create a TODO application")
    id3 = db.create_todo("Write tests", "Add unit tests")

    print(f"Created TODOs with IDs: {id1}, {id2}, {id3}")

    # Read all TODOs
    print("\nAll TODOs:")
    for todo in db.get_all_todos():
        status = "✓" if todo['completed'] else "○"
        print(f"  {status} [{todo['id']}] {todo['title']}")

    # Update a TODO
    db.update_todo(id1, completed=True)
    print(f"\nMarked TODO {id1} as completed")

    # Read specific TODO
    todo = db.get_todo(id1)
    print(f"TODO {id1}: {todo['title']} - Completed: {todo['completed']}")

    # Delete a TODO
    db.delete_todo(id3)
    print(f"\nDeleted TODO {id3}")

    # List remaining TODOs
    print("\nRemaining TODOs:")
    for todo in db.get_all_todos():
        status = "✓" if todo['completed'] else "○"
        print(f"  {status} [{todo['id']}] {todo['title']}")

Expected Output:

Created TODOs with IDs: 1, 2, 3

All TODOs:
  ○ [3] Write tests
  ○ [2] Build app
  ○ [1] Learn SQLite

Marked TODO 1 as completed
TODO 1: Learn SQLite - Completed: 1

Deleted TODO 3

Remaining TODOs:
  ○ [2] Build app
  ✓ [1] Learn SQLite

Key Takeaways

SQLite is Python's built-in, zero-configuration database
CRUD operations: CREATE (INSERT), READ (SELECT), UPDATE, DELETE
Always use parameterized queries (?) to prevent SQL injection
Commit changes with conn.commit() to save to database
Use context managers (with) for automatic connection cleanup
Be careful with WHERE clauses in UPDATE and DELETE - missing WHERE affects all rows
Handle errors with try-except blocks and use rollback on failures
Row factories let you access results by column name instead of index
PRIMARY KEY AUTOINCREMENT generates unique IDs automatically
SQLite skills transfer directly to PostgreSQL, MySQL, and other SQL databases

What's Next?

Congratulations! You've completed the Python Intermediate course. You now have the skills to build professional, maintainable Python applications.

Database Fundamentals Course - Learn JOINs, relationships, normalization, and advanced queries
Advanced Databases Course - Master transactions, optimization, and production deployment
Web APIs Course - Build RESTful APIs with Flask and FastAPI
Python Advanced Course - Concurrency, async/await, metaclasses, and more

Explore our advanced courses to continue your Python journey and become a professional Python developer!

Back to "Configuration Management"

Python IntermediateLesson 17

Next: "Capstone Project"