Python SQLite

SQLite is one of the most widely used embedded database engines in the world. It's lightweight, serverless, and perfectly suited for many Python applications. In this tutorial, you'll learn how to work with SQLite databases using Python's built-in sqlite3 module.

Introduction to SQLite

SQLite is a C library that implements a small, fast, self-contained SQL database engine. It's a popular choice for applications that need a database without the overhead of a separate database server. Some key features of SQLite include:

Serverless: No separate server process is needed
Zero-configuration: No setup or administration necessary
Cross-platform: Works on virtually all computing platforms
Self-contained: A single file contains the entire database
Small footprint: The entire library is typically less than 600KB

Python provides built-in support for SQLite through the sqlite3 module, making it incredibly easy to create and work with SQLite databases.

Setting Up SQLite in Python

The sqlite3 module comes pre-installed with Python, so you don't need to install anything additional to get started.

Let's begin by importing the module:

python
import sqlite3

Connecting to a Database

To work with an SQLite database, first, you need to create a connection object:

python
# Connect to a database (creates it if it doesn't exist)
conn = sqlite3.connect('example.db')

print("Database connection established successfully!")

Output:

Database connection established successfully!

A few important things to note:

If the specified database file doesn't exist, SQLite will create it automatically
You can use :memory: as the database name to create a temporary database in RAM

Creating a Cursor

Once connected, you need a cursor object to execute SQL commands:

python
# Create a cursor object
cursor = conn.cursor()

Creating Tables

Let's create a simple table to store user information:

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

# Commit the changes
conn.commit()
print("Table created successfully!")

Output:

Table created successfully!

Inserting Data

Now let's add some data to our table:

Single Row Insertion

python
# Insert a single record
cursor.execute('''
INSERT INTO users (name, email, age) 
VALUES (?, ?, ?)
''', ('John Doe', '[email protected]', 28))

# Commit the changes
conn.commit()
print("Record inserted successfully!")

Output:

Record inserted successfully!

The ? placeholders are used for parameter substitution, which helps prevent SQL injection attacks.

Multiple Row Insertion

To insert multiple rows at once, you can use executemany():

python
# Multiple records to insert
users_data = [
    ('Alice Smith', '[email protected]', 24),
    ('Bob Johnson', '[email protected]', 32),
    ('Charlie Brown', '[email protected]', 45)
]

# Insert multiple records
cursor.executemany('INSERT INTO users (name, email, age) VALUES (?, ?, ?)', users_data)

# Commit the changes
conn.commit()
print(f"{cursor.rowcount} records inserted successfully!")

Output:

3 records inserted successfully!

Querying Data

Now that we have data in our database, let's retrieve it:

Fetching All Records

python
# Query all records
cursor.execute('SELECT * FROM users')
all_users = cursor.fetchall()

print("All users:")
for user in all_users:
    print(f"ID: {user[0]}, Name: {user[1]}, Email: {user[2]}, Age: {user[3]}, Created: {user[4]}")

Output:

All users:
ID: 1, Name: John Doe, Email: [email protected], Age: 28, Created: 2023-07-20 15:32:45
ID: 2, Name: Alice Smith, Email: [email protected], Age: 24, Created: 2023-07-20 15:32:46
ID: 3, Name: Bob Johnson, Email: [email protected], Age: 32, Created: 2023-07-20 15:32:46
ID: 4, Name: Charlie Brown, Email: [email protected], Age: 45, Created: 2023-07-20 15:32:46

Fetching a Single Record

python
# Query a single record
cursor.execute('SELECT * FROM users WHERE id = ?', (1,))
user = cursor.fetchone()

if user:
    print(f"\nUser with ID 1:")
    print(f"Name: {user[1]}")
    print(f"Email: {user[2]}")
    print(f"Age: {user[3]}")
else:
    print("User not found")

Output:

User with ID 1:
Name: John Doe
Email: [email protected]
Age: 28

Working with Query Results

SQLite returns query results as tuples by default. If you'd prefer to work with dictionaries, you can use the following approach:

python
# Get column names
cursor.execute('SELECT * FROM users LIMIT 1')
column_names = [description[0] for description in cursor.description]

# Query all users
cursor.execute('SELECT * FROM users')
users = cursor.fetchall()

# Convert to list of dictionaries
users_list = []
for user in users:
    user_dict = {column_names[i]: user[i] for i in range(len(column_names))}
    users_list.append(user_dict)

