Python MySQL - Connecting to MySQL Database

What You'll Learn Here

Why connect Python to MySQL — building data-driven applications
Installing the MySQL connector — getting the driver
Connection parameters — what you need to connect
Creating a connection — the basic connection code
Handling errors — dealing with connection failures
Connection pooling — reusing connections

Why Connect Python to MySQL?

Connecting Python to MySQL is the first step in building any data-driven application. Once you have a connection, you can read, write, update, and delete data from your Python programs.

Think of the connection like a phone line between your Python program and the MySQL database. You pick up the phone (create a connection), talk to the database (send queries), and hang up (close the connection) when you're done.

Without a connection, Python can't talk to MySQL. It's the essential bridge that makes everything possible.

💡 Key concept: The connection is the bridge between Python and MySQL. You need a proper connection to send queries and receive results.

Installing the MySQL Connector

Getting the Driver

Before you can connect to MySQL, you need to install a MySQL driver for Python. The driver is like a translator that helps Python and MySQL understand each other.

# Installing MySQL Connector

print("=" * 50)
print("INSTALLING THE MYSQL CONNECTOR")
print("=" * 50)

# ============================================================
# OPTION 1: mysql-connector-python (Recommended)
# ============================================================

print("\n1. mysql-connector-python (Official)")

print("""
# Install using pip
pip install mysql-connector-python

# Or with specific version
pip install mysql-connector-python==8.0.33

# This is the official MySQL driver
# Pure Python - no C dependencies
# Works on all platforms
""")

# ============================================================
# OPTION 2: PyMySQL
# ============================================================

print("\n2. PyMySQL")

print("""
# Install using pip
pip install pymysql

# Pure Python implementation
# Lightweight and fast
# Popular in the community
""")

# ============================================================
# OPTION 3: mysqlclient (C-based, faster)
# ============================================================

print("\n3. mysqlclient")

print("""
# Install using pip (Linux/Mac)
pip install mysqlclient

# On Windows, may need extra setup
# C-based - faster than pure Python
# Requires MySQL development headers
""")

# ============================================================
# COMPARISON
# ============================================================

print("\n4. COMPARISON")

print("""
┌─────────────────────────────┬─────────────────────────────────────────────┐
│ DRIVER                      │ BEST FOR                                    │
├─────────────────────────────┼─────────────────────────────────────────────┤
│ mysql-connector-python      │ Most users (recommended)                   │
│                             │ No extra dependencies                      │
│                             │ Official MySQL support                     │
├─────────────────────────────┼─────────────────────────────────────────────┤
│ PyMySQL                     │ When you need a lightweight driver         │
│                             │ When you can't use C extensions            │
├─────────────────────────────┼─────────────────────────────────────────────┤
│ mysqlclient                 │ When performance is critical               │
│                             │ When you have C compiler available         │
└─────────────────────────────┴─────────────────────────────────────────────┘

We'll use mysql-connector-python in this tutorial.
""")

# ============================================================
# VERIFY INSTALLATION
# ============================================================

print("\n5. VERIFY INSTALLATION")

print("""
# Check if mysql-connector-python is installed
import mysql.connector
print(mysql.connector.__version__)

# If you see a version number, it's installed!
""")

# Try to import (simulated)
try:
    import mysql.connector
    print("   ✅ mysql-connector-python is installed")
except ImportError:
    print("    mysql-connector-python is NOT installed")
    print("   Run: pip install mysql-connector-python")

Installing the connector key points:

mysql-connector-python — official driver, recommended
pip install — install using pip
Pure Python — no extra dependencies
Check installation — import mysql.connector

Quick Check: What command installs the official MySQL connector for Python? (Answer: pip install mysql-connector-python)

Connection Parameters

What You Need to Connect

To connect to MySQL, you need specific information about your database. Think of these like the address and key to get into a building.

# Connection Parameters

print("=" * 50)
print("CONNECTION PARAMETERS")
print("=" * 50)

# ============================================================
# REQUIRED PARAMETERS
# ============================================================

print("\n1. REQUIRED PARAMETERS")

