Python: Introduction to MySQL with Python

# Why Use MySQL with Python?

print("=" * 50)
print("WHY USE MYSQL WITH PYTHON?")
print("=" * 50)

# ============================================================
# THE POWER OF PYTHON + MYSQL
# ============================================================

print("\n1. WHY THIS COMBINATION WORKS")

print("""
┌─────────────────────────────────────────────────────────────────────┐
│ WHY PYTHON + MYSQL IS A GREAT CHOICE                               │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  PYTHON:                                                           │
│  • Easy to learn and use                                           │
│  • Huge ecosystem of libraries                                     │
│  • Great for web development, data science, automation             │
│  • Active community                                                │
│                                                                     │
│  MYSQL:                                                            │
│  • Reliable and proven (used by major companies)                   │
│  • Free and open-source                                            │
│  • Fast and efficient                                              │
│  • Works on all platforms (Windows, Mac, Linux)                    │
│                                                                     │
│  TOGETHER:                                                         │
│  • Build powerful web applications                                 │
│  • Analyze large datasets                                          │
│  • Automate data processing                                        │
│  • Store and retrieve data efficiently                             │
└─────────────────────────────────────────────────────────────────────┘
""")


# ============================================================
# REAL-WORLD USE CASES
# ============================================================

print("\n2. REAL-WORLD USE CASES")

print("""
┌─────────────────────┬─────────────────────────────────────────────────────┐
│ USE CASE            │ EXAMPLE                                            │
├─────────────────────┼─────────────────────────────────────────────────────┤
│ Web Applications    │ Django, Flask, FastAPI apps with user data        │
│ E-commerce          │ Product catalogs, orders, customers                │
│ Data Analysis       │ Analyzing sales data, customer behavior            │
│ Automation          │ Automated reports, data pipelines                 │
│ Content Management  │ Blogs, CMS systems with articles and users         │
│ APIs                │ REST APIs that serve data to frontend              │
│ Business Apps       │ Inventory management, CRM, ERP                    │
└─────────────────────┴─────────────────────────────────────────────────────┘
""")

print("\n   Python + MySQL is used in production by:")
print("   • Thousands of startups and companies")
print("   • Data-driven applications")
print("   • Web applications (Django, Flask)")
print("   • Data analysis and reporting tools")

# How Python Talks to MySQL

print("=" * 50)
print("HOW PYTHON TALKS TO MYSQL")
print("=" * 50)

# ============================================================
# THE BIG PICTURE
# ============================================================

print("\n1. THE BIG PICTURE")

print("""
┌─────────────────────────────────────────────────────────────────────┐
│ HOW IT WORKS                                                       │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  1. Python connects to MySQL using a driver (connector)            │
│  2. Python sends SQL queries to MySQL                              │
│  3. MySQL executes the queries                                      │
│  4. MySQL sends results back to Python                             │
│  5. Python processes the results                                   │
│                                                                     │
│  Python ---------> MySQL Driver ---------> MySQL Database          │
│           SQL Query                      Execute                    │
│                                                                     │
│  Python <--------- MySQL Driver <--------- MySQL Database          │
│           Results                       Results                    │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘
""")


# ============================================================
# POPULAR MYSQL DRIVERS FOR PYTHON
# ============================================================

print("\n2. POPULAR MYSQL DRIVERS FOR PYTHON")

print("""
┌─────────────────────────────┬─────────────────────────────────────────────┐
│ DRIVER                      │ DESCRIPTION                                │
├─────────────────────────────┼─────────────────────────────────────────────┤
│ mysql-connector-python      │ Official MySQL driver (recommended)        │
│ (mysql.connector)           │ Pure Python, no external dependencies      │
│                             │                                             │
│ PyMySQL                     │ Popular third-party driver                 │
│                             │ Lightweight, pure Python                   │
│                             │                                             │
│ mysqlclient                 │ Fast C-based driver                        │
│                             │ Faster than pure Python drivers            │
│                             │                                             │
│ SQLAlchemy                  │ ORM (Object Relational Mapper)             │
│                             │ Higher-level abstraction                   │
│                             │ Works with multiple databases              │
└─────────────────────────────┴─────────────────────────────────────────────┘

Installing mysql-connector-python:
    pip install mysql-connector-python

Installing PyMySQL:
    pip install pymysql
""")


