Python MySQL - SELECT Statement (E-Commerce Platform)

What You'll Learn Here

E-Commerce SELECT operations — retrieving customer, product, and order data
Python setup — connecting to MySQL from Python
Parameterized queries — secure SQL with %s placeholders
Fetching results — fetchall(), fetchone(), fetchmany()
Real-world examples — product search, customer orders, sales analytics

E-Commerce SELECT Overview

In an E-Commerce platform, SELECT statements are used everywhere:

Product catalog — showing products with filters and search
Customer dashboard — displaying order history
Admin panel — generating sales reports and analytics
Checkout — retrieving customer details and pricing

📊 E-Commerce Database Schema:

customers (customer_id, first_name, last_name, email, city, state)
products (product_id, product_name, category, price, stock_quantity)
orders (order_id, customer_id, order_date, total_amount, status)
order_items (order_item_id, order_id, product_id, quantity, unit_price)
reviews (review_id, customer_id, product_id, rating, comment)

SELECT in E-Commerce

# ============================================================
# E-COMMERCE SELECT QUERIES
# ============================================================

# 1. Show all products
SELECT * FROM products;

# 2. Get product by category
SELECT * FROM products WHERE category = 'Electronics';

# 3. Get customer orders
SELECT * FROM orders WHERE customer_id = 1;

# 4. Get order details with items
SELECT o.order_id, o.order_date, o.total_amount, 
       oi.product_id, oi.quantity, oi.unit_price
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.customer_id = 1;

# 5. Get product reviews with customer names
SELECT r.rating, r.comment, c.first_name, c.last_name
FROM reviews r
JOIN customers c ON r.customer_id = c.customer_id
WHERE r.product_id = 1;

SELECT key points:

SELECT * — retrieves all columns
WHERE — filters results (category, customer)
JOIN — combines related tables
ORDER BY — sorts results

Quick Check: How would you get all orders for customer ID 1? (Answer: SELECT * FROM orders WHERE customer_id = 1)

Python Setup

Connecting to MySQL from Python

# ============================================================
# INSTALLING THE MYSQL CONNECTOR
# ============================================================

pip install mysql-connector-python

# ============================================================
# IMPORT AND CONNECT
# ============================================================

import mysql.connector
from mysql.connector import Error

# ============================================================
# ESTABLISH CONNECTION
# ============================================================

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="secret",
        database="ecommerce_db"  # Our E-Commerce database
    )
    
    if connection.is_connected():
        print("Connected to E-Commerce database successfully!")
        print(f"Server version: {connection.get_server_info()}")
        
except Error as e:
    print(f"Error connecting to MySQL: {e}")
finally:
    if connection.is_connected():
        connection.close()
        print("Connection closed")

# ============================================================
# CONNECTION PARAMETERS
# ============================================================

# Required parameters:
# host     - Where MySQL is running
# user     - MySQL username
# password - MySQL password
# database - The database to use (ecommerce_db)

# Optional parameters:
# port     - MySQL port (default: 3306)
# charset  - Character set (default: utf8mb4)

Setup key points:

pip install — install the connector
connect() — establish connection
is_connected() — check connection status
Always close — use try/finally or with statement

Quick Check: What module do you import to work with MySQL in Python? (Answer: mysql.connector)

Basic SELECT in Python

Executing SELECT Statements from Python

# ============================================================
# BASIC SELECT IN PYTHON - E-COMMERCE
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. GET ALL PRODUCTS
# ============================================================

cursor.execute("SELECT * FROM products")
products = cursor.fetchall()

print("All Products:")
for p in products:
    print(f"  {p[1]} - ${p[4]} (Stock: {p[5]})")

# ============================================================
# 2. GET ELECTRONICS PRODUCTS
# ============================================================

cursor.execute("SELECT * FROM products WHERE category = 'Electronics'")
electronics = cursor.fetchall()

print("\nElectronics Products:")
for p in electronics:
    print(f"  {p[1]} - ${p[4]}")

# ============================================================
# 3. GET CUSTOMER ORDERS
# ============================================================

cursor.execute("SELECT * FROM orders WHERE customer_id = 1")
orders = cursor.fetchall()

print("\nOrders for Customer 1:")
for o in orders:
    print(f"  Order {o[0]} - ${o[3]} - Status: {o[5]}")

# ============================================================
# 4. GET TOP SELLING PRODUCTS
# ============================================================