print("""
┌─────────────────────┬─────────────────────────────────────────────────────┐
│ Parameter           │ What it is                                        │
├─────────────────────┼─────────────────────────────────────────────────────┤
│ host                │ Where MySQL is running (localhost, IP address)    │
│ user                │ Your MySQL username                               │
│ password            │ Your MySQL password                               │
│ database            │ The database you want to use                      │
└─────────────────────┴─────────────────────────────────────────────────────┘

Examples:
    host="localhost"        # MySQL on the same computer
    host="192.168.1.100"    # MySQL on another computer
    user="root"             # Admin user (or your username)
    password="secret"       # Your MySQL password
    database="mydb"         # The database name
""")


# ============================================================
# OPTIONAL PARAMETERS
# ============================================================

print("\n2. OPTIONAL PARAMETERS")

print("""
┌─────────────────────┬─────────────────────────────────────────────────────┐
│ Parameter           │ What it does                                      │
├─────────────────────┼─────────────────────────────────────────────────────┤
│ port                │ MySQL port (default: 3306)                       │
│ charset             │ Character set (default: utf8mb4)                 │
│ use_unicode         │ Use Unicode (True/False)                         │
│ autocommit          │ Auto-commit transactions (True/False)            │
│ connection_timeout  │ Timeout for connection (seconds)                 │
│ pool_name           │ Name of connection pool                          │
│ pool_size           │ Size of connection pool                          │
└─────────────────────┴─────────────────────────────────────────────────────┘

Examples:
    port=3306
    charset="utf8mb4"
    autocommit=True
    connection_timeout=10
""")


# ============================================================
# USING A CONFIGURATION DICTIONARY
# ============================================================

print("\n3. USING A CONFIGURATION DICTIONARY")

print("""
# Method 1: Individual parameters
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="mydb"
)

# Method 2: Using a dictionary
config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "mydb"
}
connection = mysql.connector.connect(**config)

# Method 3: Reading from a config file
# config.ini file:
# [mysql]
# host = localhost
# user = root
# password = secret
# database = mydb
""")


# ============================================================
# ENVIRONMENT VARIABLES (Best Practice)
# ============================================================

print("\n4. ENVIRONMENT VARIABLES (Best Practice)")

print("""
# Store credentials in environment variables
import os

connection = mysql.connector.connect(
    host=os.getenv("DB_HOST", "localhost"),
    user=os.getenv("DB_USER", "root"),
    password=os.getenv("DB_PASSWORD", ""),
    database=os.getenv("DB_NAME", "mydb")
)

# This keeps passwords out of your code!
# On Linux/Mac: export DB_PASSWORD=secret
# On Windows: set DB_PASSWORD=secret

# Or use a .env file with python-dotenv
from dotenv import load_dotenv
load_dotenv()
""")

Connection parameters key points:

Required — host, user, password, database
Optional — port, charset, autocommit
Use environment variables — keep passwords out of code
Use config dictionary — cleaner code

Quick Check: What are the required parameters to connect to MySQL? (Answer: host, user, password, database)

Basic Connection

Creating Your First Connection

Let's create a basic connection to MySQL and test it by getting the server version.

# Basic Connection

print("=" * 50)
print("BASIC CONNECTION")
print("=" * 50)

# ============================================================
# SIMPLE CONNECTION
# ============================================================

print("\n1. SIMPLE CONNECTION")

print("""
import mysql.connector

# Create the connection
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="mydb"
)

# Check if connected
if connection.is_connected():
    print("Connected to MySQL!")
    
    # Get server information
    db_info = connection.get_server_info()
    print(f"Server version: {db_info}")
    
    # Close the connection
    connection.close()
    print("Connection closed")
""")


# ============================================================
# CONNECTION WITH CONTEXT MANAGER (Recommended)
# ============================================================

print("\n2. CONNECTION WITH CONTEXT MANAGER")

print("""
# Using with statement - automatically closes connection
with mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="mydb"
) as connection:
    print("Connected to MySQL!")
    
    # Create a cursor
    cursor = connection.cursor()
    
    # Execute a query
    cursor.execute("SELECT VERSION()")
    
    # Get the result
    version = cursor.fetchone()
    print(f"MySQL version: {version[0]}")
    
    # Cursor is automatically closed
    # Connection is automatically closed
""")