# ============================================================
# BASIC CONNECTION PROCESS
# ============================================================

print("\n3. BASIC CONNECTION PROCESS")

print("""
import mysql.connector

# 1. Create a connection
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# 2. Create a cursor (the tool that sends queries)
cursor = connection.cursor()

# 3. Send a query
cursor.execute("SELECT * FROM users")

# 4. Get the results
results = cursor.fetchall()

# 5. Process the results
for row in results:
    print(row)

# 6. Close the connection
cursor.close()
connection.close()
""")

print("   This is the basic pattern you'll use for all database operations!")


# ============================================================
# THE CURSOR EXPLAINED
# ============================================================

print("\n4. THE CURSOR EXPLAINED")

print("""
A cursor is like a pointer that moves through the result set.

Think of it like reading a book:
- The cursor is your finger pointing to the current line
- fetchone() reads one row and moves the finger down
- fetchall() reads all remaining rows

Methods:
    cursor.execute(query)     - Send a query
    cursor.fetchone()         - Get one row
    cursor.fetchall()         - Get all rows
    cursor.fetchmany(n)       - Get n rows
    cursor.close()            - Close the cursor
""")

# What You Can Do with Python and MySQL

print("=" * 50)
print("WHAT YOU CAN DO WITH PYTHON AND MYSQL")
print("=" * 50)

# ============================================================
# WEB APPLICATIONS
# ============================================================

print("\n1. WEB APPLICATIONS")

print("""
Build dynamic websites with user accounts, data, and content:

    • User registration and login
    • Profile management
    • Blog posts and comments
    • Product catalogs
    • Shopping carts and orders
    • Content management systems

Frameworks that work great with MySQL:
    • Django (includes ORM)
    • Flask (with SQLAlchemy or MySQL connector)
    • FastAPI (with SQLAlchemy)
""")


# ============================================================
# DATA ANALYSIS AND REPORTING
# ============================================================

print("\n2. DATA ANALYSIS AND REPORTING")

print("""
Analyze data stored in MySQL using Python's data tools:

    • Generate reports and dashboards
    • Analyze sales and customer data
    • Create data visualizations
    • Build data pipelines
    • Export data to CSV, Excel, JSON

Tools you can use:
    • Pandas (read SQL queries)
    • Matplotlib and Seaborn (visualizations)
    • Jupyter Notebooks (interactive analysis)
""")


# ============================================================
# AUTOMATION AND TOOLS
# ============================================================

print("\n3. AUTOMATION AND TOOLS")

print("""
Automate tasks that involve database operations:

    • Automated backups
    • Data migration scripts
    • Scheduled reports
    • Data cleaning and validation
    • ETL (Extract, Transform, Load) pipelines

Example:
    - Export data from MySQL to CSV every day at midnight
    - Clean and transform data
    - Send reports via email
""")


# ============================================================
# API DEVELOPMENT
# ============================================================

print("\n4. API DEVELOPMENT")

print("""
Build REST APIs that serve data from MySQL:

    • CRUD operations (Create, Read, Update, Delete)
    • Data validation and security
    • Authentication and authorization
    • Serve data to web and mobile apps

Framework examples:
    • Flask-RESTful
    • FastAPI
    • Django REST Framework
""")


# ============================================================
# WHAT YOU'LL LEARN IN THIS SERIES
# ============================================================

print("\n5. WHAT YOU'LL LEARN IN THIS SERIES")

print("""
┌─────────────────────────────────────────────────────────────────────┐
│ IN THIS MYSQL WITH PYTHON SERIES                                   │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  • DBMS vs File System (Understanding the difference)              │
│  • Connecting to MySQL (Making the connection)                    │
│  • Creating Databases (Creating and managing databases)            │
│  • Creating Tables (Defining table structures)                    │
│  • Inserting Data (Adding records)                                │
│  • Selecting Data (Querying and retrieving data)                  │
│  • WHERE Clause (Filtering results)                               │
│  • Updating Data (Modifying records)                              │
│  • Deleting Data (Removing records)                               │
│  • Parameterized Queries (Preventing SQL injection)               │
│  • Transactions (ACID operations)                                 │
│  • Error Handling (Handling database errors)                      │
│  • Joins (Combining tables)                                       │
│  • Aggregation (Grouping and summarizing)                         │
│  • Best Practices (Writing clean database code)                   │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘
""")

