Python MySQL: 100 Real-World Examples

Python MySQL: 100 Real-World Examples with Combined Topics

This tutorial provides 100 examples combining Python (lists, dictionaries, tuples, strings, functions, loops, conditionals, classes, inheritance, encapsulation) and MySQL topics (Introduction, Database Creation, Create Table, Insert Data, Describe/Alter/Drop Table, Select Records, Where/Like/Order By, Delete Records, Update Records, Limit/Hint/Auto/Regex/Dedupe, Commit/Rollback, Join Tables, Aggregate Functions, Subqueries, Stored Procedures, Indexes, Views, Triggers, Dates/Times, DISTINCT) for real-time applications like e-commerce, user management, inventory systems, and analytics. Examples use mysql-connector-python in a standard Python environment with a MySQL server on localhost and a database my_db with tables users, orders, products, categories, cart, and user_logs. Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks and outputs span 100% width of the content area (70% on desktop, 100% on mobile).

Prerequisites: Ensure MySQL server is running, my_db database exists, and tables are set up as described. Install mysql-connector-python (pip install mysql-connector-python) and replace your_password. Create stored procedures, triggers, and views as shown in the setup instructions.

Example 1: Connect to MySQL (Introduction, Functions)

Defines a function to connect to MySQL for an e-commerce platform setup.

Output:

Connected to MySQL database

Example 2: Create E-commerce Database (Database Creation)

Creates a database for an e-commerce application.

Output:

E-commerce database created

Example 3: Create Cart Table (Create Table, Classes)

Uses a DatabaseManager class to create a cart table for an e-commerce shopping cart.

Output:

Cart table created

Example 4: Insert Product with Validation (Insert Data, Strings, Classes)

Uses a Product class with string validation to insert a product.

Output:

Product Headphones added

Example 5: Alter Table to Add Discount (Describe/Alter/Drop Table)

Alters the products table to add a discount column for a promotional campaign.

Output:

Discount column added to products

Example 6: Fetch User Profile (Select Records, Dictionaries)

Fetches user details into a dictionary for a user profile page.

Output:

User profile for ID 4:
{'name': 'Diana Smith', 'email': 'diana@example.com'}

Example 7: Search Products by Name (Where/Like/Order By, Strings)

Searches products using LIKE with string processing for a search bar.

Output:

Search results for 'phone':
('Smartphone', 699.99)

Example 8: Delete Old Cart Items (Delete Records, Dates/Times)

Deletes cart items older than 30 days for cleanup in an e-commerce system.

Output:

Deleted 0 old cart items

Example 9: Update Product Discount (Update Records, Classes)

Uses a ProductManager class to update product discounts for a sale event.

Output:

Updated discount for product ID 1

Example 10: Limit Product Results (Limit/Hint/Auto/Regex/Dedupe, Lists)

Fetches a limited number of products into a list for a featured products section.

Output:

Top 2 products:
('Smartphone', 699.99)
('Headphones', 99.99)

Example 11: Transactional Order Placement (Commit/Rollback, Classes)

Uses a Checkout class to place an order with a transaction for a checkout system.

Output:

Order placed for user 4: Laptop

Example 12: User Orders with Join (Join Tables, Lists)

Fetches user orders using INNER JOIN into a list for an order history page.

Output:

Orders for user 4:
('Diana Smith', 'Tablet')
('Diana Smith', 'Monitor')

Example 13: Total Sales by Category (Aggregate Functions, Dictionaries)

Calculates total sales per category using SUM and GROUP BY into a dictionary for an analytics dashboard.

import mysql.connector
def get_sales_by_category():
    sales = {}
    try:
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",  # Replace with your MySQL password
            database="my_db"
        )
        cursor = connection.cursor()
        query = """
            SELECT c.name, SUM(o.total) AS total_sales
            FROM categories c
            LEFT JOIN products p ON c.category_id = p.category_id
            LEFT JOIN orders o ON p.name = o.product
            GROUP BY c.name
        """
        cursor.execute(query)
        for category, total in cursor.fetchall():
            sales[category] = float(total) if total else 0
        print("Sales by category:")
        for category, total in sales.items():
            print(f"{category}: ${total}")
        cursor.close()
        connection.close()
        return sales
    except mysql.connector.Error as e:
        print(f"Error: {e}")
        return {}