# ============================================================
# TESTING THE CONNECTION
# ============================================================

print("\n3. TESTING THE CONNECTION")

print("""
import mysql.connector

def test_connection():
    \"\"\"Test MySQL connection\"\"\"
    try:
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="secret",
            database="mydb",
            connection_timeout=5
        )
        
        print("✅ Connection successful!")
        print(f"   MySQL version: {connection.get_server_info()}")
        
        connection.close()
        return True
        
    except mysql.connector.Error as e:
        print(f" Connection failed: {e}")
        return False

# Test the connection
test_connection()
""")

# ============================================================
# CONNECTION POOL
# ============================================================

print("\n4. CONNECTION POOL")

print("""
from mysql.connector import pooling

# Create a connection pool
config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "mydb",
    "pool_name": "mypool",
    "pool_size": 5
}

connection_pool = mysql.connector.pooling.MySQLConnectionPool(**config)

# Get a connection from the pool
connection = connection_pool.get_connection()
print("Connection from pool")

# Use the connection
cursor = connection.cursor()
cursor.execute("SELECT VERSION()")
version = cursor.fetchone()
print(f"MySQL version: {version[0]}")

# Return the connection to the pool
connection.close()
print("Connection returned to pool")
""")

Basic connection key points:

mysql.connector.connect() — creates a connection
is_connected() — check if connected
with statement — automatic cleanup
get_server_info() — get MySQL version

Quick Check: What function creates a connection to MySQL? (Answer: mysql.connector.connect())

Handling Connection Errors

Dealing with Connection Problems

Connections can fail for many reasons. It's important to handle errors gracefully so your program doesn't crash.

# Handling Connection Errors

print("=" * 50)
print("HANDLING CONNECTION ERRORS")
print("=" * 50)

# ============================================================
# COMMON CONNECTION ERRORS
# ============================================================

print("\n1. COMMON CONNECTION ERRORS")

print("""
┌─────────────────────────────┬─────────────────────────────────────────────┐
│ Error                       │ Cause                                       │
├─────────────────────────────┼─────────────────────────────────────────────┤
│ InterfaceError              │ Connection issue                           │
│ DatabaseError               │ Database-related error                     │
│ IntegrityError              │ Data integrity violation                   │
│ ProgrammingError            │ SQL syntax error                           │
│ OperationalError            │ Connection, timeout, etc.                  │
│ NotSupportedError           │ Feature not supported                      │
└─────────────────────────────┴─────────────────────────────────────────────┘
""")


# ============================================================
# ERROR HANDLING WITH TRY/EXCEPT
# ============================================================

print("\n2. ERROR HANDLING WITH TRY/EXCEPT")

print("""
import mysql.connector
from mysql.connector import Error

def safe_connect():
    \"\"\"Connect to MySQL with error handling\"\"\"
    try:
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="secret",
            database="mydb"
        )
        print("✅ Connected successfully")
        return connection
        
    except mysql.connector.Error as e:
        print(f" Database error: {e}")
        return None
        
    except Exception as e:
        print(f" Unexpected error: {e}")
        return None

# Use the function
connection = safe_connect()
if connection:
    connection.close()
""")


# ============================================================
# SPECIFIC ERROR HANDLING
# ============================================================

print("\n3. SPECIFIC ERROR HANDLING")

print("""
import mysql.connector
from mysql.connector import errorcode

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="wrong_password",  # Wrong password
        database="mydb"
    )
    
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print(" Access denied: Check username and password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print(" Database does not exist")
    elif err.errno == errorcode.ER_CON_COUNT_ERROR:
        print(" Too many connections")
    else:
        print(f" Database error: {err}")
""")

# ============================================================
# RETRY LOGIC
# ============================================================

print("\n4. RETRY LOGIC")