print("\nUsers as dictionaries:")
for user in users_list:
    print(user)

Output:

Users as dictionaries:
{'id': 1, 'name': 'John Doe', 'email': '[email protected]', 'age': 28, 'created_at': '2023-07-20 15:32:45'}
{'id': 2, 'name': 'Alice Smith', 'email': '[email protected]', 'age': 24, 'created_at': '2023-07-20 15:32:46'}
{'id': 3, 'name': 'Bob Johnson', 'email': '[email protected]', 'age': 32, 'created_at': '2023-07-20 15:32:46'}
{'id': 4, 'name': 'Charlie Brown', 'email': '[email protected]', 'age': 45, 'created_at': '2023-07-20 15:32:46'}

Updating Data

To modify existing records:

python
# Update a record
cursor.execute('''
UPDATE users 
SET age = ? 
WHERE name = ?
''', (29, 'John Doe'))

conn.commit()
print(f"{cursor.rowcount} record(s) updated")

# Verify the update
cursor.execute('SELECT name, age FROM users WHERE name = ?', ('John Doe',))
updated_user = cursor.fetchone()
print(f"Updated user: {updated_user[0]} is now {updated_user[1]} years old")

Output:

1 record(s) updated
Updated user: John Doe is now 29 years old

Deleting Data

To remove records from a database:

python
# Delete a record
cursor.execute('DELETE FROM users WHERE name = ?', ('Charlie Brown',))
conn.commit()
print(f"{cursor.rowcount} record(s) deleted")

# Verify deletion
cursor.execute('SELECT COUNT(*) FROM users')
count = cursor.fetchone()[0]
print(f"Remaining users: {count}")

Output:

1 record(s) deleted
Remaining users: 3

Using Transactions

Transactions allow you to group multiple operations and commit or rollback them as a single unit:

python
try:
    # Begin transaction
    conn.execute('BEGIN TRANSACTION')
    
    # Perform multiple operations
    cursor.execute('INSERT INTO users (name, email, age) VALUES (?, ?, ?)', 
                  ('David Parker', '[email protected]', 38))
    cursor.execute('UPDATE users SET age = age + 1 WHERE name = ?', ('Alice Smith',))
    
    # Commit if all operations are successful
    conn.commit()
    print("Transaction committed successfully!")
    
except sqlite3.Error as e:
    # Rollback in case of error
    conn.rollback()
    print(f"Transaction failed: {e}")

Output:

Transaction committed successfully!

Real-World Example: A Simple Task Manager

Let's create a practical example of a task manager application using SQLite:

python
import sqlite3
from datetime import datetime

def setup_database():
    conn = sqlite3.connect('task_manager.db')
    cursor = conn.cursor()
    
    # Create tasks table
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT,
        status TEXT DEFAULT 'pending',
        due_date TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    conn.commit()
    return conn, cursor

def add_task(cursor, title, description=None, due_date=None):
    cursor.execute('''
    INSERT INTO tasks (title, description, due_date)
    VALUES (?, ?, ?)
    ''', (title, description, due_date))
    return cursor.lastrowid

def get_all_tasks(cursor):
    cursor.execute('SELECT * FROM tasks ORDER BY due_date')
    return cursor.fetchall()

def update_task_status(cursor, task_id, status):
    cursor.execute('''
    UPDATE tasks
    SET status = ?
    WHERE id = ?
    ''', (status, task_id))
    return cursor.rowcount

def delete_task(cursor, task_id):
    cursor.execute('DELETE FROM tasks WHERE id = ?', (task_id,))
    return cursor.rowcount

def search_tasks(cursor, keyword):
    cursor.execute('''
    SELECT * FROM tasks 
    WHERE title LIKE ? OR description LIKE ?
    ''', (f'%{keyword}%', f'%{keyword}%'))
    return cursor.fetchall()