cursor.execute("""
    SELECT p.product_name, SUM(oi.quantity) as total_sold
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id
    ORDER BY total_sold DESC
    LIMIT 5
""")
top_products = cursor.fetchall()

print("\nTop Selling Products:")
for p in top_products:
    print(f"  {p[0]} - {p[1]} units sold")

cursor.close()
connection.close()

Python SELECT basics:

cursor.execute() — runs the SQL query
cursor.fetchall() — gets all results
Results as tuples — each row is a tuple
Column order — matches SELECT order

Quick Check: What method is used to execute a SQL query in Python? (Answer: cursor.execute())

Parameterized Queries

Secure SELECT Queries with %s Placeholders

⚠️ NEVER use string concatenation for SQL queries!

Always use parameterized queries with %s placeholders to prevent SQL injection attacks.

# ============================================================
# UNSAFE WAY - NEVER DO THIS!
# ============================================================

# ❌ VULNERABLE TO SQL INJECTION
category = input("Enter category: ")
cursor.execute(f"SELECT * FROM products WHERE category = '{category}'")

# ============================================================
# SAFE WAY - USE PARAMETERIZED QUERIES!
# ============================================================

# ✅ SECURE - Use %s placeholders
category = input("Enter category: ")
cursor.execute("SELECT * FROM products WHERE category = %s", (category,))

# ============================================================
# E-COMMERCE PARAMETERIZED SELECT QUERIES
# ============================================================

# 1. Get products by category
cursor.execute("SELECT * FROM products WHERE category = %s", ("Electronics",))
products = cursor.fetchall()

# 2. Get orders by customer
cursor.execute("SELECT * FROM orders WHERE customer_id = %s", (1,))
orders = cursor.fetchall()

# 3. Get products with price range
cursor.execute(
    "SELECT * FROM products WHERE price BETWEEN %s AND %s",
    (100, 500)
)
products = cursor.fetchall()

# 4. Get products with search
search = "phone"
cursor.execute(
    "SELECT * FROM products WHERE product_name LIKE %s",
    (f"%{search}%",)
)
products = cursor.fetchall()

# 5. Get multiple customers
ids = (1, 3, 5)
placeholder = ', '.join(['%s'] * len(ids))
cursor.execute(
    f"SELECT * FROM customers WHERE customer_id IN ({placeholder})",
    ids
)
customers = cursor.fetchall()

Parameterized queries key points:

%s — placeholder for values
Tuple — pass parameters as a tuple
Security — prevents SQL injection
Performance — query plan can be cached

Quick Check: What is the safe way to include user input in SQL queries? (Answer: Use parameterized queries with %s placeholders)

Fetching Results in Python

Methods to Retrieve Data

# ============================================================
# FETCH METHODS - E-COMMERCE
# ============================================================

cursor.execute("SELECT * FROM products")

# ============================================================
# 1. fetchall() - Get ALL rows
# ============================================================

all_products = cursor.fetchall()
print(f"All products: {len(all_products)}")
for p in all_products:
    print(f"  {p[1]} - ${p[4]}")

# ============================================================
# 2. fetchone() - Get ONE row at a time
# ============================================================

cursor.execute("SELECT * FROM products")
first_product = cursor.fetchone()
print(f"\nFirst product: {first_product[1]} - ${first_product[4]}")

second_product = cursor.fetchone()
print(f"Second product: {second_product[1]} - ${second_product[4]}")

# ============================================================
# 3. fetchmany() - Get rows in BATCHES
# ============================================================

cursor.execute("SELECT * FROM products")
batch_size = 3

print("\nFetching products in batches:")
while True:
    batch = cursor.fetchmany(batch_size)
    if not batch:
        break
    print(f"  Batch: {len(batch)} products")
    for p in batch:
        print(f"    {p[1]} - ${p[4]}")

# ============================================================
# 4. Looping with fetchone()
# ============================================================

cursor.execute("SELECT * FROM customers")
print("\nCustomers:")
while True:
    row = cursor.fetchone()
    if row is None:
        break
    print(f"  {row[1]} {row[2]} - {row[3]}")

Fetch methods comparison:

fetchall() — all rows at once (small datasets)
fetchone() — one row at a time (memory efficient)
fetchmany(n) — n rows at a time (balanced)
fetchone() returns None — when no more rows

