Skip to content

Instantly share code, notes, and snippets.

@ahmedsamirsaid

ahmedsamirsaid/create_schema.sql

Last active August 10, 2024 10:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ahmedsamirsaid/a65d8ec2cba332c90527dd678c7d33b3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ahmedsamirsaid/a65d8ec2cba332c90527dd678c7d33b3 to your computer and use it in GitHub Desktop.
Download ZIP
Inventory management system
Raw
create_schema.sql
create database InventoryManagement;
use InventoryManagement;
CREATE TABLE Employee (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Position VARCHAR(100),
DoB DATE,
email VARCHAR(255) UNIQUE, -- Unique index on email
phone VARCHAR(20),
smp_id INT,
FOREIGN KEY (smp_id) REFERENCES Employee(emp_id)
on delete set null
on update set null
);
-- Location Table
CREATE TABLE Location (
loc_id INT AUTO_INCREMENT PRIMARY KEY,
address VARCHAR(255),
city VARCHAR(100),
country VARCHAR(100)
);
-- Warehouse Table
CREATE TABLE Warehouse (
wh_id INT AUTO_INCREMENT PRIMARY KEY,
loc_id INT,
Capacity INT,
FOREIGN KEY (loc_id) REFERENCES Location(loc_id)
on delete cascade
on update cascade
);
ALTER TABLE Employee
ADD COLUMN wh_id INT,
ADD FOREIGN KEY (wh_id) REFERENCES Warehouse(wh_id)
on delete cascade
on update cascade
;
-- Supplier Table
CREATE TABLE Supplier (
supp_id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255),
address VARCHAR(255),
phone VARCHAR(20)
);
-- Customer Table
CREATE TABLE Customer (
cust_id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255),
address VARCHAR(255),
phone VARCHAR(20),
email VARCHAR(255) UNIQUE, -- Unique index on email
DoB DATE
);
-- Category Table
CREATE TABLE Category (
cat_id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255),
Description TEXT
);
-- Sub_Category Table
CREATE TABLE Sub_Category (
sub_cat_id INT AUTO_INCREMENT PRIMARY KEY,
cat_id INT,
Name VARCHAR(255),
Description TEXT,
FOREIGN KEY (cat_id) REFERENCES Category(cat_id)
on delete cascade
on update cascade
);
-- Product Table
CREATE TABLE Product (
pro_id INT AUTO_INCREMENT PRIMARY KEY,
sub_cat_id INT,
Name VARCHAR(255),
price DECIMAL(10, 2),
cost_price DECIMAL(10, 2),
FOREIGN KEY (sub_cat_id) REFERENCES Sub_Category(sub_cat_id)
on delete cascade
on update cascade
);
-- Inventory Table
CREATE TABLE Inventory (
inv_id INT AUTO_INCREMENT PRIMARY KEY,
pro_id INT,
Quantity INT,
Reorder_point INT,
wh_id INT,
initial_quantity INT, -- New column for initial inventory level
final_quantity INT, -- New column for final inventory level
FOREIGN KEY (pro_id) REFERENCES Product(pro_id)
on delete set null
on update cascade
,
FOREIGN KEY (wh_id) REFERENCES Warehouse(wh_id)
on delete cascade
on update cascade
);
-- Order Table
CREATE TABLE `Order` (
order_id INT AUTO_INCREMENT PRIMARY KEY,
cust_id INT,
order_date DATETIME,
amount DECIMAL(10, 2),
status VARCHAR(50),
quantity INT,
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)
on delete set null
on update cascade
);
-- Order_Details Table
CREATE TABLE Order_Details (
order_details_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
pro_id INT,
quantity INT,
price DECIMAL(10, 2),
FOREIGN KEY (order_id) REFERENCES `Order`(order_id)
on delete cascade
on update cascade
,
FOREIGN KEY (pro_id) REFERENCES Product(pro_id)
on delete cascade
on update cascade
);
-- Payments Table
CREATE TABLE Payments (
pay_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
amount DECIMAL(10, 2),
method VARCHAR(50),
pay_date DATETIME,
FOREIGN KEY (order_id) REFERENCES `Order`(order_id)
on delete cascade
on update cascade
);
-- Feedback Table
CREATE TABLE Feedback (
feedback_id INT AUTO_INCREMENT PRIMARY KEY,
cust_id INT,
order_id INT,
Rating INT,
feedback_date DATETIME,
comment TEXT,
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)
on delete cascade
on update cascade
,
FOREIGN KEY (order_id) REFERENCES `Order`(order_id)
on delete cascade
on update cascade
);
-- Stock Transfer Table
CREATE TABLE Stock_Transfer (
transfer_id INT AUTO_INCREMENT PRIMARY KEY,
from_wh_id INT,
to_wh_id INT,
transfer_date DATETIME,
quantity INT,
FOREIGN KEY (from_wh_id) REFERENCES Warehouse(wh_id)
on delete cascade
on update cascade
,
FOREIGN KEY (to_wh_id) REFERENCES Warehouse(wh_id)
on delete cascade
on update cascade
);
-- Restock Table
CREATE TABLE Restock (
restock_id INT AUTO_INCREMENT PRIMARY KEY,
supp_id INT,
pro_id INT,
restock_date DATETIME,
Quantity INT,
delivery_time VARCHAR(50),
FOREIGN KEY (supp_id) REFERENCES Supplier(supp_id)
on delete set null
on update cascade
,
FOREIGN KEY (pro_id) REFERENCES Product(pro_id)
on delete cascade
on update cascade
);
-- Returns Table
CREATE TABLE Returns (
return_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
pro_id INT,
wh_id INT,
Quantity INT,
return_date DATETIME,
reason TEXT,
FOREIGN KEY (order_id) REFERENCES `Order`(order_id)
on delete cascade
on update cascade
,
FOREIGN KEY (pro_id) REFERENCES Product(pro_id)
on delete cascade
on update cascade
,
FOREIGN KEY (wh_id) REFERENCES Warehouse(wh_id)
on delete cascade
on update cascade
);
-- Shipments Table
CREATE TABLE Shipments (
ship_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
ship_date DATETIME,
status VARCHAR(50),
carrier VARCHAR(100),
track_no VARCHAR(100),
expected_date DATETIME,
actual_date DATETIME,
wh_id INT,
FOREIGN KEY (order_id) REFERENCES `Order`(order_id)
on delete cascade
on update cascade
,
FOREIGN KEY (wh_id) REFERENCES Warehouse(wh_id)
on delete cascade
on update cascade
);
-- Shipment_Details Table
CREATE TABLE Shipment_Details (
shipment_details_id INT AUTO_INCREMENT PRIMARY KEY,
ship_id INT,
pro_id INT,
quantity INT,
status VARCHAR(50),
expected_date DATETIME,
actual_date DATETIME,
wh_id INT,
FOREIGN KEY (ship_id) REFERENCES Shipments(ship_id)
on delete cascade
on update cascade
,
FOREIGN KEY (pro_id) REFERENCES Product(pro_id)
on delete cascade
on update cascade
);
DELIMITER //
CREATE TRIGGER update_final_quantity
BEFORE UPDATE ON Inventory
FOR EACH ROW
BEGIN
SET NEW.final_quantity = NEW.Quantity;
END //
DELIMITER ;
DELIMITER //
CREATE EVENT update_initial_quantity_event
ON SCHEDULE EVERY 1 MONTH
DO
BEGIN
UPDATE Inventory
SET initial_quantity = Quantity;
END;
Raw
data_provider.py
#!/usr/bin/env python3
from faker import Faker
fake = Faker()
def get_cit(country):
city_country_map = {
'United States': ['New York', 'Los Angeles', 'Chicago'],
'France': ['Paris', 'Marseille', 'Lyon'],
'Germany': ['Berlin', 'Munich', 'Hamburg'],
'Japan': ['Tokyo', 'Osaka', 'Kyoto'],
'Brazil': ['São Paulo', 'Rio de Janeiro', 'Brasília'],
'Egypt': ['Cairo', 'Alexandria', 'Giza'],
'India': ['Mumbai', 'Delhi', 'Bangalore'],
'China': ['Beijing', 'Shanghai', 'Guangzhou'],
'Russia': ['Moscow', 'Saint Petersburg', 'Novosibirsk'],
'Italy': ['Rome', 'Milan', 'Naples'],
'Spain': ['Madrid', 'Barcelona', 'Valencia'],
'United Kingdom': ['London', 'Birmingham', 'Manchester'],
'Canada': ['Toronto', 'Montreal', 'Vancouver'],
'Australia': ['Sydney', 'Melbourne', 'Brisbane'],
'South Korea': ['Seoul', 'Busan', 'Incheon'],
'Mexico': ['Mexico City', 'Guadalajara', 'Monterrey'],
'Indonesia': ['Jakarta', 'Surabaya', 'Bandung'],
'Nigeria': ['Lagos', 'Kano', 'Ibadan'],
'Turkey': ['Istanbul', 'Ankara', 'Izmir'],
'Iran': ['Tehran', 'Mashhad', 'Isfahan'],
'Pakistan': ['Karachi', 'Lahore', 'Faisalabad'],
'Bangladesh': ['Dhaka', 'Chittagong', 'Khulna'],
'Philippines': ['Manila', 'Davao City', 'Cebu City'],
'Vietnam': ['Ho Chi Minh City', 'Hanoi', 'Da Nang'],
}
if country in city_country_map:
return fake.random.choice(city_country_map[country])
else:
return fake.city()
def get_country():
countries = ['United States', 'France', 'Germany', 'Japan', 'Brazil', 'Egypt', 'India', 'China', 'Russia', 'Italy', 'Spain', 'United Kingdom', 'Canada', 'Australia', 'South Korea', 'Mexico', 'Indonesia', 'Nigeria', 'Turkey', 'Iran', 'Pakistan', 'Bangladesh', 'Philippines', 'Vietnam']
return fake.random.choice(countries)
def get_address():
return fake.address().replace("\n", ", ")
def get_category():
categories = ['Electronics', 'Clothing', 'Furniture',
'Books', 'Toys', 'Food', 'Appliances', 'Jewelry', 'Automotive', 'Sports']
description_map = {
'Electronics': 'Electronic gadgets and accessories',
'Clothing': 'Clothing and apparel',
'Furniture': 'Furniture and home decor',
'Books': 'Books and stationery',
'Toys': 'Toys and games',
'Food': 'Food and beverages',
'Appliances': 'Home appliances',
'Jewelry': 'Jewelry and accessories',
'Automotive': 'Automotive parts and accessories',
'Sports': 'Sporting goods and equipment'
}
name = fake.random.choice(categories)
description = description_map[name]
return name, description
def get_sub_category(name):
sub_categories = {
'Electronics': ['Smartphones', 'Laptops', 'Tablets', 'Headphones', 'Smartwatches', 'Cameras'],
'Clothing': ['Men\'s Clothing', 'Women\'s Clothing', 'Children\'s Clothing', 'Footwear', 'Accessories'],
'Furniture': ['Living Room Furniture', 'Bedroom Furniture', 'Dining Room Furniture', 'Home Office Furniture'],
'Books': ['Fiction', 'Non-fiction', 'Children\'s Books', 'Textbooks', 'Magazines'],
'Toys': ['Action Figures', 'Dolls', 'Building Blocks', 'Board Games', 'Educational Toys'],
'Food': ['Beverages', 'Snacks', 'Canned Food', 'Frozen Food', 'Bakery'],
'Appliances': ['Refrigerators', 'Washing Machines', 'Microwaves', 'Dishwashers'],
'Jewelry': ['Rings', 'Necklaces', 'Bracelets', 'Earrings', 'Watches'],
'Automotive': ['Car Parts', 'Car Accessories', 'Motorcycle Parts', 'Motorcycle Accessories'],
'Sports': ['Fitness Equipment', 'Sportswear', 'Footwear', 'Outdoor Gear']
}
description_map = {
'Smartphones': 'Mobile phones and accessories',
'Laptops': 'Laptops and accessories',
'Tablets': 'Tablets and accessories',
'Headphones': 'Headphones and earphones',
'Smartwatches': 'Smartwatches and fitness trackers',
'Cameras': 'Digital cameras and accessories',
'Men\'s Clothing': 'Men\'s clothing and apparel',
'Women\'s Clothing': 'Women\'s clothing and apparel',
'Children\'s Clothing': 'Children\'s clothing and apparel',
'Footwear': 'Shoes and footwear',
'Accessories': 'Clothing accessories',
'Living Room Furniture': 'Furniture for the living room',
'Bedroom Furniture': 'Furniture for the bedroom',
'Dining Room Furniture': 'Furniture for the dining room',
'Home Office Furniture': 'Furniture for the home office',
'Fiction': 'Fiction books',
'Non-fiction': 'Non-fiction books',
'Children\'s Books': 'Books for children',
'Textbooks': 'Educational textbooks',
'Magazines': 'Magazines and periodicals',
'Action Figures': 'Action figures and collectibles',
'Dolls': 'Dolls and dollhouses',
'Building Blocks': 'Building blocks and construction toys',
'Board Games': 'Board games and puzzles',
'Educational Toys': 'Educational toys and games',
'Beverages': 'Beverages and drinks',
'Snacks': 'Snacks and confectionery',
'Canned Food': 'Canned food and preserves',
'Frozen Food': 'Frozen food and ice cream',
'Bakery': 'Bakery products and pastries',
'Refrigerators': 'Refrigerators and freezers',
'Washing Machines': 'Washing machines and dryers',
'Microwaves': 'Microwave ovens',
'Dishwashers': 'Dishwashers and dishwashers',
'Rings': 'Rings and bands',
'Necklaces': 'Necklaces and pendants',
'Bracelets': 'Bracelets and bangles',
'Earrings': 'Earrings and ear studs',
'Watches': 'Watches and timepieces',
'Car Parts': 'Car parts and components',
'Car Accessories': 'Car accessories and gadgets',
'Motorcycle Parts': 'Motorcycle parts and components',
'Motorcycle Accessories': 'Motorcycle accessories and gadgets',
'Fitness Equipment': 'Fitness equipment and gear',
'Sportswear': 'Sportswear and activewear',
'Footwear': 'Sports shoes and footwear',
'Outdoor Gear': 'Outdoor gear and equipment'
}
s_name = fake.random.choice(sub_categories[name])
description = description_map[name]
return s_name, description
def get_product(sub_category):
products = {
'Smartphones': ['iPhone 12', 'Samsung Galaxy S21', 'Google Pixel 5', 'OnePlus 9', 'Xiaomi Mi 11'],
'Laptops': ['Apple MacBook Pro', 'Dell XPS 15', 'HP Spectre x360', 'Lenovo ThinkPad X1 Carbon', 'Microsoft Surface Laptop 4'],
'Tablets': ['Apple iPad Pro', 'Samsung Galaxy Tab S7', 'Microsoft Surface Pro 7', 'Amazon Fire HD 10', 'Lenovo Tab P11'],
'Headphones': ['Apple AirPods Pro', 'Sony WH-1000XM4', 'Bose QuietComfort 35 II', 'Jabra Elite 85t', 'Sennheiser Momentum 3'],
'Smartwatches': ['Apple Watch Series 6', 'Samsung Galaxy Watch 3', 'Fitbit Versa 3', 'Garmin Forerunner 945', 'Huawei Watch GT 2'],
'Cameras': ['Canon EOS R5', 'Sony Alpha A7 III', 'Nikon Z6 II', 'Fujifilm X-T4', 'Panasonic Lumix GH5'],
'Men\'s Clothing': ['T-Shirts', 'Shirts', 'Jeans', 'Shorts', ' Jackets'],
'Women\'s Clothing': ['Dresses', 'Tops', 'Skirts', 'Pants', 'Coats'],
'Children\'s Clothing': ['T-Shirts', 'Dresses', 'Jeans', 'Shorts', 'Jackets'],
'Footwear': ['Sneakers', 'Boots', 'Sandals', 'Loafers', 'Slippers'],
'Accessories': ['Belts', 'Hats', 'Scarves', 'Sunglasses', 'Watches'],
'Living Room Furniture': ['Sofas', 'Coffee Tables', 'TV Stands', 'Bookshelves', 'Armchairs'],
'Bedroom Furniture': ['Beds', 'Mattresses', 'Dressers', 'Nightstands', 'Wardrobes'],
'Dining Room Furniture': ['Dining Tables', 'Dining Chairs', 'Sideboards', 'Bar Carts', 'Buffets'],
'Home Office Furniture': ['Desks', 'Office Chairs', 'Bookcases', 'Filing Cabinets', 'Computer Desks'],
'Fiction': ['Mystery', 'Thriller', 'Romance', 'Science Fiction', 'Fantasy'],
'Non-fiction': ['Biography', 'History', 'Self-help', 'Cooking', 'Travel'],
'Children\'s Books': ['Picture Books', 'Chapter Books', 'Activity Books', 'Board Books', 'Educational Books'],
'Textbooks': ['Mathematics', 'Science', 'English', 'History', 'Geography'],
'Magazines': ['Fashion', 'Lifestyle', 'Tech', 'Sports', 'Travel'],
'Action Figures': ['Superheroes', 'Villains', 'Anime', 'Cartoons', 'Movies'],
'Dolls': ['Barbie', 'Bratz', 'Monster High', 'American doll', 'Baby Alive'],
'Building Blocks': ['LEGO', 'Mega Bloks', 'K\'NEX', 'Lincoln Logs', 'Tegu'],
'Board Games': ['Monopoly', 'Scrabble', 'Catan', 'Ticket to Ride', 'Risk'],
'Educational Toys': ['Puzzles', 'Science Kits', 'Art Supplies', 'Musical Instruments', 'Building Toys'],
'Beverages': ['Soft Drinks', 'Juices', 'Tea', 'Coffee', 'Energy Drinks'],
'Snacks': ['Chips', 'Candy', 'Nuts', 'Popcorn', 'Cookies'],
'Canned Food': ['Soup', 'Vegetables', 'Fruit', 'Beans', 'Meat'],
'Frozen Food': ['Pizza', 'Ice Cream', 'Vegetables', 'Fruit', 'Meat'],
'Bakery': ['Bread', 'Pastries', 'Cakes', 'Cookies', 'Donuts'],
'Refrigerators': ['LG', 'Samsung', 'Whirlpool', 'GE', 'Haier'],
'Washing Machines': ['LG', 'Samsung', 'Whirlpool', 'GE', 'Haier'],
'Microwaves': ['LG', 'Samsung', 'Whirlpool', 'GE', 'Haier'],
'Dishwashers': ['LG', 'Samsung', 'Whirlpool', 'GE', 'Haier'],
'Rings': ['Diamond', 'Gold', 'Silver', 'Platinum', 'Titanium'],
'Necklaces': ['Diamond', 'Gold', 'Silver', 'Platinum', 'Titanium'],
'Bracelets': ['Diamond', 'Gold', 'Silver', 'Platinum', 'Titanium'],
'Earrings': ['Diamond', 'Gold', 'Silver', 'Platinum', 'Titanium'],
'Watches': ['Analog', 'Digital', 'Smart', 'Luxury', 'Sports'],
'Car Parts': ['Engine', 'Brakes', 'Suspension', 'Transmission', 'Exhaust'],
'Car Accessories': ['Seat Covers', 'Floor Mats', 'Car Covers', 'Steering Wheel Covers', 'Phone Mounts'],
'Motorcycle Parts': ['Engine', 'Brakes', 'Suspension', 'Transmission', 'Exhaust'],
'Motorcycle Accessories': ['Helmets', 'Jackets', 'Gloves', 'Boots', 'Pants'],
'Fitness Equipment': ['Treadmills', 'Exercise Bikes', 'Ellipticals', 'Rowing Machines', 'Home Gyms'],
'Sportswear': ['T-Shirts', 'Shorts', 'Leggings', 'Sports Bras', 'Socks'],
'Footwear': ['Running Shoes', 'Training Shoes', 'Basketball Shoes', 'Soccer Cleats', 'Hiking Boots'],
'Outdoor Gear': ['Tents', 'Sleeping Bags', 'Backpacks', 'Camping Stoves', 'Coolers']
}
price_ranges = {
'Smartphones': (500, 1500),
'Laptops': (800, 2000),
'Tablets': (300, 800),
'Headphones': (100, 500),
'Smartwatches': (200, 600),
'Cameras': (500, 2000),
'Men\'s Clothing': (20, 100),
'Women\'s Clothing': (20, 100),
'Children\'s Clothing': (10, 50),
'Footwear': (30, 150),
'Accessories': (10, 50),
'Living Room Furniture': (500, 2000),
'Bedroom Furniture': (400, 1500),
'Dining Room Furniture': (300, 1000),
'Home Office Furniture': (200, 800),
'Fiction': (10, 30),
'Non-fiction': (10, 30),
'Children\'s Books': (5, 20),
'Textbooks': (20, 100),
'Magazines': (5, 10),
'Action Figures': (10, 50),
'Dolls': (10, 50),
'Building Blocks': (20, 100),
'Board Games': (20, 50),
'Educational Toys': (10, 50),
'Beverages': (1, 5),
'Snacks': (1, 5),
'Canned Food': (1, 5),
'Frozen Food': (1, 5),
'Bakery': (1, 5),
'Refrigerators': (500, 2000),
'Washing Machines': (400, 1500),
'Microwaves': (100, 500),
'Dishwashers': (300, 1000),
'Rings': (100, 500),
'Necklaces': (100, 500),
'Bracelets': (50, 200),
'Earrings': (50, 200),
'Watches': (100, 500),
'Car Parts': (50, 500),
'Car Accessories': (10, 50),
'Motorcycle Parts': (50, 500),
'Motorcycle Accessories': (10, 50),
'Fitness Equipment': (500, 2000),
'Sportswear': (20, 100),
'Footwear': (30, 150),
'Outdoor Gear': (100, 500)
}
name = fake.random.choice(products[sub_category])
price_range = price_ranges[sub_category]
price = fake.random.randint(price_range[0], price_range[1])
return name, price
Raw
helper.py
def get_supervisors_ids():
conn, cursor = connect_db()
cursor.execute("SELECT emp_id FROM Employee WHERE Position = 'Supervisor'")
emp_ids = cursor.fetchall()
cursor.close()
conn.close()
return [emp_id[0] for emp_id in emp_ids]
def update_supervisor(supervisor_id, employee_id):
conn, cursor = connect_db()
cursor.execute("UPDATE Employee SET smp_id = %s WHERE emp_id = %s", (supervisor_id, employee_id))
conn.commit()
cursor.close()
conn.close()
def emp_sup():
'''Assign supervisors to employees'''
supervisors = get_supervisors_ids()
conn, cursor = connect_db()
cursor.execute("SELECT emp_id FROM Employee WHERE Position != 'Supervisor'")
emp_ids = cursor.fetchall()
for emp_id in emp_ids:
supervisor_id = random.choice(supervisors)
update_supervisor(supervisor_id, emp_id[0])
cursor.close()
def get_sub_category_names_and_ids():
conn, cursor = connect_db()
cursor.execute("SELECT sub_cat_id, Name FROM Sub_Category")
sub_cats = cursor.fetchall()
cursor.close()
conn.close()
return sub_cats
def get_warehouse_ids():
conn, cursor = connect_db()
cursor.execute("SELECT wh_id FROM Warehouse")
wh_ids = cursor.fetchall()
cursor.close()
conn.close()
return [wh_id[0] for wh_id in wh_ids]
def insert_category_and_sub_category_without_repeating(number_of_categories, number_of_sub_categories):
list_of_category_inserted = {}
map_category_sub_category = {}
for i in range(number_of_categories):
category = get_category()
if category[0] not in list_of_category_inserted:
list_of_category_inserted[category[0]] = category[1]
sub_categories = []
tmp = []
for _ in range(number_of_sub_categories):
sub_category = get_sub_category(category[0])
if sub_category[0] not in tmp:
tmp.append(sub_category[0])
sub_categories.append({sub_category[0]: sub_category[1]})
map_category_sub_category[category[0]] = sub_categories
conn, cursor = connect_db()
for category in list_of_category_inserted.items():
cursor.execute("INSERT INTO Category (Name, Description) VALUES (%s, %s) RETURNING cat_id", (category[0], category[1]))
cat_id = cursor.fetchone()[0]
for sub_category in map_category_sub_category[category[0]]:
for sub_cat in sub_category.items():
cursor.execute("INSERT INTO Sub_Category (cat_id, Name, Description) VALUES (%s, %s, %s) RETURNING sub_cat_id", (cat_id, sub_cat[0], sub_cat[1]))
conn.commit()
cursor.close()
conn.close()
def get_product_ids():
conn, cursor = connect_db()
cursor.execute("SELECT pro_id FROM Product")
pro_ids = cursor.fetchall()
cursor.close()
conn.close()
return [pro_id[0] for pro_id in pro_ids]
Raw
insert.py
import mysql.connector
from faker import Faker
from data_provider import get_address, get_country, get_cit, get_category, get_sub_category,get_product
import random
fake = Faker()
# Database connection configuration
db_config = {
'host': 'localhost',
'user': 'root',
'password': 'Sssal@292002',
'database': 'InventoryManagement'
}
def connect_db():
"""Connect to the database and return the connection and cursor."""
conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()
return conn, cursor
def insert_location():
conn, cursor = connect_db()
address = get_address()
country = get_country()
city = get_cit(country)
cursor.execute("INSERT INTO Location (address, city, country) VALUES (%s, %s, %s)", (address, city, country))
loc_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return loc_id
def insert_warehouse(loc_id):
conn, cursor = connect_db()
capacity = fake.random_int(min=1000, max=5000)
cursor.execute("INSERT INTO Warehouse (loc_id, Capacity) VALUES (%s, %s)", (loc_id, capacity))
wh_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return wh_id
def insert_employee(smp_id=None, wh_id=None):
conn, cursor = connect_db()
name = fake.name()
positions = ['Manager', 'Supervisor', 'Sales Associate', 'Cashier', 'Stock Clerk']
position = fake.random.choice(positions)
dob = fake.date_of_birth(minimum_age=18, maximum_age=60)
email = fake.email()
phone = fake.phone_number()
phone = phone[:19]
cursor.execute("INSERT INTO Employee (Name, Position, DoB, email, phone, smp_id, wh_id) VALUES (%s, %s, %s, %s, %s, %s, %s)",
(name, position, dob, email, phone, smp_id, wh_id))
emp_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return emp_id
def insert_supplier():
conn, cursor = connect_db()
name = fake.company()
address = fake.address()
phone = fake.phone_number()
phone = phone[:19]
cursor.execute("INSERT INTO Supplier (Name, address, phone) VALUES (%s, %s, %s)", (name, address, phone))
supp_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return supp_id
def insert_customer():
conn, cursor = connect_db()
name = fake.name()
address = fake.address()
phone = fake.phone_number()
phone = phone[:19]
email = fake.email()
dob = fake.date_of_birth(minimum_age=18, maximum_age=80)
cursor.execute("INSERT INTO Customer (Name, address, phone, email, DoB) VALUES (%s, %s, %s, %s, %s)",
(name, address, phone, email, dob))
cust_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return cust_id
def insert_category():
conn, cursor = connect_db()
name, description = get_category()
cursor.execute("INSERT INTO Category (Name, Description) VALUES (%s, %s)", (name, description))
cat_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return cat_id, name
def insert_sub_category(cat_id, name):
conn, cursor = connect_db()
s_name, description = get_sub_category(name)
cursor.execute("INSERT INTO Sub_Category (cat_id, Name, Description) VALUES (%s, %s, %s)", (cat_id, s_name, description))
sub_cat_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return sub_cat_id, s_name
def insert_product(sub_cat_id,sub_cat_name):
conn, cursor = connect_db()
name, price = get_product(sub_cat_name)
cost_price = price * random.uniform(0.5, 0.8)
cursor.execute("INSERT INTO Product (sub_cat_id, Name, price, cost_price) VALUES (%s, %s, %s, %s)",
(sub_cat_id, name, price, cost_price))
pro_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return pro_id
def insert_inventory(pro_id, wh_id):
conn, cursor = connect_db()
quantity = fake.random_int(min=50, max=500)
reorder_point = fake.random_int(min=10, max=50)
initial_quantity = abs(quantity - fake.random_int(min=20, max=200))
final_quantity = abs(quantity - fake.random_int(min=20, max=200))
cursor.execute("INSERT INTO Inventory (pro_id, Quantity, Reorder_point, wh_id, initial_quantity, final_quantity) VALUES (%s, %s, %s, %s, %s, %s)",
(pro_id, quantity, reorder_point, wh_id, initial_quantity, final_quantity))
inv_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return inv_id
def insert_order(cust_id):
conn, cursor = connect_db()
order_date = fake.date_time_this_year()
amount = fake.random_number(digits=3)
status = fake.random_element(elements=('Pending', 'Shipped', 'Delivered', 'Cancelled'))
cursor.execute("INSERT INTO `Order` (cust_id, order_date, amount, status) VALUES (%s, %s, %s, %s)",
(cust_id, order_date, amount, status))
order_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return order_id
def insert_order_details(order_id, pro_id):
conn, cursor = connect_db()
quantity = fake.random_int(min=1, max=10)
price = fake.random_number(digits=2)
cursor.execute("INSERT INTO Order_Details (order_id, pro_id, quantity, price) VALUES (%s, %s, %s, %s)",
(order_id, pro_id, quantity, price))
order_details_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return order_details_id
def insert_payments(order_id):
conn, cursor = connect_db()
amount = fake.random_number(digits=3)
method = fake.random_element(elements=('Credit Card', 'Debit Card', 'PayPal', 'Bank Transfer'))
pay_date = fake.date_time_this_year()
cursor.execute("INSERT INTO Payments (order_id, amount, method, pay_date) VALUES (%s, %s, %s, %s)",
(order_id, amount, method, pay_date))
pay_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return pay_id
def insert_feedback(cust_id, order_id):
conn, cursor = connect_db()
rating = fake.random_int(min=1, max=5)
feedback_date = fake.date_time_this_year()
comment = fake.random_choices(elements=('Good', 'Bad', 'Average', 'Excellent'), length=1)[0]
cursor.execute("INSERT INTO Feedback (cust_id, order_id, Rating, feedback_date, comment) VALUES (%s, %s, %s, %s, %s)",
(cust_id, order_id, rating, feedback_date, comment))
feedback_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return feedback_id
def insert_stock_transfer(from_wh_id, to_wh_id):
conn, cursor = connect_db()
transfer_date = fake.date_time_this_year()
quantity = fake.random_int(min=10, max=100)
cursor.execute("INSERT INTO Stock_Transfer (from_wh_id, to_wh_id, transfer_date, quantity) VALUES (%s, %s, %s, %s)",
(from_wh_id, to_wh_id, transfer_date, quantity))
transfer_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return transfer_id
def insert_restock(supp_id, pro_id):
conn, cursor = connect_db()
restock_date = fake.date_time_this_year()
quantity = fake.random_int(min=50, max=200)
delivery_time = fake.time()
cursor.execute("INSERT INTO Restock (supp_id, pro_id, restock_date, Quantity, delivery_time) VALUES (%s, %s, %s, %s, %s)",
(supp_id, pro_id, restock_date, quantity, delivery_time))
restock_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return restock_id
def insert_returns(order_id, pro_id, wh_id):
conn, cursor = connect_db()
quantity = fake.random_int(min=1, max=10)
return_date = fake.date_time_this_year()
reason = fake.random_choices(elements=('Damaged', 'Wrong Item', 'Defective', 'Not as Expected'), length=1)[0]
cursor.execute("INSERT INTO Returns (order_id, pro_id, wh_id, Quantity, return_date, reason) VALUES (%s, %s, %s, %s, %s, %s)",
(order_id, pro_id, wh_id, quantity, return_date, reason))
return_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return return_id
def insert_shipments(order_id, wh_id):
conn, cursor = connect_db()
ship_date = fake.date_time_this_year()
status = fake.random_element(elements=('Pending', 'Shipped', 'Delivered', 'Returned'))
carrier = fake.company()
track_no = fake.uuid4()
expected_date = fake.date_time_this_year()
actual_date = fake.date_time_this_year()
cursor.execute("INSERT INTO Shipments (order_id, ship_date, status, carrier, track_no, expected_date, actual_date, wh_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
(order_id, ship_date, status, carrier, track_no, expected_date, actual_date, wh_id))
ship_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return ship_id
def insert_shipment_details(ship_id, pro_id, wh_id):
conn, cursor = connect_db()
quantity = fake.random_int(min=1, max=10)
status = fake.random_element(elements=('Pending', 'Shipped', 'Delivered', 'Returned'))
expected_date = fake.date_time_this_year()
actual_date = fake.date_time_this_year()
cursor.execute("INSERT INTO Shipment_Details (ship_id, pro_id, quantity, status, expected_date, actual_date, wh_id) VALUES (%s, %s, %s, %s, %s, %s, %s)",
(ship_id, pro_id, quantity, status, expected_date, actual_date, wh_id))
shipment_details_id = cursor.lastrowid
conn.commit()
cursor.close()
conn.close()
return shipment_details_id
def get_warehouse():
conn, cursor = connect_db()
cursor.execute("SELECT wh_id FROM Warehouse")
wh_ids = cursor.fetchall()
cursor.close()
conn.close()
return wh_ids
def get_supervisor():
conn, cursor = connect_db()
cursor.execute("SELECT emp_id FROM Employee WHERE Position = 'Supervisor'")
smp_ids = cursor.fetchall()
cursor.close()
conn.close()
return smp_ids
def update_employee(smp_id):
conn, cursor = connect_db()
cursor.execute("UPDATE Employee SET smp_id = %s WHERE Position != 'Supervisor'", (smp_id,))
conn.commit()
cursor.close()
conn.close()
if __name__ == "__main__":
print("hello")
connect_db()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment