Of course! Here is a comprehensive guide on how to create and work with an SQLite database in Python, starting from the very basics and moving to more common operations.

What is SQLite?

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. It's often called a "serverless" database because it reads and writes directly to a single disk file.

Key characteristics:

• Self-contained: No separate server process needed.
• Serverless: No configuration or administration required.
• Zero-configuration: No setup or installation needed (it's built into Python).
• Single file: The entire database is stored in one file on your disk.

This makes it perfect for small applications, local data storage, prototypes, and embedded systems.

Step 1: The Core Process (Connecting and Creating a Database)

The first step is to import the sqlite3 module and connect to a database file.

• If the file does not exist, SQLite will create it for you.
• If the file does exist, Python will connect to that existing database.

This single line of code is all you need to create or connect to a database.

import sqlite3
# The 'database.db' file will be created in the same directory as your script.
# If it already exists, it will be opened.
connection = sqlite3.connect('database.db')
print("Database connection successful.")
# Always close the connection when you're done.
connection.close()

After running this, you will see a new file named database.db in your project folder.

Step 2: Creating a Table (The CREATE TABLE Statement)

A database is useless without tables. Let's create a table to store user information.

To execute SQL commands, you use a cursor. A cursor is like a pointer that allows you to traverse over the records in the database.

import sqlite3
try:
    # Connect to the database (or create it)
    connection = sqlite3.connect('database.db')
    cursor = connection.cursor()  # Create a cursor object
    # Define the SQL command to create a table
    # We use triple quotes for multi-line strings
    sql_create_table = """
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE,
        age INTEGER
    );
    """
    # Execute the command
    cursor.execute(sql_create_table)
    # Commit the changes to the database
    connection.commit()
    print("Table 'users' created successfully.")
except sqlite3.Error as e:
    print(f"Database error: {e}")
finally:
    # Ensure the connection is closed
    if connection:
        connection.close()
        print("Database connection closed.")

Explanation of the SQL Command:

• CREATE TABLE IF NOT EXISTS users: Creates a table named users only if it doesn't already exist. This prevents errors if you run the script multiple times.
• The parentheses contain the column definitions.
• id INTEGER PRIMARY KEY: A column named id that stores integers. PRIMARY KEY means this value will uniquely identify each row and will be automatically incremented.
• name TEXT NOT NULL: A column for text (strings). NOT NULL means this field must have a value.
• email TEXT NOT NULL UNIQUE: An email column that must be unique across all rows.
• age INTEGER: An optional column for the user's age.

Step 3: Inserting Data (The INSERT Statement)

Now let's add some data to our users table.

import sqlite3
def insert_user(name, email, age):
    """Inserts a new user into the users table."""
    try:
        connection = sqlite3.connect('database.db')
        cursor = connection.cursor()
        # Use a parameterized query to prevent SQL injection
        sql_insert_query = """
        INSERT INTO users (name, email, age)
        VALUES (?, ?, ?);
        """
        # The '?' are placeholders. Pass data as a tuple.
        data_tuple = (name, email, age)
        cursor.execute(sql_insert_query, data_tuple)
        # Commit the transaction
        connection.commit()
        print(f"{cursor.rowcount} record inserted successfully into users table.")
    except sqlite3.Error as e:
        print(f"Failed to insert data into sqlite table: {e}")
    finally:
        if connection:
            connection.close()
# --- Example Usage ---
insert_user('Alice', 'alice@example.com', 30)
insert_user('Bob', 'bob@example.com', 25)

⚠️ Security Note: Parameterized Queries

Notice we used as placeholders for our data and passed the values as a tuple: (name, email, age). This is the correct and secure way to insert data. It prevents SQL injection attacks, where a malicious user could inject harmful SQL code into your application.

Step 4: Querying and Fetching Data (The SELECT Statement)

Let's retrieve all the users from our table.

import sqlite3
def get_all_users():
    """Fetches and prints all records from the users table."""
    try:
        connection = sqlite3.connect('database.db')
        cursor = connection.cursor()
        # The SQL query to select all columns and all rows
        sql_select_query = "SELECT * FROM users;"
        cursor.execute(sql_select_query)
        # fetchall() retrieves all rows from the query result
        records = cursor.fetchall()
        print("Total rows in users table: ", cursor.rowcount)
        print("Printing each row...")
        for row in records:
            print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}, Age: {row[3]}")
    except sqlite3.Error as e:
        print(f"Failed to read data from sqlite table: {e}")
    finally:
        if connection:
            connection.close()
# --- Example Usage ---
get_all_users()

Other fetch Methods:

• fetchone(): Retrieves the next single row from the query result. Useful when you expect only one result (e.g., fetching a user by ID).
• fetchmany(size): Retrieves the next set of rows from the query result (e.g., the next 5 rows).

Complete Example: A Mini CRUD Application

Here is a full script that combines all the basic operations: Create, Read, Update, and Delete.

import sqlite3
import os
# --- Database Setup ---
DB_FILE = "my_app.db"
def setup_database():
    """Creates the database and table if they don't exist."""
    if os.path.exists(DB_FILE):
        print(f"Database '{DB_FILE}' already exists.")
        return
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    cursor.execute("""
    CREATE TABLE products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        price REAL NOT NULL
    );
    """)
    conn.commit()
    conn.close()
    print(f"Database '{DB_FILE}' and 'products' table created.")
# --- CRUD Functions ---
def add_product(name, price):
    """Adds a new product to the database."""
    try:
        conn = sqlite3.connect(DB_FILE)
        cursor = conn.cursor()
        sql = "INSERT INTO products (name, price) VALUES (?, ?);"
        cursor.execute(sql, (name, price))
        conn.commit()
        print(f"Product '{name}' added successfully.")
    except sqlite3.Error as e:
        print(f"Error adding product: {e}")
    finally:
        if conn:
            conn.close()
def get_all_products():
    """Retrieves and prints all products."""
    try:
        conn = sqlite3.connect(DB_FILE)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM products;")
        products = cursor.fetchall()
        print("\n--- All Products ---")
        for product in products:
            print(f"ID: {product[0]}, Name: {product[1]}, Price: ${product[2]:.2f}")
        print("--------------------")
    except sqlite3.Error as e:
        print(f"Error fetching products: {e}")
    finally:
        if conn:
            conn.close()
def update_product_price(product_id, new_price):
    """Updates the price of a specific product."""
    try:
        conn = sqlite3.connect(DB_FILE)
        cursor = conn.cursor()
        sql = "UPDATE products SET price = ? WHERE id = ?;"
        cursor.execute(sql, (new_price, product_id))
        conn.commit()
        print(f"Price for product ID {product_id} updated to ${new_price:.2f}.")
    except sqlite3.Error as e:
        print(f"Error updating product: {e}")
    finally:
        if conn:
            conn.close()
def delete_product(product_id):
    """Deletes a product from the database."""
    try:
        conn = sqlite3.connect(DB_FILE)
        cursor = conn.cursor()
        sql = "DELETE FROM products WHERE id = ?;"
        cursor.execute(sql, (product_id,))
        conn.commit()
        print(f"Product ID {product_id} deleted successfully.")
    except sqlite3.Error as e:
        print(f"Error deleting product: {e}")
    finally:
        if conn:
            conn.close()
#