print("""
import time
import mysql.connector

def connect_with_retry(max_retries=3, delay=2):
    \"\"\"Connect with retry logic\"\"\"
    for attempt in range(max_retries):
        try:
            print(f"Attempt {attempt + 1}/{max_retries}...")
            connection = mysql.connector.connect(
                host="localhost",
                user="root",
                password="secret",
                database="mydb"
            )
            print("✅ Connected successfully")
            return connection
            
        except mysql.connector.Error as e:
            print(f" Connection failed: {e}")
            if attempt < max_retries - 1:
                print(f" Retrying in {delay} seconds...")
                time.sleep(delay)
            else:
                print(" All retry attempts failed")
                return None

# Use the function
connection = connect_with_retry()
if connection:
    connection.close()
""")

# ============================================================
# CONNECTION TEST FUNCTION
# ============================================================

print("\n5. CONNECTION TEST FUNCTION")

print("""
def test_connection(host, user, password, database):
    \"\"\"Test if connection works\"\"\"
    try:
        connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            connection_timeout=5
        )
        connection.close()
        return True, "Connection successful"
    except mysql.connector.Error as e:
        return False, str(e)

# Test the connection
success, message = test_connection("localhost", "root", "secret", "mydb")
if success:
    print("✅ Connection works!")
else:
    print(f" Connection fails: {message}")
""")

Error handling key points:

Use try/except — catch and handle errors
Specific errors — handle different error types
Retry logic — try again on temporary failures
Log errors — record what went wrong

Quick Check: How do you handle connection errors in Python? (Answer: Use try/except blocks with mysql.connector.Error)

Connection Pooling

Reusing Connections Efficiently

Connection pooling is a technique where you create a pool of connections and reuse them. This is much more efficient than creating a new connection for every request.

# Connection Pooling

print("=" * 50)
print("CONNECTION POOLING")
print("=" * 50)

# ============================================================
# CREATING A CONNECTION POOL
# ============================================================

print("\n1. CREATING A CONNECTION POOL")

print("""
from mysql.connector import pooling

# Create a connection pool
config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "mydb",
    "pool_name": "mypool",
    "pool_size": 5  # Max 5 connections
}

pool = mysql.connector.pooling.MySQLConnectionPool(**config)
print(f"Created connection pool: {pool.pool_name} (size: {pool.pool_size})")
""")


# ============================================================
# USING THE POOL
# ============================================================

print("\n2. USING THE POOL")

print("""
# Get a connection from the pool
connection = pool.get_connection()
print("Connection from pool")

# Use the connection
cursor = connection.cursor()
cursor.execute("SELECT VERSION()")
version = cursor.fetchone()
print(f"MySQL version: {version[0]}")

# Return the connection to the pool
connection.close()  # This returns it to the pool
print("Connection returned to pool")

# Get another connection
connection2 = pool.get_connection()
print("Another connection from pool")
connection2.close()
""")


# ============================================================
# POOLING WITH CONTEXT MANAGER
# ============================================================

print("\n3. POOLING WITH CONTEXT MANAGER")

print("""
# Using the pool with context manager
with pool.get_connection() as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT VERSION()")
    version = cursor.fetchone()
    print(f"MySQL version: {version[0]}")
# Connection automatically returned to pool
""")


# ============================================================
# POOL STATUS
# ============================================================

print("\n4. POOL STATUS")

print("""
# Check pool status
# Note: MySQL Connector/Python doesn't have a built-in status method
# But you can track usage manually

class ConnectionPool:
    def __init__(self, **config):
        self.pool = mysql.connector.pooling.MySQLConnectionPool(**config)
        self.active_connections = 0
        self.total_connections = 0
    
    def get_connection(self):
        conn = self.pool.get_connection()
        self.active_connections += 1
        self.total_connections += 1
        return conn
    
    def release_connection(self, connection):
        connection.close()
        self.active_connections -= 1
    
    def get_stats(self):
        return {
            "active": self.active_connections,
            "total": self.total_connections,
            "pool_size": self.pool.pool_size
        }

# Usage
pool = ConnectionPool(**config)
conn = pool.get_connection()
print(pool.get_stats())
pool.release_connection(conn)
print(pool.get_stats())
""")


# ============================================================
# WHEN TO USE CONNECTION POOLING
# ============================================================

print("\n5. WHEN TO USE CONNECTION POOLING")

print("""
┌─────────────────────────────────────────────────────────────────────┐
│ WHEN TO USE CONNECTION POOLING                                     │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  ✅ Web applications (many concurrent users)                      │
│  ✅ APIs that handle many requests                                  │
│  ✅ Applications with frequent database queries                    │
│  ✅ Microservices                                                  │
│                                                                     │
│  ❌ Simple scripts (one-time use)                                  │
│  ❌ Command-line tools                                             │
│  ❌ Batch processing (one long-running job)                        │
│                                                                     │
│  Connection pooling saves time by reusing connections             │
│  Creating a new connection takes ~100-200ms                       │
│  Reusing a connection takes ~1-2ms                                │
└─────────────────────────────────────────────────────────────────────┘
""")

Connection pooling key points:

Pool — a group of reusable connections
Faster — reusing connections is faster than creating new ones
Scalable — handles many concurrent users
Best for web apps — frequent database access

Quick Check: What is connection pooling? (Answer: A technique where connections are reused instead of created each time)

Real-World Example

Building a Database Manager Class

# Real-World Example: Database Manager

import mysql.connector
from mysql.connector import Error
import time

print("=" * 60)
print("DATABASE MANAGER")
print("=" * 60)

# ============================================================
# DATABASE MANAGER CLASS
# ============================================================

class DatabaseManager:
    """Manage MySQL database connections and operations"""
    
    def __init__(self, host, user, password, database):
        """Initialize with connection parameters"""
        self.config = {
            "host": host,
            "user": user,
            "password": password,
            "database": database
        }
        self.connection = None
        self.cursor = None
        self.is_connected = False
    
    def connect(self):
        """Establish a connection to the database"""
        try:
            self.connection = mysql.connector.connect(**self.config)
            self.is_connected = True
            print("✅ Connected to MySQL database")
            return True
        except mysql.connector.Error as e:
            print(f" Connection error: {e}")
            return False
    
    def disconnect(self):
        """Close the connection"""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
        self.is_connected = False
        print("🔌 Disconnected from database")
    
    def execute_query(self, query, params=None):
        """Execute a query and return results"""
        if not self.is_connected:
            print(" Not connected to database")
            return None
        
        try:
            self.cursor = self.connection.cursor()
            
            if params:
                self.cursor.execute(query, params)
            else:
                self.cursor.execute(query)
            
            # If it's a SELECT query, return results
            if query.strip().upper().startswith("SELECT"):
                return self.cursor.fetchall()
            
            # For INSERT, UPDATE, DELETE, commit and return affected rows
            self.connection.commit()
            return self.cursor.rowcount
            
        except mysql.connector.Error as e:
            print(f" Query error: {e}")
            self.connection.rollback()
            return None
    
    def test_connection(self):
        """Test if the connection is working"""
        try:
            result = self.execute_query("SELECT VERSION()")
            if result:
                version = result[0][0]
                print(f"✅ MySQL version: {version}")
                return True
            return False
        except:
            return False
    
    def get_status(self):
        """Get connection status"""
        return {
            "connected": self.is_connected,
            "host": self.config["host"],
            "database": self.config["database"],
            "user": self.config["user"]
        }


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

print("\n1. CREATING DATABASE MANAGER")

# Create the manager (using simulated config)
db = DatabaseManager("localhost", "root", "secret", "mydb")
print("   Database manager created")

print("\n2. CONNECTING TO DATABASE")
db.connect()

print("\n3. TESTING CONNECTION")
db.test_connection()

print("\n4. GETTING STATUS")
status = db.get_status()
print(f"   Status: {status}")

print("\n5. EXECUTING A QUERY")
# Simulate a query
print("   Executing: SELECT VERSION()")
result = db.execute_query("SELECT VERSION()")
if result:
    print(f"   Result: {result}")

print("\n6. DISCONNECTING")
db.disconnect()

print("\n7. TRYING TO QUERY AFTER DISCONNECT")
result = db.execute_query("SELECT 1")
if result is None:
    print("   ✅ Query blocked (not connected)")


# ============================================================
# USING THE MANAGER WITH CONTEXT MANAGER
# ============================================================

print("\n8. USING WITH CONTEXT MANAGER")

