TechStore: SQL & Python Architecture Explanation

This document explains how the TechStore project uses SQL (MySQL) and Python (Flask)
together to store and manage data. It covers database structure, Python code patterns, and the
complete data flow.

1. Database Connection Setup

 The connection between Python and MySQL is established in market/__init__.py:
 • Reads [Link] (host, user, password, database name)
 • Configures Flask app with MySQL credentials
 • Initializes MySQL object: my_sql = MySQL(app)
 This creates a connection pool that routes can use via my_sql.[Link]()

2. Database Structure (SQL Tables)

 The database schema is defined in [Link]. Key tables:

product: Stores catalog items: Product_ID (PK), Name, Price, Brand, Measurement, Unit,
Category_ID, Admin_ID

cart: Temporary shopping carts: Cart_ID (PK), Total_Value, Total_Count, Offer_ID, Final_Amount

associated_with: Links customers to products in carts: (Customer_ID, Cart_ID, Product_ID)

composite PK

orders: Finalized orders: Order_ID (PK), Mode, Amount, City, State, Cart_ID, Delivery_Boy_ID,
Date

customer_orders: Maps customers to orders for history: (Order_ID, Customer_ID) composite PK

seller_product_requests: Pending seller submissions: Request_ID (PK), Seller_ID, Name, Brand,

Price, Status

customer: User accounts: Customer_ID (PK), First_Name, Last_Name, Email, Mobile_No,

Password

seller: Seller accounts: Seller_ID (PK), First_Name, Last_Name, Email, Phone_Number, Password,
Admin_ID

admin: Admin accounts: Admin_ID (PK), First_Name, Last_Name, Admin_Password

delivery_boy: Delivery staff: Delivery_Boy_ID (PK), First_Name, Last_Name, Mobile_No, Email,
Password, Admin_ID

offer: Promotional codes: Offer_ID (PK), Promo_Code, Percentage_Discount, Min_OrderValue,

Max_Discount, Admin_ID

category: Product categories: Category_ID (PK), Category_Name

3. Python Code Patterns for Database Operations

3.1 Reading Data (SELECT)

 Pattern used throughout [Link]:
 1. Get cursor: cur = my_sql.[Link]()
 2. Execute query: [Link]("SELECT * FROM product")
 3. Fetch results: product_all = [Link]()
 4. Process data: Loop through tuples and build dictionaries
 5. Close cursor: [Link]()

3.2 Writing Data (INSERT/UPDATE)

 Pattern for saving data:
 1. Get cursor: cur = my_sql.[Link]()
 2. Execute INSERT/UPDATE with parameters: [Link]("INSERT INTO ... VALUES(%s, %s)",
(val1, val2))
 3. Commit transaction: my_sql.[Link]()
 4. Close cursor: [Link]()

3.3 Parameterized Queries (Security)

 All queries use parameterized statements to prevent SQL injection:
 • [Link]("SELECT * FROM product WHERE Name = %s", (name,))
 • The %s placeholders are replaced safely by MySQLdb

4. Complete Data Flow Examples

4.1 Customer Browsing Products

 User visits /home/<user_id>
 Python route userEnter() executes
 Opens cursor: cur = my_sql.[Link]()
 Executes SQL: SELECT * FROM product
 MySQL returns all product rows as tuples
 Python loops through tuples, extracts columns (prod[1]=Name, prod[2]=Price, etc.)
 Builds dictionary list: [{"Name": "...", "Price": 100, ...}, ...]
 Passes list to template: render_template("[Link]", list=my_list)
 Jinja2 template renders HTML with product cards
 User sees products on webpage

4.2 Adding Product to Cart

 User clicks "Add to Cart" on product
 GET request with parameters: ?Name=...&Price=...&Brand=...
 Python route userEnter() receives [Link]
 Extracts Name, Brand, Price from URL parameters
 Updates Python global variables: total_val += Price, total_count += 1
 Adds product dict to customer_cart_list (in-memory Python list)
 User clicks "My Cart" button
 POST request to /home/<user_id>
 Python creates cart record in database: INSERT INTO cart(Cart_ID, Total_Value, ...)
 Then inserts product associations: INSERT INTO associated_with(Customer_ID, Cart_ID,
Product_ID)
 Redirects to /order/<user_id> to show cart page

4.3 Placing an Order

 User applies promo code and clicks "Place Order"
 POST to /order/<user_id> route placeOrder()
 Python validates promo code: SELECT * FROM offer WHERE Promo_Code = %s
 Calculates discount and updates cart: UPDATE cart SET Offer_ID = ..., Final_Amount = ...
 Inserts product associations: INSERT IGNORE INTO associated_with(...)
 Redirects to /placeOrder/<user_id> (order details form)
 User fills address and submits
 POST to /placeOrder/<user_id> route order_placing()
 Python extracts form data: HNO, City, State, Mode
 Inserts order: INSERT INTO orders(Mode, Amount, City, State, Cart_ID, Delivery_Boy_ID, ...)
 Maps customer to order: INSERT INTO customer_orders(Order_ID, Customer_ID)
 Cleans up: DELETE FROM associated_with WHERE Cart_ID = ...
 Commits transaction: my_sql.[Link]()
 Shows success message with delivery ETA
4.4 Seller Submitting Product
 Seller fills form: Name, Brand, Price
 POST to /sell/<seller_id> route sell()
 Python extracts form data and validates (name, price > 0)
 Inserts pending request: INSERT INTO seller_product_requests(Seller_ID, Name, Brand,
Price, Status)
 Status defaults to "pending"
 Commits: my_sql.[Link]()
 Shows flash message: "Product submitted for admin approval"
 Data stored in database but NOT visible to customers yet

