Python MySQL Tutorial: Learn Database Access from Scratch (2026)

I learned MySQL the hard way — debugging a production outage caused by an unparameterized query. That experience taught me that database access in Python isn't just about running SQL; it's about connection management, parameter binding, transaction handling, and avoiding the injection risks that come from string interpolation. The mysql-connector-python library provides a clean DB-API 2.0 interface that works across MySQL and MariaDB.

This tutorial covers the full lifecycle: connecting to a MySQL database, creating tables, performing CRUD operations, using parameterized queries, handling transactions, and managing connection pools for production applications.

Connecting to MySQL and Creating a Database

mysql.connector.connect() establishes a connection with host, user, password, and database parameters. I always use connection context managers (with statement) so connections are closed even if an error occurs. After connecting, create a cursor to execute SQL statements.

import mysql.connector
from mysql.connector import Error

try:
    conn = mysql.connector.connect(
        host='localhost',
        user='root',
        password='your_password'
    )
    cursor = conn.cursor()
    cursor.execute("CREATE DATABASE IF NOT EXISTS ecommerce")
    cursor.execute("USE ecommerce")
    print("Database connected and ready")
except Error as e:
    print(f"Error: {e}")
finally:
    if conn.is_connected():
        cursor.close()
        conn.close()

Creating Tables with Constraints

Define tables with appropriate data types, primary keys, foreign keys, and constraints. AUTO_INCREMENT generates unique IDs. Foreign key constraints maintain referential integrity — attempting to delete a referenced parent row raises an error unless ON DELETE CASCADE is specified.

create_products = """
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)"""

create_orders = """
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
)"""

cursor.execute(create_products)
cursor.execute(create_orders)
conn.commit()

CRUD Operations with Parameterized Queries

Never use f-strings or string formatting to build SQL — that's how SQL injection happens. Use %s placeholders and pass parameters as a tuple to cursor.execute(). For multiple rows, executemany() inserts them in one call.

insert_product = "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)"
product_data = ("Laptop", 999.99, 15)
cursor.execute(insert_product, product_data)
print(f"Inserted with ID: {cursor.lastrowid}")

products = [
    ("Mouse", 29.99, 100),
    ("Keyboard", 79.99, 50),
    ("Monitor", 299.99, 30)
]
cursor.executemany(insert_product, products)
conn.commit()

select = "SELECT * FROM products WHERE price > %s AND stock > %s"
cursor.execute(select, (100, 10))
for row in cursor.fetchall():
    print(f"{row[1]}: ${row[2]} ({row[3]} in stock)")

Transactions and Error Handling

Transactions group multiple operations into one atomic unit. conn.start_transaction() or simply set conn.autocommit = False. If any operation fails, rollback() reverts all changes. commit() persists them. I always wrap transactional blocks in try/except and rollback on error.

try:
    conn.start_transaction()
    cursor = conn.cursor()

    cursor.execute(
        "UPDATE products SET stock = stock - %s WHERE id = %s",
        (1, 1)
    )
    cursor.execute(
        "INSERT INTO orders (product_id, quantity, total) VALUES (%s, %s, %s)",
        (1, 1, 999.99)
    )
    conn.commit()
    print("Transaction committed")

except Error as e:
    conn.rollback()
    print(f"Transaction failed, rolled back: {e}")

Connection Pooling for Production

Opening a new database connection for every request is slow and exhausts server resources. MySQLConnectionPool maintains a pool of reusable connections. Acquire a connection with pool.get_conn(), use it, then return it with conn.close() (which returns it to the pool).

from mysql.connector.pooling import MySQLConnectionPool

pool = MySQLConnectionPool(
    pool_name='mypool',
    pool_size=10,
    pool_reset_session=True,
    host='localhost',
    database='ecommerce',
    user='root',
    password='your_password'
)

try:
    conn = pool.get_conn()
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM products")
    count = cursor.fetchone()[0]
    print(f"Total products: {count}")
except Error as e:
    print(f"Pool error: {e}")
finally:
    if conn.is_connected():
        conn.close()

Using MySQL with Pandas and Exporting

Pandas makes it trivial to read query results directly into a DataFrame with pd.read_sql(). This is my standard workflow for analysis: run SQL to aggregate data, load into Pandas, then visualize with Matplotlib or Seaborn.

import pandas as pd

query = """
SELECT p.name, SUM(o.total) as revenue, COUNT(o.id) as order_count
FROM products p
JOIN orders o ON p.id = o.product_id
GROUP BY p.name
ORDER BY revenue DESC
"""

df = pd.read_sql(query, conn)
print(df.head())

df.head(10).plot(kind='bar', x='name', y='revenue')
plt.title('Top Products by Revenue')
plt.tight_layout()
plt.show()

df.to_sql('product_summary', conn, if_exists='replace', index=False)

Frequently Asked Questions

Should I use mysql-connector-python or PyMySQL?

Both implement the DB-API 2.0 spec. mysql-connector-python is the official Oracle connector. PyMySQL is pure Python and easier to install. I recommend PyMySQL for compatibility and mysql-connector for performance.

How do I handle character encoding issues?

Set charset='utf8mb4' in the connect call and ensure the database and tables use utf8mb4 as the default charset.

What is the difference between autocommit and explicit transactions?

With autocommit=True, each DML statement is committed immediately. With explicit transactions, you control when changes are committed, allowing rollback on error.

How do I avoid 'MySQL has gone away' errors?

Use connection pooling, set wait_timeout and interactive_timeout in MySQL config, and add retry logic. Pooled connections are automatically validated before use.

Originally published on Ayodhyya. Last updated June 1, 2026.