print("""
class DatabaseManager:
    def __enter__(self):
        self.connect()
        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.disconnect()

# Usage
with DatabaseManager("localhost", "root", "secret", "mydb") as db:
    result = db.execute_query("SELECT VERSION()")
    print(f"Version: {result[0][0]}")
# Automatically disconnects
""")


# ============================================================
# KEY TAKEAWAYS
# ============================================================

print("\n" + "=" * 60)
print("KEY TAKEAWAYS:")
print("=" * 60)
print("""
- Create a connection using mysql.connector.connect()
- Always close connections when done (use with statement)
- Handle errors with try/except
- Use connection pooling for web applications
- Create a DatabaseManager class for reusable code
- Store credentials in environment variables
- Test your connection before using it
""")

Real-world example key points:

DatabaseManager class — reusable connection manager
connect() — establishes connection
disconnect() — closes connection
execute_query() — runs queries
Context manager — automatic connection handling

Quick Check: What's the benefit of using a DatabaseManager class? (Answer: It encapsulates connection logic, making code reusable and cleaner)

Best Practices

Connection Best Practices

# Connection Best Practices

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

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

print("\n1. ALWAYS CLOSE CONNECTIONS")

print("""
# Good - with statement auto-closes
with mysql.connector.connect(...) as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT 1")

# Good - try/finally
connection = mysql.connector.connect(...)
try:
    cursor = connection.cursor()
    cursor.execute("SELECT 1")
finally:
    connection.close()

# Bad - never closing (resource leak)
connection = mysql.connector.connect(...)
cursor = connection.cursor()
cursor.execute("SELECT 1")
# connection is never closed!
""")


# ============================================================
# 2. USE CONTEXT MANAGERS
# ============================================================

print("\n2. USE CONTEXT MANAGERS")

print("""
# Good - using context manager
with mysql.connector.connect(...) as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT 1")
        result = cursor.fetchone()
# Both cursor and connection are closed

# Good - custom context manager
class Database:
    def __enter__(self):
        self.connection = mysql.connector.connect(...)
        return self
    
    def __exit__(self, *args):
        self.connection.close()
""")


# ============================================================
# 3. DON'T HARDCODE CREDENTIALS
# ============================================================

print("\n3. DON'T HARDCODE CREDENTIALS")

print("""
# Bad - hardcoded credentials
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret123",
    database="mydb"
)

# Good - environment variables
import os
connection = mysql.connector.connect(
    host=os.getenv("DB_HOST", "localhost"),
    user=os.getenv("DB_USER", "root"),
    password=os.getenv("DB_PASSWORD"),
    database=os.getenv("DB_NAME", "mydb")
)

# Good - config file
# config.json
# {"host": "localhost", "user": "root", ...}
import json
with open("config.json") as f:
    config = json.load(f)
connection = mysql.connector.connect(**config)
""")


# ============================================================
# 4. USE CONNECTION POOLING FOR WEB APPS
# ============================================================

print("\n4. USE CONNECTION POOLING FOR WEB APPS")

print("""
# For web applications with many users
from mysql.connector import pooling

config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "mydb",
    "pool_name": "mypool",
    "pool_size": 10
}

pool = pooling.MySQLConnectionPool(**config)

# In each request:
connection = pool.get_connection()
try:
    # Use connection
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM users")
finally:
    connection.close()  # Returns to pool
""")


# ============================================================
# 5. SET CONNECTION TIMEOUT
# ============================================================

print("\n5. SET CONNECTION TIMEOUT")

print("""
# Set a timeout to avoid hanging
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="mydb",
    connection_timeout=10,  # 10 seconds
    connect_timeout=5       # 5 seconds for initial connection
)
""")


# ============================================================
# 6. LOG CONNECTION EVENTS
# ============================================================

print("\n6. LOG CONNECTION EVENTS")

print("""
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

try:
    connection = mysql.connector.connect(...)
    logger.info("Database connection established")
except mysql.connector.Error as e:
    logger.error(f"Database connection failed: {e}")
    raise
finally:
    if connection:
        connection.close()
        logger.info("Database connection closed")
""")

# ============================================================
# 7. SUMMARY
# ============================================================

