Python sqlite3 Module: Detailed Overview and Examples

The sqlite3 module in Python provides a lightweight, disk-based database that doesn’t require a separate server process. It allows you to work with SQLite databases directly from Python, making it ideal for small to medium-sized applications or for prototyping.

Importing the sqlite3 Module

To use the sqlite3 module, you need to import it:

import sqlite3

Key Functions and Methods

1. Connecting to a Database

To interact with an SQLite database, you first need to establish a connection to it. If the database file does not exist, SQLite will create it.

Example

import sqlite3

# Connect to a database (or create it if it doesn't exist)
connection = sqlite3.connect('example.db')

2. Creating a Table

Once connected, you can create tables using SQL commands.

Example

import sqlite3

# Connect to the database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Create a table
cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    email TEXT NOT NULL
)
''')

# Commit the transaction
connection.commit()

# Close the connection
connection.close()

3. Inserting Data

You can insert data into tables using SQL INSERT statements.

Example

import sqlite3

# Connect to the database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Insert data into the table
cursor.execute('''
INSERT INTO users (username, email)
VALUES (?, ?)
''', ('john_doe', 'john.doe@example.com'))

# Commit the transaction
connection.commit()

# Close the connection
connection.close()

4. Querying Data

To retrieve data from the database, use SQL SELECT statements.

Example

import sqlite3

# Connect to the database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Query data from the table
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

# Print the results
for row in rows:
    print(row)

# Close the connection
connection.close()

Output:

(1, 'john_doe', 'john.doe@example.com')

5. Updating Data

You can update existing records using SQL UPDATE statements.

Example

import sqlite3

# Connect to the database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Update data in the table
cursor.execute('''
UPDATE users
SET email = ?
WHERE username = ?
''', ('john.newemail@example.com', 'john_doe'))

# Commit the transaction
connection.commit()

# Close the connection
connection.close()

6. Deleting Data

To remove records from a table, use SQL DELETE statements.

Example

import sqlite3

# Connect to the database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Delete data from the table
cursor.execute('''
DELETE FROM users
WHERE username = ?
''', ('john_doe',))

# Commit the transaction
connection.commit()

# Close the connection
connection.close()

7. Using Transactions

SQLite3 supports transactions, allowing you to commit or roll back changes.

Example

import sqlite3

try:
    # Connect to the database
    connection = sqlite3.connect('example.db')
    cursor = connection.cursor()

    # Start a transaction
    cursor.execute('BEGIN TRANSACTION')

    # Insert data
    cursor.execute('''
    INSERT INTO users (username, email)
    VALUES (?, ?)
    ''', ('jane_doe', 'jane.doe@example.com'))

    # Commit the transaction
    connection.commit()
except sqlite3.Error as e:
    print(f"Error: {e}")
    connection.rollback()
finally:
    # Close the connection
    connection.close()

8. Using Parameterized Queries

Parameterized queries help prevent SQL injection and manage input data safely.

Example

import sqlite3

# Connect to the database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Use parameterized queries
cursor.execute('''
SELECT * FROM users WHERE username = ?
''', ('jane_doe',))

# Fetch and print the results
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close the connection
connection.close()

9. Creating and Using Indexes

Indexes improve query performance on large datasets.

Example

import sqlite3

# Connect to the database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Create an index on the username column
cursor.execute('''
CREATE INDEX idx_username
ON users (username)
''')

# Commit the transaction
connection.commit()

# Close the connection
connection.close()

10. Handling Errors

Proper error handling ensures robustness when working with databases.

Example

import sqlite3

try:
    # Connect to the database
    connection = sqlite3.connect('example.db')
    cursor = connection.cursor()

    # Perform database operations
    cursor.execute('''
    INSERT INTO users (username, email)
    VALUES (?, ?)
    ''', ('error_test', 'error_test@example.com'))

    # Commit the transaction
    connection.commit()
except sqlite3.Error as e:
    print(f"Error: {e}")
finally:
    # Close the connection
    connection.close()

Conclusion

The sqlite3 module provides a straightforward interface for interacting with SQLite databases from Python. It supports a wide range of database operations, including connecting, creating tables, inserting, querying, updating, and deleting data. By understanding these core functionalities and incorporating error handling and transactions, you can effectively manage and manipulate SQLite databases in your Python applications.