get_sales_by_category()

Output:

Sales by category:
Tech: $799.98

Example 14: High-Value Customers (Subqueries, Tuples)

Identifies users with orders over $500 using a subquery, stored in a tuple for a loyalty program.

Output:

High-value customers:
Diana Smith

Example 15: Place Order with Stored Procedure (Stored Procedures, Classes)

Uses a Checkout class to call a stored procedure for order placement.

Output:

Order placed for user 4: Phone

Example 16: Create Index on Email (Indexes, Functions)

Creates an index on users.email to optimize email-based queries.

Output:

Index created on users.email

Example 17: User Orders View (Views, Lists)

Queries the user_orders view into a list for an order summary report.

Output:

User orders from view:
('Diana Smith', 'Tablet')
('Diana Smith', 'Monitor')
('Eve', None)
('Grace', None)

Example 18: Stock Update Trigger (Triggers, Classes)

Uses an InventoryManager class to insert an order, triggering a stock update.

Output:

Order added for Tablet, stock updated via trigger

Example 19: Recent Orders (Dates/Times, Tuples)

Fetches orders from the last 30 days into a tuple for an order tracking system.

Output:

Recent orders:
(4, 'Tablet', '2025-07-01 10:00:00')
(4, 'Monitor', '2025-07-10 14:30:00')

Example 20: Unique Products Ordered (DISTINCT, Tuples)

Fetches unique ordered products using DISTINCT into a tuple for a recommendation system.

Output:

Unique ordered products:
Tablet
Monitor

Example 21: User Registration with Email Validation (Insert Data, Strings, Classes)

Uses a User class with email validation to register a user.

import mysql.connector
import re
class User:
    def __init__(self, name, email, password):
        self._name = name.strip()
        self._email = self._validate_email(email)
        self._password = password
    def _validate_email(self, email):
        if not re.match(r"[^@]+@[^@]+\.[^@]+", email):
            raise ValueError("Invalid email format")
        return email
    def register(self):
        try:
            connection = mysql.connector.connect(
                host="localhost",
                user="root",
                password="your_password",  # Replace with your MySQL password
                database="my_db"
            )
            cursor = connection.cursor()
            query = "INSERT INTO users (name, email, password) VALUES (%s, %s, %s)"
            cursor.execute(query, (self._name, self._email, self._password))
            connection.commit()
            print(f"User {self._name} registered")
            cursor.close()
            connection.close()
        except mysql.connector.Error as e:
            print(f"Error: {e}")
user = User("John Doe", "john@example.com", "hashed_pass")
user.register()

Output:

User John Doe registered

Example 22: Premium User with Inheritance (Inheritance, Subqueries)

Uses a PremiumUser class inheriting from User to check premium status.

import mysql.connector
class User:
    def __init__(self, name, email):
        self._name = name
        self._email = email
class PremiumUser(User):
    def __init__(self, name, email):
        super().__init__(name, email)
    def check_premium_status(self, min_total):
        try:
            connection = mysql.connector.connect(
                host="localhost",
                user="root",
                password="your_password",  # Replace with your MySQL password
                database="my_db"
            )
            cursor = connection.cursor()
            query = """
                SELECT name
                FROM users
                WHERE id IN (SELECT user_id FROM orders WHERE total > %s)
                AND name = %s
            """
            cursor.execute(query, (min_total, self._name))
            result = cursor.fetchone()
            print(f"Premium status for {self._name}: {'Premium' if result else 'Not Premium'}")
            cursor.close()
            connection.close()
        except mysql.connector.Error as e:
            print(f"Error: {e}")
user = PremiumUser("Diana Smith", "diana@example.com")
user.check_premium_status(500)

Output:

Premium status for Diana Smith: Premium

Example 23: Cart Items with Join (Join Tables, Dictionaries)

Fetches user cart items with product details using INNER JOIN into a dictionary for a checkout page.