# Real-World Example: User Management System

print("=" * 60)
print("USER MANAGEMENT SYSTEM")
print("=" * 60)

# This is a simulation of what you can build with Python and MySQL
# We'll use Python lists to simulate database tables

# ============================================================
# SIMULATED DATABASE TABLES
# ============================================================

# Simulating a 'users' table
users_table = [
    {"id": 1, "username": "alice", "email": "alice@example.com", "age": 30},
    {"id": 2, "username": "bob", "email": "bob@example.com", "age": 25},
    {"id": 3, "username": "charlie", "email": "charlie@example.com", "age": 35}
]

# Simulating a 'products' table
products_table = [
    {"id": 1, "name": "Laptop", "price": 999.99, "stock": 5},
    {"id": 2, "name": "Phone", "price": 699.99, "stock": 10},
    {"id": 3, "name": "Headphones", "price": 149.99, "stock": 20}
]

# Simulating an 'orders' table
orders_table = []


# ============================================================
# DATABASE OPERATIONS (Simulated)
# ============================================================

class DatabaseSimulator:
    """Simulate database operations"""
    
    def __init__(self):
        self.users = users_table.copy()
        self.products = products_table.copy()
        self.orders = orders_table.copy()
        self.next_user_id = len(users_table) + 1
        self.next_order_id = 1
    
    def get_all_users(self):
        """SELECT * FROM users"""
        print("   📋 Fetching all users...")
        return self.users
    
    def get_user_by_id(self, user_id):
        """SELECT * FROM users WHERE id = ?"""
        print(f"   🔍 Finding user with ID {user_id}...")
        for user in self.users:
            if user["id"] == user_id:
                return user
        return None
    
    def add_user(self, username, email, age):
        """INSERT INTO users (username, email, age) VALUES (?, ?, ?)"""
        print(f"   ➕ Adding user: {username}...")
        new_user = {
            "id": self.next_user_id,
            "username": username,
            "email": email,
            "age": age
        }
        self.users.append(new_user)
        self.next_user_id += 1
        return new_user
    
    def update_user(self, user_id, **kwargs):
        """UPDATE users SET ... WHERE id = ?"""
        print(f"   ✏️ Updating user {user_id}...")
        for user in self.users:
            if user["id"] == user_id:
                for key, value in kwargs.items():
                    if key in user:
                        user[key] = value
                return user
        return None
    
    def delete_user(self, user_id):
        """DELETE FROM users WHERE id = ?"""
        print(f"   🗑️ Deleting user {user_id}...")
        for i, user in enumerate(self.users):
            if user["id"] == user_id:
                del self.users[i]
                return True
        return False
    
    def create_order(self, user_id, product_id, quantity):
        """INSERT INTO orders (user_id, product_id, quantity) VALUES (?, ?, ?)"""
        print(f"   📦 Creating order for user {user_id}...")
        
        # Find the product
        product = None
        for p in self.products:
            if p["id"] == product_id:
                product = p
                break
        
        if not product:
            print(f"   ❌ Product {product_id} not found")
            return None
        
        # Check stock
        if product["stock"] < quantity:
            print(f"   ❌ Not enough stock for {product['name']}")
            return None
        
        # Create order
        order = {
            "id": self.next_order_id,
            "user_id": user_id,
            "product_id": product_id,
            "product_name": product["name"],
            "quantity": quantity,
            "total_price": product["price"] * quantity
        }
        self.orders.append(order)
        self.next_order_id += 1
        
        # Update stock
        product["stock"] -= quantity
        
        return order


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

print("\n1. CREATING THE DATABASE CONNECTION")
db = DatabaseSimulator()
print("   ✅ Database simulator ready")

print("\n2. FETCHING ALL USERS")
users = db.get_all_users()
for user in users:
    print(f"   User {user['id']}: {user['username']} ({user['email']}) - Age: {user['age']}")

print("\n3. FETCHING A SPECIFIC USER")
user = db.get_user_by_id(2)
if user:
    print(f"   Found: {user['username']} - {user['email']}")

print("\n4. ADDING A NEW USER")
new_user = db.add_user("diana", "diana@example.com", 28)
print(f"   Added: {new_user}")