print("\n" + "=" * 60)
print("BEST PRACTICES SUMMARY")
print("=" * 60)
print("""
- Always close connections (use with statement)
- Use context managers for automatic cleanup
- Store credentials in environment variables
- Use connection pooling for web apps
- Set connection timeouts
- Log connection events for debugging
- Handle errors gracefully
- Test connections before use
- Use a DatabaseManager class for reusable code
""")

Best practices summary:

Close connections — always clean up
Use context managers — automatic cleanup
Don't hardcode credentials — use environment variables
Use connection pooling — for web applications
Set timeouts — prevent hanging

Quick Check: What's the best way to manage database credentials? (Answer: Use environment variables or a config file, never hardcode them)

Try It Yourself

Experiment with database connections in the editor below.

Loading Pyodide... 0%

Python Code Editor

# Python MySQL Connection - Practice

print("=" * 50)
print("MYSQL CONNECTION - PRACTICE")
print("=" * 50)

# ============================================================
# 1. SIMULATED CONNECTION
# ============================================================

print("\n1. SIMULATED CONNECTION")

class SimulatedConnection:
    """Simulate a MySQL connection for learning"""
    
    def __init__(self, host, user, password, database):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.is_connected = False
        self.version = "8.0.33"
    
    def connect(self):
        """Simulate connecting"""
        print(f"   Connecting to {self.host} as {self.user}...")
        # Simulate connection
        if self.host and self.user and self.password is not None:
            self.is_connected = True
            print("   ✅ Connected!")
            return True
        else:
            print("    Connection failed!")
            return False
    
    def disconnect(self):
        """Simulate disconnecting"""
        self.is_connected = False
        print("   🔌 Disconnected")
    
    def get_server_info(self):
        """Get server version"""
        if self.is_connected:
            return self.version
        return None

# Create and test connection
print("   Creating connection...")
conn = SimulatedConnection("localhost", "root", "secret", "mydb")
conn.connect()
print(f"   Server version: {conn.get_server_info()}")
conn.disconnect()

# ============================================================
# 2. CONNECTION PARAMETERS
# ============================================================

print("\n2. CONNECTION PARAMETERS")

config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "mydb",
    "port": 3306,
    "charset": "utf8mb4"
}

print("   Connection config:")
for key, value in config.items():
    print(f"      {key}: {value}")

# ============================================================
# 3. ERROR HANDLING
# ============================================================

print("\n3. ERROR HANDLING")

def safe_connect(host, user, password, database):
    """Safely connect with error handling"""
    print(f"   Trying to connect to {host}...")
    
    # Simulate errors
    if not password:
        print("    Error: Access denied (wrong password)")
        return None
    if not database:
        print("    Error: Database not found")
        return None
    if host != "localhost":
        print("    Error: Host not found")
        return None
    
    print("   ✅ Connection successful!")
    return SimulatedConnection(host, user, password, database)

# Test cases
safe_connect("localhost", "root", "secret", "mydb")
safe_connect("localhost", "root", "", "mydb")  # Wrong password
safe_connect("localhost", "root", "secret", "")  # No database
safe_connect("remotehost", "root", "secret", "mydb")  # Wrong host

# ============================================================
# 4. SUMMARY
# ============================================================

print("\n" + "=" * 50)
print("SUMMARY")
print("=" * 50)
print("""
MYSQL CONNECTION — KEY CONCEPTS

1. Install driver: pip install mysql-connector-python
2. Parameters: host, user, password, database
3. Connect: mysql.connector.connect(**config)
4. Test: connection.is_connected()
5. Close: connection.close()
6. Use with: with mysql.connector.connect(...) as connection:

ERROR TYPES:
• InterfaceError: Connection issues
• DatabaseError: Database problems
• ProgrammingError: SQL syntax errors
• OperationalError: Timeout, connection lost

BEST PRACTICES:
• Always close connections
• Use environment variables for credentials
• Use connection pooling for web apps
• Handle errors with try/except
""")

==================================================
MYSQL CONNECTION - PRACTICE
==================================================

1. SIMULATED CONNECTION
Creating connection...
Connecting to localhost as root...
✅ Connected!
Server version: 8.0.33
🔌 Disconnected