import mysql.connector
def get_user_cart(user_id):
    cart = {}
    try:
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",  # Replace with your MySQL password
            database="my_db"
        )
        cursor = connection.cursor()
        query = """
            SELECT p.name, c.quantity, p.price
            FROM cart c
            INNER JOIN products p ON c.product_id = p.id
            WHERE c.user_id = %s
        """
        cursor.execute(query, (user_id,))
        for name, quantity, price in cursor.fetchall():
            cart[name] = {"quantity": quantity, "price": float(price)}
        print(f"Cart for user {user_id}:")
        for item, details in cart.items():
            print(f"{item}: {details['quantity']} units, ${details['price']}")
        cursor.close()
        connection.close()
        return cart
    except mysql.connector.Error as e:
        print(f"Error: {e}")
        return {}
get_user_cart(4)

Output:

Cart for user 4:
Smartphone: 2 units, $699.99

Example 24: Count Orders by User (Aggregate Functions, Classes)

Uses an OrderManager class to count orders per user for an analytics report.

Output:

User order counts:
('Diana Smith', 2)
('Eve', 0)
('Grace', 0)

Example 25: Add User and Order with Procedure (Stored Procedures, Functions)

Calls a stored procedure to add a user and order for a streamlined checkout.

Output:

Added user Jane Doe and order for Phone

Example 26: Regex Product Search (Limit/Hint/Auto/Regex/Dedupe, Strings)

Searches products using regex for advanced filtering in a product catalog.

Output:

Products matching regex 'phone|tablet':
('Smartphone', 699.99)
('Tablet', 499.99)

Example 27: Create Product Summary View (Views, Classes)

Creates a view for product summaries using a DatabaseManager class.

Output:

Product summary view created

Example 28: Log User Updates with Trigger (Triggers, Update Records)

Updates a user’s email, triggering a log entry in user_logs.

Output:

User email updated, log entry created

Example 29: Order History by Date (Dates/Times, Join Tables)

Fetches user order history within a date range using a join for a reporting system.

Output:

Order history since 2025-07-01
('Diana Smith', 'Tablet', '2025-07-01 10:00:00')
('Diana Smith', 'Monitor', '2025-07-10 14:30:00')

Example 30: Deduplicate Emails (Limit/Hint/Auto/Regex/Dedupe, Loops)

Removes duplicate emails from the users table using a loop and subquery.

Output:

Removed 0 duplicate emails

Example 31: Add Cart Item (Insert Data, Classes, Dictionaries)

Uses a Cart class to add an item to the cart, storing details in a dictionary.

import mysql.connector
class Cart:
    def __init__(self, user_id):
        self._user_id = user_id
        self._connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",  # Replace with your MySQL password
            database="my_db"
        )
    def add_item(self, product_id, quantity):
        try:
            cursor = self._connection.cursor()
            query = "INSERT INTO cart (user_id, product_id, quantity) VALUES (%s, %s, %s)"
            cursor.execute(query, (self._user_id, product_id, quantity))
            self._connection.commit()
            print(f"Added {quantity} of product ID {product_id} to cart")
            cursor.close()
            return {"user_id": self._user_id, "product_id": product_id, "quantity": quantity}
        except mysql.connector.Error as e:
            print(f"Error: {e}")
            return {}
    def __del__(self):
        self._connection.close()
cart = Cart(4)
cart.add_item(2, 1)

Output:

Added 1 of product ID 2 to cart

Example 32: Product Categories with Join (Join Tables, Lists)

Fetches products with their categories using a join, stored in a list for a catalog page.

Output:

Product categories:
('Smartphone', 'Tech')
('Headphones', 'Tech')

Example 33: Average Order Value (Aggregate Functions, Functions)

Calculates the average order value using AVG for a financial report.

Output:

Average order value: $399.99

Example 34: Products in Stock (Subqueries, Lists)

Fetches products with stock using a subquery, stored in a list for inventory management.

Output:

Products in stock:
Smartphone
Headphones

Example 35: Update Stock with Procedure (Stored Procedures, Classes)

Uses a class to call a stored procedure to update product stock.

Output:

Updated stock for product ID 1

Example 36: Describe Table Structure (Describe/Alter/Drop Table, Dictionaries)

Fetches products table structure into a dictionary for admin panel.

Output:

Structure of products:
{'id': 'int', 'name': 'varchar(100)', 'price': 'decimal(10,2)', 'stock': 'int', 'category_id': 'int', 'discount': 'decimal(5,2)'}