def main():
    conn, cursor = setup_database()
    
    # Add some sample tasks
    add_task(cursor, "Complete Python SQLite tutorial", 
             "Learn how to use SQLite with Python", "2023-07-25")
    add_task(cursor, "Buy groceries", 
             "Milk, eggs, bread, and vegetables", "2023-07-22")
    add_task(cursor, "Call mom", 
             "Don't forget to wish her happy birthday", "2023-07-28")
    conn.commit()
    
    # Display all tasks
    print("\nAll Tasks:")
    tasks = get_all_tasks(cursor)
    for task in tasks:
        print(f"ID: {task[0]} | Title: {task[1]} | Status: {task[3]} | Due: {task[4]}")
    
    # Update a task status
    update_task_status(cursor, 2, "completed")
    conn.commit()
    
    # Search for tasks
    print("\nSearch Results for 'tutorial':")
    results = search_tasks(cursor, "tutorial")
    for task in results:
        print(f"ID: {task[0]} | Title: {task[1]} | Status: {task[3]}")
    
    # Clean up
    conn.close()

if __name__ == "__main__":
    main()

Output:

All Tasks:
ID: 2 | Title: Buy groceries | Status: pending | Due: 2023-07-22
ID: 1 | Title: Complete Python SQLite tutorial | Status: pending | Due: 2023-07-25
ID: 3 | Title: Call mom | Status: pending | Due: 2023-07-28

Search Results for 'tutorial':
ID: 1 | Title: Complete Python SQLite tutorial | Status: pending

Closing the Connection

Always remember to close the database connection when you're done:

python
conn.close()
print("Connection closed")

Output:

Connection closed

Advanced SQLite Features in Python

Using SQLite Data Types

SQLite has a dynamic type system that's different from most SQL databases. It supports these storage classes:

NULL
INTEGER
REAL
TEXT
BLOB

However, you can store any data type in any column regardless of the declared type.

Using Row Factory

To make working with query results easier, you can use the Row factory:

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

cursor = conn.cursor()
cursor.execute('SELECT * FROM users')

for row in cursor.fetchall():
    print(f"Name: {row['name']}, Email: {row['email']}")

Output:

Name: John Doe, Email: [email protected]
Name: Alice Smith, Email: [email protected]
Name: Bob Johnson, Email: [email protected]
Name: David Parker, Email: [email protected]

Using Context Managers

Python's context managers can help ensure connections are properly closed:

python
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 when the block exits

print("Connection is closed automatically")

Output:

Total users: 4
Connection is closed automatically

Summary

In this tutorial, you've learned how to:

Create and connect to SQLite databases using Python's sqlite3 module
Create tables and define schema
Insert, update, query, and delete data
Use transactions for data integrity
Implement a practical task manager application
Utilize advanced SQLite features like row factories and context managers

SQLite provides a powerful yet simple way to add persistent data storage to your Python applications without the complexity of a full-fledged database server. It's perfect for small to medium applications, prototypes, and situations where simplicity and convenience are important.

Additional Resources and Exercises

Resources

Exercises

Build a Contact Manager: Create a simple contact management system that stores names, phone numbers, email addresses, and birthdays.
Implement a Blog Database: Design a database for a blog with tables for posts, comments, and users.
Create a Product Inventory System: Build a system that tracks products, their quantities, prices, and categories.
Add Error Handling: Enhance the task manager example by adding robust error handling for all database operations.
Database Browser: Create a simple command-line tool that lets users explore the structure and content of an SQLite database file.

Remember that the best way to learn is by doing. Try these exercises and modify them to explore different aspects of SQLite in Python.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction to SQLite​

Setting Up SQLite in Python​

Connecting to a Database​

Creating a Cursor​

Creating Tables​

Inserting Data​

Single Row Insertion​

Multiple Row Insertion​

Querying Data​

Fetching All Records​

Fetching a Single Record​

Working with Query Results​

Updating Data​

Deleting Data​

Using Transactions​

Real-World Example: A Simple Task Manager​

Closing the Connection​

Advanced SQLite Features in Python​

Using SQLite Data Types​

Using Row Factory​

Using Context Managers​

Summary​

Additional Resources and Exercises​

Resources​

Exercises​

Introduction to SQLite

Setting Up SQLite in Python

Connecting to a Database

Creating a Cursor

Creating Tables

Inserting Data

Single Row Insertion

Multiple Row Insertion

Querying Data

Fetching All Records

Fetching a Single Record

Working with Query Results

Updating Data

Deleting Data

Using Transactions

Real-World Example: A Simple Task Manager

Closing the Connection

Advanced SQLite Features in Python

Using SQLite Data Types

Using Row Factory

Using Context Managers

Summary

Additional Resources and Exercises

Resources

Exercises