Python-SQL Business Data Management System

PROJECT NUMBER :1
Project Name : Business Management Database Design (Python + SQL)
SQL – Table Creation Script :
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
phone VARCHAR(15),
email VARCHAR(100)
);

CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
price DECIMAL(10,2),
stock INT
);

CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
item_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT,
product_id INT,
quantity INT,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE payments (
payment_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT,
amount DECIMAL(10,2),
payment_date DATE,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- What This Project Shows

* Proper database design
* Primary & foreign keys
* Real business-level schema
*Clean & scalable structure


I designed a normalized business database schema using SQL to manage customers, products, orders, and payments.

PROJECT NUMBER 2
Project Name :Monthly Sales Reporting System using Python & SQL

*SQL – Monthly Sales Query

SELECT
MONTH(order_date) AS month,
SUM(p.amount) AS total_sales
FROM orders o
JOIN payments p ON o.order_id = p.order_id
GROUP BY MONTH(order_date);

*Python – Generate Monthly Sales Report

import mysql.connector
import pandas as pd

conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="business_db"
)

query = """
SELECT
MONTH(order_date) AS month,
SUM(p.amount) AS total_sales
FROM orders o
JOIN payments p ON o.order_id = p.order_id
GROUP BY MONTH(order_date)
"""

df = pd.read_sql(query, conn)
df.to_csv("monthly_sales_report.csv", index=False)

print("Monthly sales report generated successfully")

conn.close()

Output:

monthly_sales_report.csv
Automated monthly sales summary
Business-ready report