Connecting Python to MySQL

MySQL is one of the most widely used database servers for web applications. Python connects to it with a driver package — the official mysql-connector-python from Oracle, or the pure-Python PyMySQL. Both follow the DB-API you learned with sqlite3, so the code looks almost the same; the main differences are connection settings, the %s placeholder style, and running a separate server.

These examples need a running MySQL server and the driver installed, so run them on your own computer.

Setup

Install MySQL Server (or run it with Docker: docker run -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 mysql:8.4).
Create a database and a dedicated user: CREATE DATABASE shop; CREATE USER 'shop_app'@'%' IDENTIFIED BY '...'; GRANT ALL ON shop.* TO 'shop_app'@'%';
Install the driver into your virtual environment: pip install mysql-connector-python (or pip install pymysql).
Keep the password out of your code — read it from an environment variable or a secrets manager.

Differences from sqlite3

Placeholders are %s (or %(name)s) for every type — never use Python's % operator yourself. Autocommit is off by default, so call conn.commit(). conn.cursor(dictionary=True) returns rows as dictionaries. AUTO_INCREMENT ids are available from cursor.lastrowid. For web applications, use a connection pool so requests reuse connections instead of opening a new one each time.

Examples

Connecting and handling connection errors

Python

# pip install mysql-connector-python
import os
import mysql.connector
from mysql.connector import errorcode

config = {
    "host": "localhost",
    "port": 3306,
    "user": "shop_app",
    "password": os.environ["DB_PASSWORD"],     # never hard-code passwords
    "database": "shop",
}

try:
    conn = mysql.connector.connect(**config)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Wrong user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    print("connected:", conn.is_connected(), "| server version:", conn.get_server_info())
    conn.close()

Output

connected: True | server version: 8.4.2

Create a table and perform CRUD with %s placeholders

Python

import os
import mysql.connector

conn = mysql.connector.connect(host="localhost", user="shop_app",
                               password=os.environ["DB_PASSWORD"], database="shop")
cur = conn.cursor(dictionary=True)             # rows as dicts

cur.execute("""
    CREATE TABLE IF NOT EXISTS customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE,
        city VARCHAR(50)
    )
""")

cur.execute("INSERT INTO customers (name, email, city) VALUES (%s, %s, %s)",
            ("Asha", "asha@example.com", "Pune"))
print("new id:", cur.lastrowid)
cur.executemany("INSERT INTO customers (name, email, city) VALUES (%s, %s, %s)", [
    ("Ravi", "ravi@example.com", "Mumbai"),
    ("Meera", "meera@example.com", "Pune"),
])
conn.commit()                                  # autocommit is off by default

cur.execute("SELECT id, name, city FROM customers WHERE city = %s ORDER BY name", ("Pune",))
for row in cur.fetchall():
    print(row)

cur.execute("UPDATE customers SET city = %s WHERE email = %s", ("Nagpur", "ravi@example.com"))
print("updated:", cur.rowcount)
cur.execute("DELETE FROM customers WHERE email = %s", ("meera@example.com",))
print("deleted:", cur.rowcount)
conn.commit()

cur.close()
conn.close()

Output

new id: 1
{'id': 1, 'name': 'Asha', 'city': 'Pune'}
{'id': 3, 'name': 'Meera', 'city': 'Pune'}
updated: 1
deleted: 1

Transactions with a connection pool

Python

import os
import mysql.connector
from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
    pool_name="shop_pool",
    pool_size=5,
    host="localhost",
    user="shop_app",
    password=os.environ["DB_PASSWORD"],
    database="shop",
)

def place_order(customer_id, items):
    conn = pool.get_connection()
    try:
        conn.start_transaction()
        cur = conn.cursor()
        cur.execute("INSERT INTO orders (customer_id) VALUES (%s)", (customer_id,))
        order_id = cur.lastrowid
        cur.executemany(
            "INSERT INTO order_items (order_id, product, quantity) VALUES (%s, %s, %s)",
            [(order_id, product, qty) for product, qty in items],
        )
        conn.commit()                  # the order and all its items, or nothing
        return order_id
    except mysql.connector.Error:
        conn.rollback()
        raise
    finally:
        conn.close()                   # returns the connection to the pool

print("order", place_order(1, [("Pen", 3), ("Notebook", 2)]), "placed")

Output

order 101 placed

The same queries with PyMySQL

Python

# pip install pymysql
import os
import pymysql

conn = pymysql.connect(host="localhost", user="shop_app", password=os.environ["DB_PASSWORD"],
                       database="shop", cursorclass=pymysql.cursors.DictCursor)
with conn:
    with conn.cursor() as cur:
        cur.execute("SELECT name, city FROM customers WHERE id = %s", (1,))
        print(cur.fetchone())

Output

{'name': 'Asha', 'city': 'Pune'}

Common Mistakes

Using ? placeholders (sqlite3 style) with MySQL drivers, which expect %s.
Formatting values into the query with % or f-strings instead of passing them as the second argument.
Forgetting conn.commit() because autocommit is off.
Hard-coding database passwords in source code or committing them to Git.
Opening a new connection for every request in a web app instead of using a pool.

Key Points to Remember

mysql-connector-python and PyMySQL are DB-API drivers for MySQL.
Use %s / %(name)s placeholders and pass values separately.
Commit explicitly; roll back on errors.
cursor(dictionary=True) or DictCursor returns rows as dictionaries.
Use connection pooling and environment variables for credentials in real applications.

Practice the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

Use your local project environment for these examples. Codelab currently runs Python and HTML/CSS/JavaScript; framework examples may need project dependencies.