Databases and SQLite in Python

When you work with data, you often need to store it somewhere permanent. APIs return data, but it disappears when your script ends. A database lets you keep data around between program runs.

SQLite is the simplest way to get started with databases in Python. It’s built into Python’s standard library, requires no setup, and stores everything in a single file. This tutorial shows you how to create databases, run queries, and build a practical contacts manager.

What is SQLite?

SQLite is a lightweight database that lives in a regular file on your disk. Unlike PostgreSQL or MySQL, there’s no server process to run. The database is just a file you can copy, email, or back up like any other.

Use SQLite when:

• You need simple local storage for a small to medium application
• You want zero configuration - just import and use
• You’re building a prototype before switching to a larger database
• You need a single-file database that’s easy to share

Don’t use SQLite when:

• You need multiple simultaneous write connections (it locks the file)
• You’re building a high-traffic web application (consider PostgreSQL)
• You need advanced database features like stored procedures

Creating a Database and Table

The sqlite3 module ships with Python, so you don’t need to install anything. Connect to a database using sqlite3.connect() - this creates the file if it doesn’t exist:

import sqlite3

# Creates tutorial.db if it doesn't exist
conn = sqlite3.connect("tutorial.db")
cursor = conn.cursor()

# Create a table to store contacts
cursor.execute("""
    CREATE TABLE IF NOT EXISTS contacts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        phone TEXT
    )
""")

conn.commit()
print("Database and table created successfully")

The PRIMARY KEY AUTOINCREMENT gives each contact a unique ID automatically. The UNIQUE constraint on email prevents duplicate entries.

Inserting Data

Now add some contacts using INSERT statements. Always use parameterized queries with ? placeholders instead of string formatting - this prevents SQL injection attacks:

# Insert a single contact
cursor.execute("""
    INSERT INTO contacts (name, email, phone)
    VALUES (?, ?, ?)
""", ("Alice Johnson", "alice@example.com", "555-1234"))

# Insert multiple contacts at once
contacts = [
    ("Bob Smith", "bob@example.com", "555-5678"),
    ("Carol White", "carol@example.com", "555-9012"),
    ("David Brown", "david@example.com", "555-3456"),
]

cursor.executemany("""
    INSERT INTO contacts (name, email, phone)
    VALUES (?, ?, ?)
""", contacts)

conn.commit()
print("Contacts inserted")

The ? placeholders get replaced with the values in the tuple. If any contact violates a constraint (like duplicate email), SQLite raises an error and none of the inserts proceed.

Reading Data

Fetch data with SELECT statements. Use fetchone() to get a single row, fetchall() for all rows, or iterate directly over the cursor:

# Get all contacts
cursor.execute("SELECT * FROM contacts")
all_contacts = cursor.fetchall()

print("All contacts:")
for contact in all_contacts:
    print(f"  {contact}")

# Get contacts matching a condition
cursor.execute("""
    SELECT name, email FROM contacts
    WHERE name LIKE ? 
    ORDER BY name
""", ("A%",))

print("\nContacts starting with A:")
for name, email in cursor:
    print(f"  {name}: {email}")

The query returns tuples. In this case, each contact is (id, name, email, phone).

Updating and Deleting Data

Modify existing data with UPDATE and remove data with DELETE:

# Update a contact's phone number
cursor.execute("""
    UPDATE contacts
    SET phone = ?
    WHERE email = ?
""", ("555-0000", "alice@example.com"))

# Delete a contact
cursor.execute("""
    DELETE FROM contacts
    WHERE name = ?
""", ("Bob Smith",))

conn.commit()
print("Changes saved")

Always include a WHERE clause with UPDATE and DELETE. Without it, you’d modify or delete every row in the table.

Closing the Connection

When you’re done, close the connection to flush any pending changes and release resources:

conn.close()

A better approach uses a context manager that closes automatically:

with sqlite3.connect("tutorial.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM contacts")
    count = cursor.fetchone()[0]
    print(f"Total contacts: {count}")
# Connection auto-closes when the with block exits

Practical Example: Contacts Manager

Here’s a complete contacts manager demonstrating everything you’ve learned:

import sqlite3

def init_db():
    """Create the database and table if they don't exist."""
    with sqlite3.connect("contacts.db") as conn:
        cursor = conn.cursor()
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS contacts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE,
                phone TEXT
            )
        """)

def add_contact(name, email, phone):
    """Add a new contact."""
    try:
        with sqlite3.connect("contacts.db") as conn:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO contacts (name, email, phone)
                VALUES (?, ?, ?)
            """, (name, email, phone))
            conn.commit()
            print(f"Added {name}")
    except sqlite3.IntegrityError:
        print(f"Error: {email} already exists")

def get_all_contacts():
    """Retrieve all contacts."""
    with sqlite3.connect("contacts.db") as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT name, email, phone FROM contacts ORDER BY name")
        return cursor.fetchall()

def search_contacts(query):
    """Search contacts by name or email."""
    with sqlite3.connect("contacts.db") as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT name, email, phone FROM contacts
            WHERE name LIKE ? OR email LIKE ?
            ORDER BY name
        """, (f"%{query}%", f"%{query}%"))
        return cursor.fetchall()

def delete_contact(email):
    """Delete a contact by email."""
    with sqlite3.connect("contacts.db") as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM contacts WHERE email = ?", (email,))
        if cursor.rowcount > 0:
            print(f"Deleted {email}")
        else:
            print(f"No contact found with {email}")

# Demo usage
if __name__ == "__main__":
    init_db()
    
    # Add some contacts
    add_contact("Alice Johnson", "alice@example.com", "555-1234")
    add_contact("Bob Smith", "bob@example.com", "555-5678")
    add_contact("Carol White", "carol@example.com", "555-9012")
    
    # List all contacts
    print("\nAll contacts:")
    for name, email, phone in get_all_contacts():
        print(f"  {name} - {email} - {phone}")
    
    # Search
    print("\nSearching for 'alice':")
    for name, email, phone in search_contacts("alice"):
        print(f"  {name} - {email}")
    
    # Delete
    delete_contact("bob@example.com")

Run this script and you’ll have a working contacts database that persists between runs.

Common Gotchas

A few things that trip up beginners:

Forgetting to commit: Changes don’t persist until you call conn.commit(). In a context manager, commit happens automatically on exit.

SQL injection: Never use f-strings or % formatting in SQL queries. Always use ? placeholders.

Connection threading: SQLite connections shouldn’t be shared across threads. Each thread should create its own connection.

Case sensitivity: SQLite TEXT comparisons are case-sensitive by default. Use COLLATE NOCASE for case-insensitive searches.

Next Steps

You’ve learned the fundamentals of SQLite in Python. From here, you can explore:

• Using transactions for atomic operations
• Creating indexes to speed up queries
• Integrating with ORMs like SQLAlchemy
• Connecting to PostgreSQL or MySQL when you outgrow SQLite

The next tutorial in this series covers working with JSON data, another common format for storing and exchanging data in Python.