Pandas with SQL Databases

import pandas as pd
import sqlite3
import numpy as np

# Create in-memory SQLite database (no server needed)
conn = sqlite3.connect('sales.db')   # Or ':memory:' for in-memory

# Create tables from DataFrames
products = pd.DataFrame({
    'product_id': [1, 2, 3, 4, 5],
    'name': ['Laptop', 'Phone', 'Monitor', 'Desk', 'Chair'],
    'category': ['Electronics', 'Electronics', 'Electronics', 'Furniture', 'Furniture'],
    'price': [1200, 500, 300, 450, 200]
})

customers = pd.DataFrame({
    'customer_id': [1, 2, 3, 4],
    'name': ['Alice', 'Bob', 'Carol', 'David'],
    'city': ['NYC', 'LA', 'Chicago', 'Houston']
})

np.random.seed(42)
n = 100
orders = pd.DataFrame({
    'order_id': range(1, n+1),
    'customer_id': np.random.randint(1, 5, n),
    'product_id': np.random.randint(1, 6, n),
    'quantity': np.random.randint(1, 10, n),
    'order_date': pd.date_range('2024-01-01', periods=n, freq='2D').astype(str)
})

# Write to SQLite
products.to_sql('products', conn, if_exists='replace', index=False)
customers.to_sql('customers', conn, if_exists='replace', index=False)
orders.to_sql('orders', conn, if_exists='replace', index=False)
print("Tables created successfully!")

# Simple query
df = pd.read_sql("SELECT * FROM products", conn)
print(df)

# Filtered query
df_electronics = pd.read_sql("""
    SELECT name, price
    FROM products
    WHERE category = 'Electronics'
    ORDER BY price DESC
""", conn)
print(df_electronics)

# Join query
df_orders = pd.read_sql("""
    SELECT
        o.order_id,
        c.name as customer_name,
        p.name as product_name,
        p.price,
        o.quantity,
        (p.price * o.quantity) as revenue,
        o.order_date
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products p ON o.product_id = p.product_id
    ORDER BY o.order_date
""", conn)
print(f"\nOrders with details ({len(df_orders)} rows):")
print(df_orders.head(10))

# MySQL connection (requires: pip install sqlalchemy mysql-connector-python)
from sqlalchemy import create_engine

# Connection string format:
# mysql+mysqlconnector://user:password@host:port/database
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/mydb')

# Read from MySQL
df = pd.read_sql("SELECT * FROM employees WHERE department = 'Engineering'", engine)

# Read with params (safe parameterized queries)
dept = 'Engineering'
df = pd.read_sql("SELECT * FROM employees WHERE department = %(dept)s",
                 engine, params={'dept': dept})

# Write DataFrame to MySQL
df_results = pd.DataFrame({
    'report_date': [pd.Timestamp.now().date()],
    'total_revenue': [df_orders['revenue'].sum()],
    'order_count': [len(df_orders)]
})
df_results.to_sql('daily_reports', engine, if_exists='append', index=False)

import pandas as pd
import sqlite3
import numpy as np

# Using our existing connection
print("=" * 55)
print("SALES DATABASE ANALYSIS")
print("=" * 55)

# 1. Revenue by product
revenue_by_product = pd.read_sql("""
    SELECT p.name as Product, p.category as Category,
           SUM(o.quantity * p.price) as Total_Revenue,
           SUM(o.quantity) as Units_Sold,
           COUNT(o.order_id) as Order_Count
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY p.product_id, p.name, p.category
    ORDER BY Total_Revenue DESC
""", conn)
print("\n1. Revenue by Product:")
print(revenue_by_product.to_string(index=False))

# 2. Customer analysis
customer_analysis = pd.read_sql("""
    SELECT c.name as Customer, c.city as City,
           COUNT(o.order_id) as Orders,
           SUM(o.quantity * p.price) as Total_Spent
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products p ON o.product_id = p.product_id
    GROUP BY c.customer_id, c.name, c.city
    ORDER BY Total_Spent DESC
""", conn)
print("\n2. Customer Spending:")
print(customer_analysis.to_string(index=False))

# 3. Category breakdown (SQL + Pandas)
category_rev = revenue_by_product.groupby('Category').agg(
    Total=('Total_Revenue', 'sum'),
    Products=('Product', 'count')
)
print("\n3. Revenue by Category:")
print(category_rev)

conn.close()