Of course! Here is a comprehensive guide to using MySQL with Python 3, covering the most common and recommended approaches.

We'll focus on two primary libraries:

1. mysql-connector-python: The official driver from Oracle. It's a great choice for general-purpose use.
2. SQLAlchemy: A powerful Object-Relational Mapper (ORM). It's the recommended approach for larger applications as it abstracts away the raw SQL and provides a more Pythonic way to interact with the database.

Part 1: The Official Driver (mysql-connector-python)

This is the most direct way to connect to a MySQL database. You write raw SQL queries and execute them.

Step 1: Installation

First, you need to install the library using pip.

pip install mysql-connector-python

Step 2: Connecting to the Database

You need to provide your database connection details: host, user, password, and database name.

import mysql.connector
from mysql.connector import Error
def create_server_connection(host_name, user_name, user_password):
    """Establishes a connection to the MySQL server."""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("MySQL connection successful")
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
    return connection
# --- Example Usage ---
# Replace with your actual MySQL server credentials
# For local development, host is often 'localhost', user is 'root', and password might be empty.
db_connection = create_server_connection("localhost", "root", "your_password")
if db_connection is not None:
    # Don't forget to close the connection when you're done
    db_connection.close()
    print("MySQL connection is closed")

Step 3: Creating a Database

It's best practice to connect to the server first (without specifying a database) and then create your database.

def create_database(connection, query):
    """Creates a new database if it doesn't exist."""
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as e:
        print(f"Error while creating database: {e}")
# --- Example Usage ---
# Connect to the server first
conn = create_server_connection("localhost", "root", "your_password")
if conn:
    create_database_query = "CREATE DATABASE IF NOT EXISTS python_db"
    create_database(conn, create_database_query)
    conn.close()

Step 4: Creating a Table and Inserting Data

Now, connect to the specific database you just created.

def create_db_connection(host_name, user_name, user_password, db_name):
    """Establishes a connection to a specific MySQL database."""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("MySQL connection to database successful")
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
    return connection
def execute_query(connection, query):
    """Executes a single query."""
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"Error while executing query: {e}")
# --- Example Usage ---
# Connect to the specific database
db_conn = create_db_connection("localhost", "root", "your_password", "python_db")
if db_conn:
    create_table_query = """
    CREATE TABLE IF NOT EXISTS employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        department VARCHAR(255),
        salary INT
    )
    """
    execute_query(db_conn, create_table_query)
    insert_employee_query = """
    INSERT INTO employees (name, department, salary)
    VALUES (%s, %s, %s)
    """
    employee_data = ("Alice", "Engineering", 90000)
    cursor = db_conn.cursor()
    cursor.execute(insert_employee_query, employee_data)
    db_conn.commit() # Commit the transaction
    # Insert multiple records at once
    employees_to_insert = [
        ("Bob", "Marketing", 75000),
        ("Charlie", "Sales", 80000)
    ]
    cursor.executemany(insert_employee_query, employees_to_insert)
    db_conn.commit()
    print(f"{cursor.rowcount} records inserted")
    db_conn.close()

Step 5: Fetching Data (Reading from the Database)

Use SELECT queries and the fetchall() method to retrieve data.

def read_query(connection, query):
    """Executes a query and returns the results."""
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"Error while reading data: {e}")
# --- Example Usage ---
db_conn = create_db_connection("localhost", "root", "your_password", "python_db")
if db_conn:
    select_employees_query = "SELECT * FROM employees"
    employees = read_query(db_conn, select_employees_query)
    print("\n--- All Employees ---")
    for employee in employees:
        print(employee)
    select_high_earners_query = "SELECT name, salary FROM employees WHERE salary > 80000"
    high_earners = read_query(db_conn, select_high_earners_query)
    print("\n--- High Earners ---")
    for earner in high_earners:
        print(f"Name: {earner[0]}, Salary: {earner[1]}")
    db_conn.close()

Part 2: The Recommended Approach (SQLAlchemy ORM)

ORMs map your database tables to Python classes, and rows to objects. This makes your code cleaner, more maintainable, and less prone to SQL injection vulnerabilities.

Step 1: Installation

You need to install both the MySQL driver and SQLAlchemy itself.

pip install mysql-connector-python
pip install SQLAlchemy

Step 2: Setting up the Engine and Model

The "engine" is the core interface to the database. We define a "declarative base" which will be the parent class for all our table-based models.

import sqlalchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# 1. Create the Engine
# The engine is the core interface to the database.
# 'mysql+mysqlconnector://user:password@host/database_name'
engine = create_engine('mysql+mysqlconnector://root:your_password@localhost/python_db')
# 2. Create the Base
# The Base is a class that our mapped classes will inherit from.
Base = declarative_base()
# 3. Define the Table (as a Python class)
class Employee(Base):
    __tablename__ = 'employees' # The name of the table in the database
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    department = Column(String(255))
    salary = Column(Integer)
    def __repr__(self):
        return f"<Employee(name='{self.name}', dept='{self.department}')>"
# 4. Create the Table in the Database (if it doesn't exist)
# This will run a CREATE TABLE statement based on the class definition.
Base.metadata.create_all(engine)
print("Database tables created/verified.")

Step 3: Interacting with the Database (CRUD)

We use a Session to manage all conversations with the database.

# Create a "Session" class bound to the engine
Session = sessionmaker(bind=engine)
session = Session()
# --- CREATE ---
print("\n--- Adding new employees ---")
new_employee1 = Employee(name="David", department="HR", salary=65000)
new_employee2 = Employee(name="Eve", department="Finance", salary=85000)
session.add(new_employee1)
session.add(new_employee2)
# Use commit() to save the new objects to the database
session.commit()
# --- READ (Querying) ---
print("\n--- Querying all employees ---")
all_employees = session.query(Employee).all()
for emp in all_employees:
    print(emp)
print("\n--- Querying employees in Engineering ---")
engineering_employees = session.query(Employee).filter(Employee.department == "Engineering").all()
for emp in engineering_employees:
    print(emp)
# --- UPDATE ---
print("\n--- Updating Alice's salary ---")
# First, find the employee
alice_to_update = session.query(Employee).filter_by(name="Alice").first()
if alice_to_update:
    alice_to_update.salary = 95000
    session.commit()
    print(f"Alice's new salary is: {alice_to_update.salary}")
# --- DELETE ---
print("\n--- Deleting Charlie from the database ---")
charlie_to_delete = session.query(Employee).filter_by(name="Charlie").first()
if charlie_to_delete:
    session.delete(charlie_to_delete)
    session.commit()
    print("Charlie has been deleted.")
# Always close the session
session.close()
print("\nSession closed.")

Summary: Which One Should You Use?

For most new projects, especially if they are expected to grow, starting with SQLAlchemy is the best long-term decision. If you just need to quickly run a simple script, the official driver is perfectly fine.