The Results.....¶

# app.py (using Flask for API)

from flask import Flask, request, jsonify
import sqlite3

app = Flask(__name__)
DATABASE = 'products.db'  # SQLite database file

def get_db_connection():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row  # Return dictionaries for rows
    return conn

def create_product(product_data):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO products (name, category, description) VALUES (?, ?, ?)",
                       (product_data['name'], product_data['category'], product_data['description']))
        conn.commit()
        product_id = cursor.lastrowid  # Get the ID of the newly inserted product
        return jsonify({"id": product_id, "message": "Product created successfully"}), 201  # 201 Created
    except Exception as e:
        conn.rollback()  # Rollback on error
        return jsonify({"error": str(e)}), 500  # 500 Internal Server Error
    finally:
        conn.close()

def get_product(product_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products WHERE id = ?", (product_id,))
    product = cursor.fetchone()
    conn.close()
    if product:
        return jsonify(dict(product)), 200
    return jsonify({"message": "Product not found"}), 404  # 404 Not Found

def update_product(product_id, product_data):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("UPDATE products SET name = ?, category = ?, description = ? WHERE id = ?",
                       (product_data['name'], product_data['category'], product_data['description'], product_id))
        conn.commit()
        if cursor.rowcount > 0:
            return jsonify({"message": "Product updated successfully"}), 200
        return jsonify({"message": "Product not found for update"}), 404
    except Exception as e:
        conn.rollback()
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()


def delete_product(product_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM products WHERE id = ?", (product_id,))
        conn.commit()
        if cursor.rowcount > 0:
            return jsonify({"message": "Product deleted successfully"}), 200
        return jsonify({"message": "Product not found for deletion"}), 404
    except Exception as e:
        conn.rollback()
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()

# API Endpoints
@app.route('/products', methods=['POST'])
def create_product_route():
    product_data = request.get_json()
    return create_product(product_data)

@app.route('/products/<int:product_id>', methods=['GET'])
def get_product_route(product_id):
    return get_product(product_id)

@app.route('/products/<int:product_id>', methods=['PUT'])
def update_product_route(product_id):
    product_data = request.get_json()
    return update_product(product_id, product_data)

@app.route('/products/<int:product_id>', methods=['DELETE'])
def delete_product_route(product_id):
    return delete_product(product_id)

if __name__ == '__main__':
    # Create the database table if it doesn't exist
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            category TEXT,
            description TEXT
        )
    """)
    conn.commit()
    conn.close()

    app.run(debug=True)  # Set debug=False in production

-- schema.sql (SQL DDL for SQLite)

CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT,
    description TEXT
);