Example 37: Delete User (Delete Records, Functions)

Deletes a user for account deactivation.

Output:

Deleted user ID 5

Example 38: Sort Products by Price (Where/Like/Order By, Lists)

Sorts products by price for a product listing page.

Output:

Sorted products by price:
('Smartphone', 699.99)
('Tablet', 499.99)
('Headphones', 99.99)

Example 39: Transactional Cart Update (Commit/Rollback, Classes)

Updates cart quantity with a transaction for a checkout system.

Output:

Updated cart for user 4

Example 40: Product Discounts with View (Views, Dictionaries)

Fetches discounted products from a view into a dictionary for a sale page.

Output:

Discounted products:
Headphones: $99.99, Tech

Example 41: Check Database Version (Introduction, Strings)

Fetches MySQL version using a string for system diagnostics.

Output:

MySQL version: 8.0.27

Example 42: Create User Logs Table (Create Table, Classes)

Creates a user_logs table using a class for audit logging.

Output:

User logs table created

Example 43: Insert Category (Insert Data, Functions)

Inserts a new category for product organization.

Output:

Category Electronics added

Example 44: Drop Table (Describe/Alter/Drop Table, Functions)

Drops a temporary table for cleanup in an admin panel.

Output:

Table temp_table dropped

Example 45: Select All Products (Select Records, Lists)

Fetches all products into a list for a product catalog.

Output:

All products:
('Smartphone', 699.99)
('Tablet', 499.99)
('Headphones', 99.99)

Example 46: Filter Products by Price (Where/Like/Order By, Lists)

Filters products by price range for a product listing page.

Output:

Products between $100 and $600:
('Tablet', 499.99)

Example 47: Delete Order (Delete Records, Functions)

Deletes an order for order cancellation.

Output:

Deleted order ID 1

Example 48: Update User Name (Update Records, Classes)

Updates a user’s name using a class for profile management.

Output:

Updated name for user ID 4

Example 49: Top Customers by Orders (Aggregate Functions, Lists)

Fetches top customers by order count into a list for a loyalty report.

Output:

Top 2 customers by orders:
('Diana Smith', 2)
('Eve', 0)

Example 50: Products Not Ordered (Subqueries, Lists)

Fetches products not ordered using a subquery for inventory analysis.

Output:

Products not ordered:
Smartphone
Headphones

Example 51: Create Order Table (Create Table, Classes)

Creates an orders table using a class for database setup.

Output:

Orders table created

Example 52: Insert Order with Date (Insert Data, Dates/Times, Classes)

Inserts an order with a timestamp using a class for an e-commerce checkout system.

Output:

Order added for user 4 on 2025-07-15 12:00:00

Example 53: User Activity Logs (Select Records, Loops, Dates/Times)

Fetches user activity logs within a date range using a loop for an audit system.

Output:

User activity logs:
User 4: Updated at 2025-07-10 14:00:00

Example 54: Encapsulated User Data (Encapsulation, Select Records)

Uses encapsulation in a User class to fetch and protect user data for a profile page.

Output:

User name: Diana Jones

Example 55: Conditional Stock Check (Conditionals, Select Records)

Checks product stock using conditionals for inventory alerts.

Output:

Smartphone has sufficient stock: 50 units

Example 56: Bulk Insert Products (Insert Data, Loops, Lists)

Inserts multiple products using a loop and list for bulk inventory updates.

Output:

Inserted 2 products

Example 57: Update Prices with Discount (Update Records, Loops)

Updates product prices with a discount using a loop for a promotional campaign.

Output:

Applied 10% discount to category 1

Example 58: Order Summary with Join (Join Tables, Dictionaries)

Fetches order summaries with user and product details into a dictionary for a dashboard.

Output:

Order summary:
Tablet: Diana Smith, $499.99
Monitor: Diana Smith, $299.99

Example 59: Total Stock Value (Aggregate Functions, Functions)

Calculates total stock value using SUM for inventory reporting.

Output:

Total stock value: $42999.50

Example 60: Users with No Orders (Subqueries, Tuples)

Fetches users with no orders using a subquery, stored in a tuple for marketing analysis.