4.5 Admin Approving Product

 Admin visits /adminSellerProducts/<admin_id>
 Python route adminSellerProducts() executes
 Queries pending requests: SELECT r.*, s.First_Name, s.Last_Name FROM
seller_product_requests r JOIN seller s ...
 Builds list of request dictionaries
 Renders [Link] with pending_requests
 Admin clicks "Approve" button
 POST with action=approve and request_id
 Python fetches request: SELECT Name, Brand, Price FROM seller_product_requests WHERE
Request_ID = %s
 Inserts into main catalog: INSERT INTO product(Name, Price, Brand, ...)
 Updates request status: UPDATE seller_product_requests SET Status="approved",
Admin_ID=%s
 Commits transaction
 Product now appears in customer product list (SELECT * FROM product)

5. Data Storage Mechanisms

5.1 In-Memory Storage (Python Variables)

 Temporary data stored in Python global variables:
 • customer_cart_list = [] - List of product dictionaries during shopping session
 • total_val = 0 - Running total of cart value
 • total_count = 0 - Number of items in cart
 • cart_id = 0 - Current cart identifier
 Note: This data is lost when server restarts. Only persisted when user creates cart in
database.
5.2 Persistent Storage (MySQL Database)
 All permanent data stored in MySQL tables:
 • Product catalog: product table
 • User accounts: customer, seller, admin tables
 • Shopping carts: cart table (created when user clicks "My Cart")
 • Cart contents: associated_with table (links products to carts)
 • Finalized orders: orders table
 • Order tracking: customer_orders table (maps customers to their orders)
 • Seller requests: seller_product_requests table (pending/approved/rejected)

5.3 Foreign Key Relationships

 Database enforces relationships via foreign keys:
 • orders.Cart_ID → cart.Cart_ID (ON DELETE CASCADE)
 • associated_with.Cart_ID → cart.Cart_ID (ON DELETE CASCADE)
 • associated_with.Product_ID → product.Product_ID (ON DELETE CASCADE)
 • customer_orders.Order_ID → orders.Order_ID (ON DELETE CASCADE)
 • customer_orders.Customer_ID → customer.Customer_ID (ON DELETE CASCADE)
 • seller_product_requests.Seller_ID → seller.Seller_ID (ON DELETE CASCADE)
 This ensures data integrity: deleting a cart removes its associations, deleting an order
removes customer mapping, etc.

6. Transaction Management
 Python uses explicit commits for data consistency:
 • After INSERT/UPDATE: my_sql.[Link]()
 • If error occurs: my_sql.[Link]() (in some error handlers)
 • Multiple operations in one transaction (e.g., approve product = INSERT product +
UPDATE request) are committed together

7. Data Types & Conversions

 Python ↔ MySQL type mappings:
 • Python int → MySQL INT (Price, Amount, IDs)
 • Python str → MySQL VARCHAR (Name, Brand, Email, etc.)
 • Python None → MySQL NULL (optional fields)
 • MySQL tuples → Python converts to dictionaries for templates
 • Example: order[0] = Order_ID, order[1] = Mode, order[2] = Amount (tuple indexing)
8. Key Code Patterns in [Link]

8.1 Reading Products for Display

 Code from userEnter() route:
 cur = my_sql.[Link]()
 [Link]("SELECT * FROM product")
 product_all = [Link]() # Returns list of tuples
 for prod in product_all:
 temp_dict = {"Name": prod[1], "Price": prod[2], "Brand": prod[3]}
 my_list.append(temp_dict)
 [Link]()

8.2 Inserting New Data

 Code from sell() route (seller submission):
 cur = my_sql.[Link]()
 [Link]("INSERT INTO seller_product_requests (Seller_ID, Name, Brand, Price)
VALUES(%s, %s, %s, %s)", (seller_id, name, brand, price))
 my_sql.[Link]() # Saves to database
 [Link]()

8.3 Updating Existing Data

 Code from placeOrder() route (applying discount):
 [Link]("UPDATE cart SET Offer_ID = %s, Final_Amount = %s WHERE Cart_ID = %s",
(offer_id, new_total, cart_id))
 my_sql.[Link]()

8.4 Joining Tables

 Code from adminSellerProducts() route:
 [Link]("SELECT r.Request_ID, [Link], s.First_Name, s.Last_Name FROM
seller_product_requests r JOIN seller s ON r.Seller_ID = s.Seller_ID WHERE [Link] =
"pending"")
 This combines data from two tables to show seller name with each request

9. Data Lifecycle Example: Complete Order Flow

 1. Customer browses: Python reads product table → displays on webpage
 2. Customer adds item: Python updates in-memory customer_cart_list
 3. Customer clicks "My Cart": Python creates cart record in database
 4. Python inserts associated_with records linking products to cart
 5. Customer applies promo: Python reads offer table, updates cart.Final_Amount
 6. Customer places order: Python inserts into orders table
 7. Python inserts into customer_orders table for tracking
 8. Python deletes associated_with records (cart cleanup)
 9. Customer views history: Python reads customer_orders JOIN orders to show past orders
 10. Data persists in MySQL even after server restart

10. Summary
TechStore uses a three-tier architecture: Python (Flask) handles business logic and routes,
MySQL stores all persistent data, and Jinja2 templates render the UI. Data flows: User action →
Python route → SQL query → MySQL database → Python processes results → Template
renders HTML → User sees updated page. All critical data (products, orders, users) is stored in
MySQL with foreign key relationships ensuring integrity. Temporary shopping data exists in
Python memory until cart is created, then persists in database tables.