print("\n5. UPDATING A USER")
updated = db.update_user(1, email="alice_new@example.com", age=31)
if updated:
    print(f"   Updated: {updated}")

print("\n6. FETCHING ALL USERS (After changes)")
users = db.get_all_users()
for user in users:
    print(f"   User {user['id']}: {user['username']} ({user['email']}) - Age: {user['age']}")

print("\n7. CREATING AN ORDER")
order = db.create_order(1, 1, 2)
if order:
    print(f"   Order created: {order}")

print("\n8. CHECKING PRODUCT STOCK")
for product in db.products:
    print(f"   {product['name']}: {product['stock']} in stock")

print("\n9. DELETING A USER")
db.delete_user(4)
print("   User 4 deleted")

print("\n10. FINAL USER LIST")
users = db.get_all_users()
for user in users:
    print(f"   User {user['id']}: {user['username']}")

print("\n" + "=" * 60)
print("KEY TAKEAWAYS:")
print("=" * 60)
print("""
- Python and MySQL work together to store and manage data
- You can perform CRUD operations (Create, Read, Update, Delete)
- This is what real applications do with databases
- The code is clean and easy to understand
- You can build anything from simple tools to complex applications
""")

# Best Practices for MySQL with Python

print("=" * 60)
print("BEST PRACTICES")
print("=" * 60)

# ============================================================
# 1. ALWAYS CLOSE YOUR CONNECTIONS
# ============================================================

print("\n1. ALWAYS CLOSE YOUR CONNECTIONS")

print("""
# Good - use context managers (with statement)
import mysql.connector

def get_users():
    with mysql.connector.connect(
        host="localhost",
        user="root",
        password="password",
        database="mydb"
    ) as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT * FROM users")
            return cursor.fetchall()
    # Connection and cursor are automatically closed

# Also good - manual cleanup
def get_users_manual():
    connection = mysql.connector.connect(...)
    cursor = connection.cursor()
    try:
        cursor.execute("SELECT * FROM users")
        return cursor.fetchall()
    finally:
        cursor.close()
        connection.close()
""")


# ============================================================
# 2. USE PARAMETERIZED QUERIES (Prevent SQL Injection)
# ============================================================

print("\n2. USE PARAMETERIZED QUERIES")

print("""
# Good - using parameterized queries
cursor.execute("SELECT * FROM users WHERE username = %s", (username,))

# Also good - using %s with tuple
cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", (name, email))

# Bad - string concatenation (SQL injection risk!)
cursor.execute(f"SELECT * FROM users WHERE username = '{username}'")
""")


# ============================================================
# 3. USE CONTEXT MANAGERS
# ============================================================

print("\n3. USE CONTEXT MANAGERS")

print("""
# Good - with statement ensures cleanup
with connection.cursor() as cursor:
    cursor.execute("SELECT * FROM users")
    results = cursor.fetchall()

# Bad - manual cleanup (easy to forget)
cursor = connection.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
cursor.close()  # Easy to forget!
""")


# ============================================================
# 4. HANDLE ERRORS
# ============================================================

print("\n4. HANDLE ERRORS")

print("""
# Good - handle database errors
try:
    cursor.execute("SELECT * FROM users")
    results = cursor.fetchall()
except mysql.connector.Error as e:
    print(f"Database error: {e}")
except Exception as e:
    print(f"Unexpected error: {e}")

# Bad - no error handling
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
""")


# ============================================================
# 5. USE TRANSACTIONS
# ============================================================

print("\n5. USE TRANSACTIONS")

print("""
# Good - use transactions for multiple operations
try:
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    connection.commit()  # Only commit if both succeed
except Exception:
    connection.rollback()  # Rollback if anything fails

# Bad - no transaction (inconsistent state if one fails)
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
""")


# ============================================================
# 6. SUMMARY
# ============================================================

print("\n" + "=" * 60)
print("BEST PRACTICES SUMMARY")
print("=" * 60)
print("""
- Always close connections (use context managers)
- Use parameterized queries (prevent SQL injection)
- Use with statements for automatic cleanup
- Handle errors properly
- Use transactions for multiple operations
- Don't hardcode credentials (use environment variables)
- Use connection pooling for multiple requests
- Index your database tables
- Test your database code
- Use ORM for complex applications (SQLAlchemy)
""")