2. CONNECTION PARAMETERS
Connection config:
host: localhost
user: root
password: secret
database: mydb
port: 3306
charset: utf8mb4

3. ERROR HANDLING
Trying to connect to localhost...
✅ Connection successful!
Trying to connect to localhost...
❌ Error: Access denied (wrong password)
Trying to connect to localhost...
❌ Error: Database not found
Trying to connect to remotehost...
❌ Error: Host not found

🏆

You've Got It!

You now understand how to connect Python to MySQL. You know how to install the connector, create connections, handle errors, and use connection pooling.

Quick Quiz

Test what you've learned:

1. What command installs the MySQL connector for Python?

A) pip install mysql B) pip install mysql-connector-python C) pip install mysqlclient D) pip install connector

2. What are the required parameters for connecting to MySQL?

A) host, user B) host, user, password, database C) host, port D) user, password only

3. What function creates a connection to MySQL?

A) mysql.connector.create() B) mysql.connector.connect() C) mysql.connector.open() D) mysql.connector.new()

4. What does connection pooling do?

A) Creates a single connection B) Reuses connections to improve performance C) Closes all connections D) Creates a new connection every time

5. Why should you use environment variables for credentials?

A) To make code faster B) To keep passwords out of source code C) To use more memory D) To make it work on all platforms

Frequently Asked Questions

How do I connect Python to MySQL?

Install mysql-connector-python, then use mysql.connector.connect(host, user, password, database) to create a connection. Always close the connection when done.

What MySQL driver should I use with Python?

The official mysql-connector-python is recommended. It's pure Python, well-maintained, and works on all platforms. PyMySQL is a good alternative, and mysqlclient is faster but requires C compilation.

Why does my connection fail?

Common reasons: wrong host, incorrect username/password, database doesn't exist, MySQL not running, firewall blocking, or connection timeout. Always handle errors with try/except to see the specific error message.

What is connection pooling and when should I use it?

Connection pooling reuses connections instead of creating new ones for each request. Use it in web applications and APIs where you have many concurrent users. It's faster and more efficient.

Should I use environment variables for database credentials?

Yes! Never hardcode passwords in your source code. Use environment variables or a config file that's not committed to version control. This keeps your credentials safe and makes your code more portable.

How do I handle connection errors gracefully?

Use try/except blocks with specific error handling. Check for different error types (access denied, database not found, connection timeout). Implement retry logic for temporary failures. Always log errors for debugging.

Where to Go From Here

Now that you know how to connect Python to MySQL, check out these related topics:

Previous: DBMS vs File System Next: Create Database →

Python

Python: Connecting to MySQL Database

Why Connect Python to MySQL?

Installing the MySQL Connector

Getting the Driver

Connection Parameters

What You Need to Connect

Basic Connection

Creating Your First Connection

Handling Connection Errors

Dealing with Connection Problems

Connection Pooling

Reusing Connections Efficiently

Real-World Example

Building a Database Manager Class

Best Practices

Connection Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

How do I connect Python to MySQL?

What MySQL driver should I use with Python?

Why does my connection fail?

What is connection pooling and when should I use it?

Should I use environment variables for database credentials?

How do I handle connection errors gracefully?

Where to Go From Here

Create Database

Create Table

Insert Data

Python

Python: Connecting to MySQL Database

Why Connect Python to MySQL?

Installing the MySQL Connector

Getting the Driver

Connection Parameters

What You Need to Connect

Basic Connection

Creating Your First Connection

Handling Connection Errors

Dealing with Connection Problems

Connection Pooling

Reusing Connections Efficiently

Real-World Example

Building a Database Manager Class

Best Practices

Connection Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

How do I connect Python to MySQL? ▼

What MySQL driver should I use with Python? ▼

Why does my connection fail? ▼

What is connection pooling and when should I use it? ▼

Should I use environment variables for database credentials? ▼

How do I handle connection errors gracefully? ▼

Where to Go From Here

Create Database

Create Table

Insert Data

How do I connect Python to MySQL?

What MySQL driver should I use with Python?

Why does my connection fail?

What is connection pooling and when should I use it?

Should I use environment variables for database credentials?

How do I handle connection errors gracefully?