Access SQLite Database with Python

SQLite is a lightweight, file-based database engine. It's perfect for small to medium applications. Python provides built-in support through the sqlite3 module.

This guide covers everything you need to start accessing SQLite databases in Python. We'll use simple examples and clear explanations.

Why Use SQLite with Python?

SQLite requires no server setup. Just create a file and start working. Python's sqlite3 module makes interaction easy and efficient.

Key benefits include:

• No external dependencies
• Single-file database storage
• Built-in Python support
• ACID-compliant transactions

Connecting to a SQLite Database

First, import the sqlite3 module. Then use sqlite3.connect() to establish a connection.

import sqlite3

# Connect to existing database or create new one
conn = sqlite3.connect('example.db')

# Always close the connection when done
conn.close()

This creates a file named example.db in your working directory. If it doesn't exist, SQLite creates it automatically.

Creating Tables

Use cursor() to execute SQL commands. Then call execute() with your CREATE TABLE statement.

import sqlite3

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

# Create a simple table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    )
''')

conn.commit()
conn.close()

Always use commit() to save changes. The IF NOT EXISTS clause prevents errors if the table already exists.

Inserting Data

Use parameterized queries to safely insert data. This prevents SQL injection attacks.

import sqlite3

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

# Insert single record
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))

# Insert multiple records
users = [
    ('Bob', 25),
    ('Charlie', 35),
    ('Diana', 28)
]
cursor.executemany('INSERT INTO users (name, age) VALUES (?, ?)', users)

conn.commit()
conn.close()

The executemany() method inserts multiple rows efficiently. Placeholders (?) ensure safe data handling.

Querying Data

Use execute() to run SELECT statements. Then use fetchone() or fetchall() to retrieve results.

import sqlite3

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

# Fetch all records
cursor.execute('SELECT * FROM users')
all_users = cursor.fetchall()
print("All users:")
for user in all_users:
    print(user)

# Fetch single record
cursor.execute('SELECT name, age FROM users WHERE age > ?', (28,))
older_users = cursor.fetchall()
print("\nUsers older than 28:")
for user in older_users:
    print(user)

conn.close()

All users:
(1, 'Alice', 30)
(2, 'Bob', 25)
(3, 'Charlie', 35)
(4, 'Diana', 28)

Users older than 28:
('Alice', 30)
('Charlie', 35)

Each row comes back as a tuple. The order matches your SELECT statement columns.

Updating Records

Modify existing data with UPDATE statements. Always include WHERE clauses to avoid updating all rows.

import sqlite3

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

# Update specific record
cursor.execute('UPDATE users SET age = ? WHERE name = ?', (31, 'Alice'))

# Verify the change
cursor.execute('SELECT name, age FROM users WHERE name = ?', ('Alice',))
updated_user = cursor.fetchone()
print(f"Updated user: {updated_user}")

conn.commit()
conn.close()

Updated user: ('Alice', 31)

Never forget commit() after modifications. Without it, changes won't persist.

Deleting Records

Remove records using DELETE statements. Be careful with WHERE clauses.

import sqlite3

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

# Delete specific record
cursor.execute('DELETE FROM users WHERE name = ?', ('Bob',))

# Show remaining users
cursor.execute('SELECT name FROM users')
remaining_users = cursor.fetchall()
print("Remaining users:")
for user in remaining_users:
    print(user[0])

conn.commit()
conn.close()

Remaining users:
Alice
Charlie
Diana

The rowcount attribute shows how many rows were affected by the last operation.

Using Context Managers

Context managers handle connections automatically. They ensure proper cleanup even if errors occur.

import sqlite3

# Using context manager for automatic cleanup
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

This approach is cleaner and safer. The connection closes automatically when the block exits.

Error Handling

Always wrap database operations in try-except blocks. This catches potential errors gracefully.

import sqlite3

try:
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    
    cursor.execute('SELECT * FROM nonexistent_table')
    results = cursor.fetchall()
    
except sqlite3.Error as e:
    print(f"Database error: {e}")
    
finally:
    if conn:
        conn.close()
        print("Connection closed")

Database error: no such table: nonexistent_table
Connection closed

Proper error handling prevents crashes and resource leaks.

Conclusion

Accessing SQLite databases in Python is straightforward with the sqlite3 module. Key practices include:

• Always close connections
• Use parameterized queries
• Handle errors gracefully
• Commit transactions explicitly

Start with simple operations and gradually explore advanced features. For more database operations, check our guides on Python SQLite examples and creating databases.

Remember to practice safe coding habits. Test your code thoroughly before deploying to production environments.