Python SQLite3

The sqlite3 module allows Python applications to create, manage, and interact with SQLite databases. SQLite databases are self-contained and stored in a single file, making them suitable for applications that require a lightweight and simple database solution.

1. Importing the sqlite3 Module

Start by importing the sqlite3 module to enable database interaction.

import sqlite3

2. Creating a Database Connection

Establish a connection to an SQLite database file. If the specified file does not exist, SQLite will create it.

# Connect to (or create) a database named 'example.db'
conn = sqlite3.connect('example.db')

# Check if the connection was successful
if conn:
    print("Connection established successfully.")
else:
    print("Connection failed.")

Output:

Connection established successfully.

Explanation: sqlite3.connect() connects to the database. The .db file is created if it does not already exist.

3. Creating a Cursor Object

The cursor object allows interaction with the database by executing SQL commands.

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

4. Creating a Table

Create a new table called users with fields for id, name, and age.

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

Output:

Table created successfully.

Explanation: The CREATE TABLE SQL command creates the table if it doesn’t exist, with id as a primary key that auto-increments.

5. Inserting Data

Insert sample data into the users table using the INSERT INTO statement.

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 30))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Charlie", 35))

# Commit changes
conn.commit()
print("Data inserted successfully.")

Output:

Data inserted successfully.

Explanation: INSERT INTO adds records to the table. Using parameterized queries (?, ?) prevents SQL injection.

6. Retrieving Data

Use the SELECT statement to retrieve all records from the users table.

# Select and retrieve data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

# Print retrieved data
print("Users table data:")
for row in rows:
    print(row)

Output:

Users table data:
(1, 'Alice', 25)
(2, 'Bob', 30)
(3, 'Charlie', 35)

Explanation: SELECT * retrieves all rows, and fetchall() collects all results into a list. Each row is represented as a tuple.

7. Updating Data

Use the UPDATE statement to modify data in the users table, such as updating a user’s age.

# Update data
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (28, "Alice"))
conn.commit()
print("Data updated successfully.")

Output:

Data updated successfully.

Explanation: UPDATE changes existing records. The query modifies Alice’s age, and conn.commit() applies the update.

8. Deleting Data

Use the DELETE FROM statement to remove records from the table, such as deleting a user.

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

Output:

Data deleted successfully.

Explanation: DELETE FROM removes records matching the condition. Here, the entry for "Bob" is deleted.

9. Using Transactions

SQLite3 supports transactions. Transactions allow grouping multiple operations that are committed together. If an error occurs, use conn.rollback() to undo changes.

try:
    # Begin a transaction
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Daisy", 22))
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Eve", 29))
    conn.commit()
    print("Transaction committed successfully.")
except sqlite3.Error as e:
    conn.rollback()
    print("Transaction failed and rolled back:", e)

Output:

Transaction committed successfully.

Explanation: Transactions help ensure atomic operations. If an error occurs, conn.rollback() undoes any partial changes.

10. Handling Errors

Errors during database operations are caught as sqlite3.Error. Handle exceptions with try-except blocks.

try:
    cursor.execute("INSERT INTO nonexistent_table (name, age) VALUES (?, ?)", ("Frank", 45))
except sqlite3.Error as e:
    print("An error occurred:", e)

Output:

An error occurred: no such table: nonexistent_table

Explanation: Attempting to access a non-existent table raises an sqlite3.Error exception, which is caught and handled.

11. Closing the Connection

Always close the connection after completing database operations to free up resources.

# Close the cursor and connection
cursor.close()
conn.close()
print("Database connection closed.")

Output:

Database connection closed.

Explanation: Closing the cursor and connection ensures that database resources are released and that no data is lost.

Summary

The sqlite3 module in Python provides an easy interface for interacting with SQLite databases, supporting common database operations like creating tables, inserting, updating, deleting, and retrieving data. It also supports transactions and error handling to ensure safe and efficient database usage.