SQLite CRUD Tutorials in Python: A Step-by-Step Guide

SQLite is a lightweight and serverless relational database engine that’s perfect for small to medium-sized applications. In this tutorial, we’ll explore how to perform CRUD (Create, Read, Update, Delete) operations using SQLite in a Python application. We’ll cover each step and provide practical examples with detailed explanations to help you get started.

Prerequisites

Before we begin, make sure you have the following prerequisites:

Python: Ensure you have Python installed on your system. You can download Python from the official Python website.
SQLite: SQLite is included with Python, so there’s no need to install it separately.

Step 1: Connecting to SQLite

To use SQLite in a Python application, establish a connection to the database.

import sqlite3

# Create a connection (this will create a new SQLite database if it doesn't exist)
try:
    connection = sqlite3.connect("your_database_name.db")
    cursor = connection.cursor()

    print("Connected to SQLite")

except sqlite3.Error as e:
    print(f"Error: {e}")

Replace "your_database_name.db" with the desired database name. If the database doesn’t exist, SQLite will create it for you.

Step 2: Creating a Table

Let’s create a simple users table to demonstrate CRUD operations.

try:
    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL
    )
    """

    cursor.execute(create_table_query)
    print("Table 'users' created successfully")

except sqlite3.Error as e:
    print(f"Error: {e}")

Step 3: Inserting Data

Now, let’s insert a new user into the users table.

try:
    insert_query = "INSERT INTO users (username, email) VALUES (?, ?)"
    user_data = ("john_doe", "[email protected]")

    cursor.execute(insert_query, user_data)
    connection.commit()
    print("Data inserted successfully")

except sqlite3.Error as e:
    print(f"Error: {e}")

Step 4: Querying Data

Retrieve data from the users table.

try:
    select_query = "SELECT * FROM users"

    cursor.execute(select_query)

    for row in cursor.fetchall():
        print(f"ID: {row[0]}, Username: {row[1]}, Email: {row[2]}")

except sqlite3.Error as e:
    print(f"Error: {e}")

Step 5: Updating Data

Update a user’s email in the users table.

try:
    update_query = "UPDATE users SET email = ? WHERE username = ?"
    user_data = ("[email protected]", "john_doe")

    cursor.execute(update_query, user_data)
    connection.commit()
    print("Data updated successfully")

except sqlite3.Error as e:
    print(f"Error: {e}")

Step 6: Deleting Data

Delete a user from the users table.

try:
    delete_query = "DELETE FROM users WHERE username = ?"
    user_data = ("john_doe",)

    cursor.execute(delete_query, user_data)
    connection.commit()
    print("Data deleted successfully")

except sqlite3.Error as e:
    print(f"Error: {e}")

Step 7: Error Handling and Cleanup

Proper error handling is crucial when working with databases. Close the SQLite connection when done.

finally:
    if 'cursor' in locals():
        cursor.close()

    if 'connection' in locals():
        connection.close()
        print("Connection closed")

Conclusion

In this tutorial, we’ve covered the basics of performing CRUD operations using SQLite in a Python application. You’ve learned how to connect to SQLite, create a table, insert data, query data, update records, and delete records. SQLite is an excellent choice for lightweight applications, and these fundamental skills will serve as a solid foundation for building more complex database-driven applications with SQLite and Python.