Output:

Users with no orders:
Eve
Grace

Example 61: Create Index on Order Date (Indexes, Functions)

Creates an index on order_date to optimize date-based queries.

Output:

Index created on orders.order_date

Example 62: Fetch Cart from View (Views, Lists)

Queries a view for cart items into a list for a shopping cart page.

Output:

Cart items from view:
('Smartphone', 699.99)

Example 63: Log Insert Trigger (Triggers, Insert Data)

Inserts a user, triggering a log entry in user_logs.

Output:

User inserted, log entry created

Example 64: Recent User Activity (Dates/Times, Dictionaries)

Fetches recent user activity into a dictionary for a monitoring dashboard.

Output:

Recent user activity:
User 4: Updated at 2025-07-10 14:00:00

Example 65: Unique Categories Ordered (DISTINCT, Tuples)

Fetches unique categories ordered using DISTINCT into a tuple for analytics.

Output:

Unique ordered categories:
Tech

Example 66: Conditional Order Status (Conditionals, Select Records)

Checks order status based on total using conditionals for an order tracking system.

Output:

Order 1 status: Standard

Example 67: Transactional User Update (Commit/Rollback, Classes)

Updates user details with a transaction using a class for profile management.

Output:

Updated email for user 4

Example 68: Product Search with Regex (Limit/Hint/Auto/Regex/Dedupe, Strings)

Searches products using regex for advanced filtering in a product catalog.

Output:

Products matching '^[A-M].*':
('Headphones', 99.99)
('Laptop', 999.99)
('Mouse', 29.99)

Example 69: Sales by Date Range (Aggregate Functions, Dates/Times)

Calculates total sales within a date range for a financial report.

Output:

Total sales from 2025-07-01 to 2025-07-31: $799.98

Example 70: Create Cart View (Views, Classes)

Creates a view for cart details using a class for a shopping cart system.

Output:

Cart details view created

Example 71: User Order Count with Procedure (Stored Procedures, Functions)

Calls a stored procedure to count user orders for analytics.

Output:

Order count for user 4: 2

Example 72: Delete Old Logs (Delete Records, Dates/Times)

Deletes logs older than 90 days for database cleanup.

Output:

Deleted 0 old logs

Example 73: Product Stock with Join (Join Tables, Lists)

Fetches product stock with category details into a list for inventory management.

Output:

Product stock:
('Smartphone', 50, 'Tech')
('Headphones', 100, 'Tech')
('Tablet', 30, 'Tech')

Example 74: Encapsulated Order Data (Encapsulation, Select Records)

Uses encapsulation to fetch and protect order data for an order details page.

Output:

Order product: Tablet

Example 75: Top Products by Sales (Aggregate Functions, Lists)

Fetches top products by total sales into a list for a sales report.

Output:

Top 2 products by sales:
('Tablet', 499.99)
('Monitor', 299.99)

Example 76: User Orders with Subquery (Subqueries, Dictionaries)

Fetches user orders using a subquery into a dictionary for an order history page.

Output:

User orders count:
Diana Smith: 2 orders
Eve: 0 orders
Grace: 0 orders

Example 77: Update Stock Trigger (Triggers, Update Records)

Updates product stock, triggering a log entry in user_logs.

Output:

Product stock updated, log entry created

Example 78: Orders by Category (Join Tables, Aggregate Functions)

Counts orders by category using a join and COUNT for analytics.

Output:

Orders by category:
Tech: 2 orders

Example 79: Create User Index (Indexes, Classes)

Creates an index on users.name using a class for query optimization.

Output:

Index created on users.name

Example 80: Recent Orders with View (Views, Dates/Times)

Fetches recent orders from a view for a recent activity page.

Output:

Recent orders from view:
('Diana Smith', 'Tablet', '2025-07-01 10:00:00')
('Diana Smith', 'Monitor', '2025-07-10 14:30:00')

Example 81: Bulk Delete Cart Items (Delete Records, Loops)

Deletes multiple cart items using a loop for cart cleanup.

Output:

Deleted cart items for 2 users

Example 82: Product Price Range (Where/Like/Order By, Tuples)

Fetches products within a price range into a tuple for a product filter.

