Python SQLite Example: Simple Database Operations

Python SQLite is a powerful built-in database module. It allows developers to work with databases without external dependencies. This article provides clear examples for beginners.

What Is Python SQLite?

SQLite is a lightweight, file-based database engine. Python includes the sqlite3 module by default. No installation is required for basic usage.

Before diving into examples, ensure Python is installed. Refer to our Python SQLite Install: Complete Setup Guide for detailed setup instructions.

Creating a Database Connection

Use the connect() function to create or open a database file. If the file doesn't exist, Python creates it automatically.

import sqlite3

# Create a connection to the database
connection = sqlite3.connect("example.db")
print("Database created successfully")

Database created successfully

The connect() function returns a connection object. This object manages interactions with the database.

Creating Tables

After connecting, create tables using SQL commands. The cursor() method executes SQL statements.

import sqlite3

connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Create a table named 'users'
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
)
''')
print("Table created successfully")

Table created successfully

The cursor.execute() method runs SQL queries. Always use IF NOT EXISTS to avoid errors when the table already exists.

Inserting Data

Insert data using the execute() method with parameterized queries. This prevents SQL injection attacks.

import sqlite3

connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Insert a 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)

connection.commit()
print("Data inserted successfully")

Data inserted successfully

The executemany() method inserts multiple rows efficiently. Always call commit() to save changes permanently.

Querying Data

Retrieve data using the fetchall() method. It returns all rows as a list of tuples.

import sqlite3

connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Query all records
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

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

The fetchall() method returns all matching rows. For large datasets, consider using fetchone() to process rows one at a time.

Filtering Results

Use the WHERE clause to filter results. Combine it with parameterized queries for safety.

import sqlite3

connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Query users older than 30
cursor.execute("SELECT name, age FROM users WHERE age > ?", (30,))
results = cursor.fetchall()

for result in results:
    print(f"{result[0]} is {result[1]} years old")

Alice is 30 years old
Charlie is 35 years old

Updating Records

Modify existing data using the UPDATE statement. Always include a WHERE clause to prevent unintended changes.

import sqlite3

connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Update Alice's age
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, "Alice"))
connection.commit()
print("Record updated successfully")

# Verify the update
cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))
print(cursor.fetchone())

Record updated successfully
(1, 'Alice', 31)

Deleting Records

Remove records using the DELETE statement. Again, use parameterized queries and a WHERE clause.

import sqlite3

connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Delete Bob
cursor.execute("DELETE FROM users WHERE name = ?", ("Bob",))
connection.commit()
print("Record deleted successfully")

# Check remaining records
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

Record deleted successfully
[(1, 'Alice', 31), (3, 'Charlie', 35), (4, 'Diana', 28)]

Handling Errors

Wrap database operations in try-except blocks. This ensures graceful error handling.

import sqlite3

try:
    connection = sqlite3.connect("example.db")
    cursor = connection.cursor()
    
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
    print(rows)
except sqlite3.Error as error:
    print(f"Database error: {error}")
finally:
    if connection:
        connection.close()
        print("Connection closed")

[(1, 'Alice', 31), (3, 'Charlie', 35), (4, 'Diana', 28)]
Connection closed

The close() method releases database resources. Always close connections in the finally block.

Using Context Managers

Python supports context managers for automatic resource management. This simplifies connection handling.

import sqlite3

with sqlite3.connect("example.db") as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT COUNT(*) FROM users")
    count = cursor.fetchone()[0]
    print(f"Total users: {count}")

Total users: 3

Context managers automatically commit or rollback transactions. They also close connections when exiting the block.

Advanced Example: Full CRUD Application

Combine all operations into a complete application. This example demonstrates a simple user management system.

import sqlite3

class UserManager:
    def __init__(self, db_name):
        self.db_name = db_name
    
    def create_table(self):
        with sqlite3.connect(self.db_name) as conn:
            conn.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    email TEXT UNIQUE
                )
            ''')
    
    def add_user(self, name, email):
        with sqlite3.connect(self.db_name) as conn:
            try:
                conn.execute("INSERT INTO users (name, email) VALUES (?, ?)", (name, email))
                print(f"User {name} added successfully")
            except sqlite3.IntegrityError:
                print(f"Email {email} already exists")
    
    def get_users(self):
        with sqlite3.connect(self.db_name) as conn:
            cursor = conn.execute("SELECT * FROM users")
            return cursor.fetchall()

# Usage
manager = UserManager("users.db")
manager.create_table()
manager.add_user("Alice", "[email protected]")
manager.add_user("Bob", "[email protected]")
print(manager.get_users())

User Alice added successfully
User Bob added successfully
[(1, 'Alice', '[email protected]'), (2, 'Bob', '[email protected]')]

Best Practices

Follow these guidelines for robust SQLite applications:

• Always use parameterized queries to prevent SQL injection
• Close connections properly using context managers
• Handle exceptions gracefully with try-except blocks
• Use IF NOT EXISTS when creating tables
• Call commit() after write operations

Conclusion

Python SQLite provides a simple yet powerful way to manage databases. The sqlite3 module offers all necessary functions for basic to advanced operations. By following best practices like parameterized queries and proper error handling, you can build reliable database applications.

For more advanced topics, explore our Python SQLite API Guide: Build Database Apps and Python SQLite3 paramstyle Guide: Query Parameters.