Quick Check: Which fetch method is best for very large datasets? (Answer: fetchone() or fetchmany() for memory efficiency)

Using WHERE with Python

Filtering E-Commerce Data from Python

# ============================================================
# WHERE CLAUSE IN PYTHON - E-COMMERCE
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. EQUALITY - Find products by category
# ============================================================

cursor.execute("SELECT * FROM products WHERE category = %s", ("Electronics",))
products = cursor.fetchall()
print("Electronics Products:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# ============================================================
# 2. COMPARISON OPERATORS - Products by price
# ============================================================

# Products under $100
cursor.execute("SELECT * FROM products WHERE price < %s", (100,))
products = cursor.fetchall()
print("\nProducts under $100:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# Products between $50 and $200
cursor.execute("SELECT * FROM products WHERE price BETWEEN %s AND %s", (50, 200))
products = cursor.fetchall()
print("\nProducts between $50 and $200:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# ============================================================
# 3. LIKE PATTERN MATCHING - Product search
# ============================================================

# Search products containing "Phone"
cursor.execute("SELECT * FROM products WHERE product_name LIKE %s", ("%Phone%",))
products = cursor.fetchall()
print("\nProducts containing 'Phone':")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# ============================================================
# 4. MULTIPLE CONDITIONS (AND) - Filter by category and price
# ============================================================

cursor.execute(
    "SELECT * FROM products WHERE category = %s AND price < %s",
    ("Electronics", 500)
)
products = cursor.fetchall()
print("\nElectronics under $500:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# ============================================================
# 5. IN OPERATOR - Get customers from specific cities
# ============================================================

cities = ('Mumbai', 'Delhi', 'Bangalore')
placeholder = ', '.join(['%s'] * len(cities))
cursor.execute(
    f"SELECT * FROM customers WHERE city IN ({placeholder})",
    cities
)
customers = cursor.fetchall()
print("\nCustomers from Mumbai, Delhi, or Bangalore:")
for c in customers:
    print(f"  {c[1]} {c[2]} - {c[4]}, {c[5]}")

cursor.close()
connection.close()

WHERE clause operators:

= — equals
>, <, >=, <= — comparisons
BETWEEN — range
LIKE — pattern matching (% for wildcard)
IN — matches any value in a list
AND, OR — multiple conditions

Quick Check: What does LIKE '%Phone%' find? (Answer: Products containing the word 'Phone' anywhere in the name)

E-Commerce Python Examples

Building E-Commerce Features in Python

# ============================================================
# E-COMMERCE FEATURES IN PYTHON
# ============================================================

import mysql.connector
from mysql.connector import Error

class ECommercePlatform:
    """E-Commerce platform with SELECT operations"""
    
    def __init__(self, db_config):
        self.db_config = db_config
        self.connection = None
        self.cursor = None
    
    def connect(self):
        try:
            self.connection = mysql.connector.connect(**self.db_config)
            self.cursor = self.connection.cursor()
            return True
        except Error as e:
            print(f"Connection failed: {e}")
            return False
    
    # ============================================================
    # PRODUCT FEATURES
    # ============================================================
    
    def get_products_by_category(self, category):
        """Get all products in a category"""
        query = "SELECT * FROM products WHERE category = %s"
        self.cursor.execute(query, (category,))
        return self.cursor.fetchall()
    
    def search_products(self, search_term):
        """Search products by name"""
        query = "SELECT * FROM products WHERE product_name LIKE %s"
        self.cursor.execute(query, (f"%{search_term}%",))
        return self.cursor.fetchall()
    
    def get_products_by_price_range(self, min_price, max_price):
        """Get products in a price range"""
        query = "SELECT * FROM products WHERE price BETWEEN %s AND %s"
        self.cursor.execute(query, (min_price, max_price))
        return self.cursor.fetchall()
    
    def get_best_sellers(self, limit=5):
        """Get top selling products"""
        query = """
            SELECT p.product_id, p.product_name, p.price, 
                   SUM(oi.quantity) as total_sold
            FROM products p
            JOIN order_items oi ON p.product_id = oi.product_id
            GROUP BY p.product_id
            ORDER BY total_sold DESC
            LIMIT %s
        """
        self.cursor.execute(query, (limit,))
        return self.cursor.fetchall()
    
    # ============================================================
    # CUSTOMER FEATURES
    # ============================================================
    
    def get_customer_orders(self, customer_id):
        """Get all orders for a customer"""
        query = "SELECT * FROM orders WHERE customer_id = %s ORDER BY order_date DESC"
        self.cursor.execute(query, (customer_id,))
        return self.cursor.fetchall()
    
    def get_customer_order_details(self, customer_id):
        """Get detailed order information for a customer"""
        query = """
            SELECT o.order_id, o.order_date, o.total_amount, o.status,
                   p.product_name, oi.quantity, oi.unit_price,
                   (oi.quantity * oi.unit_price) as item_total
            FROM orders o
            JOIN order_items oi ON o.order_id = oi.order_id
            JOIN products p ON oi.product_id = p.product_id
            WHERE o.customer_id = %s
            ORDER BY o.order_date DESC
        """
        self.cursor.execute(query, (customer_id,))
        return self.cursor.fetchall()
    
    def get_customers_by_city(self, city):
        """Get all customers in a city"""
        query = "SELECT * FROM customers WHERE city = %s"
        self.cursor.execute(query, (city,))
        return self.cursor.fetchall()
    
    # ============================================================
    # REVIEW FEATURES
    # ============================================================
    
    def get_product_reviews(self, product_id):
        """Get all reviews for a product"""
        query = """
            SELECT r.rating, r.comment, c.first_name, c.last_name, r.review_date
            FROM reviews r
            JOIN customers c ON r.customer_id = c.customer_id
            WHERE r.product_id = %s
            ORDER BY r.review_date DESC
        """
        self.cursor.execute(query, (product_id,))
        return self.cursor.fetchall()
    
    def get_average_rating(self, product_id):
        """Get average rating for a product"""
        query = "SELECT AVG(rating) FROM reviews WHERE product_id = %s"
        self.cursor.execute(query, (product_id,))
        return self.cursor.fetchone()[0]
    
    # ============================================================
    # ANALYTICS FEATURES
    # ============================================================
    
    def get_sales_by_category(self):
        """Get sales grouped by category"""
        query = """
            SELECT p.category, 
                   COUNT(DISTINCT o.order_id) as order_count,
                   SUM(oi.quantity) as units_sold,
                   SUM(oi.quantity * oi.unit_price) as total_revenue
            FROM products p
            JOIN order_items oi ON p.product_id = oi.product_id
            JOIN orders o ON oi.order_id = o.order_id
            WHERE o.status = 'completed'
            GROUP BY p.category
            ORDER BY total_revenue DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_customer_lifetime_value(self, customer_id):
        """Get total amount spent by a customer"""
        query = """
            SELECT SUM(total_amount) as total_spent,
                   COUNT(order_id) as order_count,
                   AVG(total_amount) as avg_order_value
            FROM orders
            WHERE customer_id = %s AND status = 'completed'
        """
        self.cursor.execute(query, (customer_id,))
        return self.cursor.fetchone()
    
    def get_daily_sales(self, days=30):
        """Get sales for the last N days"""
        query = """
            SELECT DATE(order_date) as sale_date,
                   COUNT(order_id) as order_count,
                   SUM(total_amount) as daily_revenue
            FROM orders
            WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL %s DAY)
            AND status = 'completed'
            GROUP BY DATE(order_date)
            ORDER BY sale_date DESC
        """
        self.cursor.execute(query, (days,))
        return self.cursor.fetchall()
    
    # ============================================================
    # DISPLAY HELPERS
    # ============================================================
    
    def display_products(self, products, title="Products"):
        """Display product list"""
        if not products:
            print(f"\n{title}: No products found")
            return
        print(f"\n{title}: {len(products)} products")
        print("-" * 60)
        for p in products:
            print(f"  {p[1]} - ${p[4]} (Stock: {p[5]})")
    
    def display_orders(self, orders, title="Orders"):
        """Display order list"""
        if not orders:
            print(f"\n{title}: No orders found")
            return
        print(f"\n{title}: {len(orders)} orders")
        print("-" * 60)
        for o in orders:
            print(f"  Order #{o[0]} - ${o[3]} - {o[5]} - {o[2]}")
    
    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

# ============================================================
# DEMONSTRATION
# ============================================================

db_config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "ecommerce_db"
}

store = ECommercePlatform(db_config)

if store.connect():
    
    print("=" * 60)
    print("E-COMMERCE PLATFORM DEMONSTRATION")
    print("=" * 60)
    
    # 1. Product search
    print("\n1. SEARCHING PRODUCTS")
    products = store.search_products("Phone")
    store.display_products(products, "Products containing 'Phone'")
    
    # 2. Category products
    print("\n2. CATEGORY PRODUCTS")
    products = store.get_products_by_category("Electronics")
    store.display_products(products, "Electronics Products")
    
    # 3. Price range
    print("\n3. PRICE RANGE")
    products = store.get_products_by_price_range(100, 300)
    store.display_products(products, "Products $100-$300")
    
    # 4. Best sellers
    print("\n4. BEST SELLERS")
    top = store.get_best_sellers(3)
    print("Top Selling Products:")
    for p in top:
        print(f"  {p[1]} - ${p[2]} - {p[3]} units sold")
    
    # 5. Customer orders
    print("\n5. CUSTOMER ORDERS")
    orders = store.get_customer_orders(1)
    store.display_orders(orders, "Customer 1 Orders")
    
    # 6. Sales by category
    print("\n6. SALES BY CATEGORY")
    sales = store.get_sales_by_category()
    print("Category Sales:")
    for s in sales:
        print(f"  {s[0]}: {s[1]} orders, {s[2]} units, ${s[3]:.2f} revenue")
    
    # 7. Product reviews
    print("\n7. PRODUCT REVIEWS")
    reviews = store.get_product_reviews(1)
    print(f"Reviews for Product ID 1:")
    for r in reviews:
        print(f"  {'⭐' * r[0]} - {r[1][:50]}... - {r[2]} {r[3]}")
    
    store.close()

This E-Commerce example shows:

Product search and filtering
Customer order history
Sales analytics and reporting
Product reviews and ratings
Best sellers and category performance
Customer lifetime value

Quick Check: What makes this a "Python" tutorial rather than a "MySQL" tutorial? (Answer: The focus is on executing queries from Python, handling results in Python, and building Python applications)

Python Best Practices

Best Practices for Python-MySQL SELECT

# ============================================================
# PYTHON BEST PRACTICES FOR SELECT
# ============================================================

print("1. USE PARAMETERIZED QUERIES")
print("   - Always use %s placeholders for user input")
print("   - Never use string concatenation")
print("   - Protects against SQL injection")

print("\n2. USE CONTEXT MANAGERS")
print("   - Use 'with' statements for connections")
print("   - Automatic cleanup")

print("\n3. SELECT ONLY NEEDED COLUMNS")
print("   - Use specific columns instead of SELECT *")
print("   - Reduces data transfer")

print("\n4. USE FETCH METHODS WISELY")
print("   - fetchall() for small datasets")
print("   - fetchmany() for large datasets")
print("   - fetchone() for single rows")

print("\n5. HANDLE ERRORS PROPERLY")
print("   - Use try/except blocks")
print("   - Log errors for debugging")

print("\n6. CLOSE CONNECTIONS")
print("   - Always close cursor and connection")
print("   - Use try/finally or context managers")

print("\n7. USE ENVIRONMENT VARIABLES")
print("   - Store credentials in environment variables")
print("   - Never hardcode passwords")

print("\n8. ADD TIMEOUTS")
print("   - Set connection_timeout for queries")
print("   - Prevent hanging on slow queries")

Summary of best practices:

Parameterized queries — prevent SQL injection
Context managers — automatic cleanup
Specific columns — reduce data transfer
Right fetch method — based on dataset size
Error handling — robust application
Close connections — prevent leaks

Quick Check: What is the most important Python best practice for SELECT queries? (Answer: Always use parameterized queries)

Try It Yourself

Experiment with E-Commerce SELECT queries in the editor below.

Loading Pyodide... 0%

Python Code Editor

# Python MySQL SELECT - E-Commerce Practice

print("=" * 40)
print("E-COMMERCE SELECT - PRACTICE")
print("=" * 40)

# Simulated E-Commerce Data
class ECommerceDB:
    products = [
        {"id": 1, "name": "iPhone 15 Pro", "category": "Electronics", "price": 999.99, "stock": 50},
        {"id": 2, "name": "MacBook Air M3", "category": "Electronics", "price": 1099.99, "stock": 20},
        {"id": 3, "name": "Nike Air Max", "category": "Footwear", "price": 149.99, "stock": 100},
        {"id": 4, "name": "Ray-Ban Sunglasses", "category": "Accessories", "price": 199.99, "stock": 60},
        {"id": 5, "name": "Apple Watch", "category": "Electronics", "price": 399.99, "stock": 40},
        {"id": 6, "name": "Sony Headphones", "category": "Electronics", "price": 299.99, "stock": 45},
        {"id": 7, "name": "Levi's Jeans", "category": "Clothing", "price": 69.99, "stock": 150},
        {"id": 8, "name": "Nike T-Shirt", "category": "Clothing", "price": 29.99, "stock": 200}
    ]
    
    customers = [
        {"id": 1, "first_name": "Rahul", "last_name": "Sharma", "city": "Mumbai"},
        {"id": 2, "first_name": "Priya", "last_name": "Patel", "city": "Delhi"},
        {"id": 3, "first_name": "Amit", "last_name": "Singh", "city": "Bangalore"},
        {"id": 4, "first_name": "Sneha", "last_name": "Reddy", "city": "Hyderabad"},
        {"id": 5, "first_name": "Vikram", "last_name": "Kumar", "city": "Chennai"}
    ]
    
    orders = [
        {"id": 1, "customer_id": 1, "total": 1099.98, "status": "completed"},
        {"id": 2, "customer_id": 2, "total": 149.99, "status": "pending"},
        {"id": 3, "customer_id": 3, "total": 899.99, "status": "processing"},
        {"id": 4, "customer_id": 1, "total": 69.99, "status": "completed"},
        {"id": 5, "customer_id": 4, "total": 399.99, "status": "completed"}
    ]
    
    @classmethod
    def select_products(cls, category=None, min_price=None, max_price=None, search=None):
        """Simulate SELECT with filters"""
        results = cls.products[:]
        
        if category:
            results = [p for p in results if p["category"] == category]
        if min_price:
            results = [p for p in results if p["price"] >= min_price]
        if max_price:
            results = [p for p in results if p["price"] <= max_price]
        if search:
            results = [p for p in results if search.lower() in p["name"].lower()]
        
        return results

print("\n1. ALL PRODUCTS")
products = ECommerceDB.select_products()
for p in products:
    print(f"  {p['name']} - ${p['price']} ({p['category']})")

print("\n2. ELECTRONICS PRODUCTS")
products = ECommerceDB.select_products(category="Electronics")
for p in products:
    print(f"  {p['name']} - ${p['price']}")

print("\n3. PRODUCTS UNDER $200")
products = ECommerceDB.select_products(max_price=200)
for p in products:
    print(f"  {p['name']} - ${p['price']}")

print("\n4. SEARCH: 'Phone'")
products = ECommerceDB.select_products(search="Phone")
for p in products:
    print(f"  {p['name']} - ${p['price']}")

print("\n5. SEARCH: 'Nike'")
products = ECommerceDB.select_products(search="Nike")
for p in products:
    print(f"  {p['name']} - ${p['price']}")

print("\n✅ SELECT queries are powerful for E-Commerce applications!")

========================================
E-COMMERCE SELECT - PRACTICE
========================================

1. ALL PRODUCTS
iPhone 15 Pro - $999.99 (Electronics)
MacBook Air M3 - $1099.99 (Electronics)
Nike Air Max - $149.99 (Footwear)
Ray-Ban Sunglasses - $199.99 (Accessories)
Apple Watch - $399.99 (Electronics)
Sony Headphones - $299.99 (Electronics)
Levi's Jeans - $69.99 (Clothing)
Nike T-Shirt - $29.99 (Clothing)

2. ELECTRONICS PRODUCTS
iPhone 15 Pro - $999.99
MacBook Air M3 - $1099.99
Apple Watch - $399.99
Sony Headphones - $299.99

3. PRODUCTS UNDER $200
Nike Air Max - $149.99
Ray-Ban Sunglasses - $199.99
Levi's Jeans - $69.99
Nike T-Shirt - $29.99

4. SEARCH: 'Phone'
iPhone 15 Pro - $999.99

5. SEARCH: 'Nike'
Nike Air Max - $149.99
Nike T-Shirt - $29.99

SELECT queries are powerful for E-Commerce applications!

🏆

You've Got It!

You now know how to execute SELECT statements in MySQL using Python with an E-Commerce case study. You can build real-world applications!

Quick Quiz

Test what you've learned:

1. How do you execute a SELECT query from Python?

A) cursor.query() B) cursor.execute() C) cursor.run() D) cursor.select()

2. What is the safe way to include user input in a SELECT query?

A) String concatenation B) Parameterized queries with %s C) f-strings D) Plus operator

3. Which method returns ALL rows as a list?

A) cursor.fetchone() B) cursor.fetchall() C) cursor.fetchmany() D) cursor.getall()

4. In an E-Commerce platform, how would you get products under $100?

A) SELECT * FROM products WHERE price > 100 B) SELECT * FROM products WHERE price < 100 C) SELECT * FROM products WHERE price = 100 D) SELECT * FROM products WHERE price BETWEEN 0 AND 100

5. What is the best fetch method for large datasets?

A) fetchall() B) fetchmany() C) fetchone() D) They are all the same

Frequently Asked Questions

What makes this a Python tutorial rather than a MySQL tutorial?

This tutorial focuses on executing SELECT statements from Python, handling results in Python, and building Python applications. Pure MySQL tutorials focus on SQL syntax, optimization, and database administration.

Why use an E-Commerce case study?

E-Commerce is a high-demand, real-world example that everyone understands. It makes learning more practical and relevant to actual job requirements.

What is the difference between this and a pure MySQL tutorial?

This tutorial: Python code, mysql-connector, result handling, Python best practices. Pure MySQL: SQL syntax, query optimization, indexing, database administration.

What is a common interview question about Python-MySQL SELECT?

Common questions include: "How do you execute a SELECT query from Python?" "How do you prevent SQL injection in Python?" "What is the difference between fetchall() and fetchmany()?"

Will this conflict with future pure MySQL tutorials?

No. This tutorial has a Python focus with code examples. Future MySQL tutorials will have a SQL focus with syntax and optimization. Different titles, URLs, and audiences prevent SEO conflict.

Where to Go From Here

Now that you understand SELECT statements in Python, check out these related topics:

Previous: Connecting to MySQL Next: MySQL Operators →

Python

Python: SELECT Statement in MySQL (E-Commerce)

👥 Customers

📦 Products

📋 Orders

⭐ Reviews

📊 Analytics

🚀 Python

E-Commerce SELECT Overview

SELECT in E-Commerce

Python Setup

Connecting to MySQL from Python

Basic SELECT in Python

Executing SELECT Statements from Python

Parameterized Queries

Secure SELECT Queries with %s Placeholders

Fetching Results in Python

Methods to Retrieve Data

Using WHERE with Python

Filtering E-Commerce Data from Python

E-Commerce Python Examples

Building E-Commerce Features in Python

Python Best Practices

Best Practices for Python-MySQL SELECT

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What makes this a Python tutorial rather than a MySQL tutorial?

Why use an E-Commerce case study?

What is the difference between this and a pure MySQL tutorial?

What is a common interview question about Python-MySQL SELECT?

Will this conflict with future pure MySQL tutorials?

Where to Go From Here

MySQL Operators

DDL Statements

JOIN Operations

Python

Python: SELECT Statement in MySQL (E-Commerce)

👥 Customers

📦 Products

📋 Orders

⭐ Reviews

📊 Analytics

🚀 Python

E-Commerce SELECT Overview

SELECT in E-Commerce

Python Setup

Connecting to MySQL from Python

Basic SELECT in Python

Executing SELECT Statements from Python

Parameterized Queries

Secure SELECT Queries with %s Placeholders

Fetching Results in Python

Methods to Retrieve Data

Using WHERE with Python

Filtering E-Commerce Data from Python

E-Commerce Python Examples

Building E-Commerce Features in Python

Python Best Practices

Best Practices for Python-MySQL SELECT

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What makes this a Python tutorial rather than a MySQL tutorial? ▼

Why use an E-Commerce case study? ▼

What is the difference between this and a pure MySQL tutorial? ▼

What is a common interview question about Python-MySQL SELECT? ▼

Will this conflict with future pure MySQL tutorials? ▼

Where to Go From Here

MySQL Operators

DDL Statements

JOIN Operations

What makes this a Python tutorial rather than a MySQL tutorial?

Why use an E-Commerce case study?

What is the difference between this and a pure MySQL tutorial?

What is a common interview question about Python-MySQL SELECT?

Will this conflict with future pure MySQL tutorials?