Output:

Products between $50 and $200:
('Headphones', 99.99)

Example 83: Transactional Product Update (Commit/Rollback, Classes)

Updates product details with a transaction using a class for inventory management.

Output:

Updated product ID 1

Example 84: Customer Segmentation (Subqueries, Conditionals)

Segments customers based on order totals using a subquery and conditionals for marketing.

Output:

Diana Smith: VIP
Eve: Regular
Grace: Regular

Example 85: Create Sales Report View (Views, Classes)

Creates a view for sales reports using a class for analytics.

Output:

Sales report view created

Example 86: Update Order with Procedure (Stored Procedures, Classes)

Updates an order using a stored procedure and class for order management.

Output:

Updated order ID 1

Example 87: Recent Logs with Join (Join Tables, Dates/Times)

Fetches recent user logs with user details using a join for audit reporting.

Output:

Recent logs with user details:
('Diana Smith', 'Updated', '2025-07-10 14:00:00')

Example 88: Unique Emails (DISTINCT, Lists)

Fetches unique user emails using DISTINCT into a list for a mailing list.

Output:

Unique emails:
diana_updated@example.com
eve@example.com
grace@example.com

Example 89: Conditional Product Availability (Conditionals, Select Records)

Checks product availability based on stock and cart quantities for a checkout system.

Output:

Smartphone: Available

Example 90: Bulk Update Stock (Update Records, Loops)

Updates stock for multiple products using a loop for inventory restocking.

Output:

Updated stock for 2 products

Example 91: User Cart Total (Aggregate Functions, Join Tables)

Calculates total cart value for a user using a join and SUM for checkout.

Output:

Cart total for user 4: $1399.98

Example 92: Products Not in Cart (Subqueries, Lists)

Fetches products not in any cart using a subquery for product recommendations.

Output:

Products not in cart:
Tablet
Laptop
Mouse

Example 93: Create Discount Trigger (Triggers, Classes)

Creates a trigger to log product discount updates using a class for audit tracking.

Output:

Discount update trigger created

Example 94: Orders by Month (Dates/Times, Aggregate Functions)

Groups orders by month using MONTH and COUNT for monthly sales reports.

Output:

Orders by month:
Month 7: 2 orders

Example 95: Unique Products in Cart (DISTINCT, Lists)

Fetches unique products in carts using DISTINCT for cart analysis.

Output:

Unique products in cart:
Smartphone

Example 96: Conditional Cart Update (Conditionals, Update Records)

Updates cart quantity based on stock availability using conditionals for checkout validation.

Output:

Updated cart quantity for user 4

Example 97: Transactional Order Deletion (Commit/Rollback, Classes)

Deletes an order with a transaction using a class for order cancellation.

Output:

Deleted order ID 2

Example 98: Product Sales with View (Views, Aggregate Functions)

Fetches product sales from a view using SUM for a sales report.

Output:

Sales from view:
Tablet: $499.99
Monitor: $299.99

Example 99: User Orders with Regex (Limit/Hint/Auto/Regex/Dedupe, Join Tables)

Fetches user orders matching a regex pattern for product filtering.

Output:

Orders matching 'Tablet|Phone':
('Diana Smith', 'Tablet')

Example 100: High-Value Orders with Subquery (Subqueries, Dictionaries)

Fetches high-value orders using a subquery into a dictionary for premium customer analysis.

import mysql.connector
def get_high_value_orders(min_total):
    orders = {}
    try:
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",
            database="my_db"
        )
        cursor = connection.cursor()
        query = """
            SELECT u.name, o.product, o.total
            FROM users u
            INNER JOIN orders o ON u.id = o.user_id
            WHERE o.total > (SELECT AVG(total) FROM orders)
        """
        cursor.execute(query)
        for name, product, total in cursor.fetchall():
            orders[product] = {"user": name, "total": float(total)}
        print("High-value orders:")
        for product, details in orders.items():
            print(f"{product}: {details['user']}, ${details['total']}")
        cursor.close()
        connection.close()
        return orders
    except mysql.connector.Error as e:
        print(f"Error: {e}")
        return {}
get_high_value_orders(400)

Output:

High-value orders:
Tablet: Diana Smith, $499.99