Offensive Security Researcher & Quantum Cryptography Analyst

Accessing MySQL Database Using MySQLdb in Python: Complete Database Connection and CRUD Operations Guide

Abdul Wahab Junaid
Accessing MySQL database using MySQLdb in python

Accessing MySQL database using MySQLdb in python

MySQL was the very first real database I connected Python to, years ago, on a small web project running on shared hosting. MySQLdb (formally mysqlclient in its modern maintained form) was the library everyone recommended at the time, and while the Python database ecosystem has grown since then, understanding this driver thoroughly gave me a solid foundation for every other database library I’ve picked up since — they all share the same underlying DB-API 2.0 shape. Here’s the complete guide.

A Naming Clarification Worth Understanding Upfront

The original MySQLdb package (sometimes called MySQL-python) was written for Python 2 and was never officially ported to Python 3. The actively maintained successor, which provides the same MySQLdb module name for import compatibility, is the mysqlclient package on PyPI.

pip install mysqlclient

import MySQLdb  # the import name stays MySQLdb even though the installed package is mysqlclient

This is a genuinely common point of confusion for people newer to the Python MySQL ecosystem — searching for “MySQLdb” on PyPI directly can lead to the outdated, Python 2-only original package, when what you actually want to pip install is mysqlclient.

Establishing a Connection

import MySQLdb

conn = MySQLdb.connect(
    host="localhost",
    user="myuser",
    passwd="mypassword",
    db="mydatabase",
    port=3306
)

cursor = conn.cursor()

Note the parameter name passwd, not password — a small but real inconsistency with some other Python database libraries (psycopg2 uses password), which has tripped me up more than once when switching between projects using different databases.

Executing Basic Queries

cursor.execute("SELECT id, name, email FROM users WHERE active = %s", (1,))

rows = cursor.fetchall()
for row in rows:
    print(row)

Like psycopg2, MySQLdb uses %s as its parameter placeholder regardless of the underlying data type — and just like with psycopg2, this is not Python’s % string formatting operator, even though it looks similar. The driver intercepts these placeholders and handles escaping safely.

# NEVER build queries with string formatting or concatenation:
name = "Alice"
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")  # SQL injection risk

# Always use parameterized queries instead:
cursor.execute("SELECT * FROM users WHERE name = %s", (name,))

Fetching Results

cursor.execute("SELECT id, name FROM users")

one_row = cursor.fetchone()
cursor.execute("SELECT id, name FROM users")
five_rows = cursor.fetchmany(5)
cursor.execute("SELECT id, name FROM users")
all_rows = cursor.fetchall()

By default, MySQLdb returns rows as plain tuples. For dictionary-style access by column name, I use MySQLdb.cursors.DictCursor.

import MySQLdb
import MySQLdb.cursors

conn = MySQLdb.connect(
    host="localhost", user="myuser", passwd="mypassword", db="mydatabase",
    cursorclass=MySQLdb.cursors.DictCursor
)

cursor = conn.cursor()
cursor.execute("SELECT id, name, email FROM users")

for row in cursor.fetchall():
    print(row["name"], row["email"])

Full CRUD Operations

Create (Insert)

cursor.execute(
    "INSERT INTO users (name, email, active) VALUES (%s, %s, %s)",
    ("Alice Johnson", "alice@example.com", 1)
)
conn.commit()

# Getting the auto-incremented ID of the just-inserted row
new_id = cursor.lastrowid
print(f"New user ID: {new_id}")

Unlike PostgreSQL’s RETURNING clause, MySQL doesn’t support returning values directly from an INSERT statement in the same way — instead, cursor.lastrowid (a MySQLdb-specific attribute, part of the broader DB-API convention) gives you the auto-incremented primary key of the row you just inserted, assuming the table has an auto-increment column.

Read (Select)

cursor.execute("SELECT * FROM users WHERE active = %s", (1,))
active_users = cursor.fetchall()

for user in active_users:
    print(user)

Update

cursor.execute(
    "UPDATE users SET active = %s WHERE id = %s",
    (0, 42)
)
conn.commit()
print(f"Rows affected: {cursor.rowcount}")

cursor.rowcount after an UPDATE (or DELETE) tells you exactly how many rows were affected — genuinely useful for confirming an operation did what you expected, especially when debugging a query that unexpectedly updates zero rows (often a sign the WHERE clause didn’t match anything).

Delete

cursor.execute("DELETE FROM users WHERE active = %s", (0,))
conn.commit()
print(f"Deleted {cursor.rowcount} rows")

Committing and Transactions

Like psycopg2 and pyodbc, MySQLdb requires an explicit conn.commit() after write operations — forgetting this is, once again, the most common source of “my insert didn’t work” confusion.

try:
    cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1))
    cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 2))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Transaction failed and was rolled back: {e}")

One MySQL-specific nuance worth knowing: whether transactions behave with full ACID guarantees depends on the storage engine of the tables involved — InnoDB (the modern default in current MySQL versions) supports proper transactions with rollback; the older MyISAM engine does not support transactions at all, and a rollback() call against MyISAM tables silently does nothing. Always confirm your tables use InnoDB if transactional integrity matters.

cursor.execute("SHOW TABLE STATUS WHERE Name = %s", ("users",))
print(cursor.fetchone())  # check the 'Engine' column in the result

Batch Inserts

data = [
    ("Alice", "alice@example.com"),
    ("Bob", "bob@example.com"),
    ("Carol", "carol@example.com"),
]

cursor.executemany(
    "INSERT INTO users (name, email) VALUES (%s, %s)",
    data
)
conn.commit()

MySQLdb‘s executemany() is genuinely optimized under the hood — for simple INSERT statements, it automatically rewrites multiple inserts into a single multi-row INSERT statement where possible, which is more efficient than issuing one round trip per row. This is a meaningful advantage over drivers where executemany() is a naive wrapper around a loop of individual execute() calls.

Using Context Managers and Proper Cleanup

import MySQLdb

conn = MySQLdb.connect(host="localhost", user="myuser", passwd="mypassword", db="mydatabase")

try:
    with conn.cursor() as cursor:
        cursor.execute("SELECT COUNT(*) FROM users")
        print(cursor.fetchone())
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Error: {e}")
finally:
    conn.close()

MySQLdb cursors support the context manager protocol for automatic cleanup, but — similar to psycopg2 — the connection itself still needs to be closed explicitly; it isn’t automatically closed just by using with on the cursor.

Handling Errors

import MySQLdb

try:
    conn = MySQLdb.connect(host="localhost", user="myuser", passwd="wrongpassword", db="mydatabase")
except MySQLdb.OperationalError as e:
    print(f"Connection failed: {e}")

try:
    cursor.execute("SELECT * FROM nonexistent_table")
except MySQLdb.ProgrammingError as e:
    print(f"SQL error: {e}")

Following the same DB-API 2.0 exception hierarchy as other Python database drivers, MySQLdb distinguishes between connection-level problems (OperationalError) and SQL syntax or logic problems (ProgrammingError), among other specific exception types — catching the right level of specificity lets your application respond appropriately (retrying a transient connection error versus surfacing a genuine bug in a query).

Using MySQLdb with pandas

import pandas as pd
import MySQLdb

conn = MySQLdb.connect(host="localhost", user="myuser", passwd="mypassword", db="mydatabase")

df = pd.read_sql("SELECT * FROM sales WHERE year = %s", conn, params=(2026,))
print(df.head())

This is a common practical use case — pulling MySQL data straight into a pandas DataFrame for analysis, exactly as with the other database drivers covered in this series.

Alternatives Worth Knowing About

MySQLdb/mysqlclient is a solid, mature, C-extension-based driver, but it’s not the only option in the Python MySQL ecosystem:

import pymysql  # drop-in-ish alternative with a very similar API shape

conn = pymysql.connect(host="localhost", user="myuser", password="mypassword", database="mydatabase")

I generally default to mysqlclient for performance in typical server environments, but reach for PyMySQL specifically when I need pure-Python portability (like in constrained deployment environments without a C compiler available).

Security Considerations

Always use parameterized queries, never string formatting or concatenation, exactly as emphasized for every database driver in this series.

Use SSL for remote connections:

conn = MySQLdb.connect(
    host="remote-host", user="myuser", passwd="mypassword", db="mydatabase",
    ssl={"ca": "/path/to/ca-cert.pem"}
)

Keep credentials out of source code:

import os
import MySQLdb

conn = MySQLdb.connect(
    host=os.environ["DB_HOST"],
    user=os.environ["DB_USER"],
    passwd=os.environ["DB_PASSWORD"],
    db=os.environ["DB_NAME"],
)

Real-World Applications

Common Mistakes

Installing the outdated MySQL-python package instead of mysqlclient when searching for “MySQLdb” — this leads to Python 2-only code that won’t even import under Python 3.

Confusing the passwd parameter name with password, a small but genuinely common typo when switching between MySQL and other database libraries.

Forgetting that MyISAM tables don’t support transactions, being surprised when a rollback() doesn’t actually undo changes made to MyISAM-engine tables.

Forgetting conn.commit() after write operations, the same recurring mistake seen across every DB-API driver.

Using %s-style placeholders incorrectly, mixing them up with Python string formatting, risking SQL injection if user input is ever concatenated directly instead.

Debugging Tips

Performance Considerations

FAQs

Is MySQLdb the same as mysqlclient? mysqlclient is the actively maintained Python 3-compatible package you install; it provides the MySQLdb module for import, preserving compatibility with code written against the original library.

Why do I get lastrowid instead of a RETURNING clause like PostgreSQL? MySQL doesn’t support returning arbitrary column values directly from an INSERT the way PostgreSQL’s RETURNING does; cursor.lastrowid is the standard way to retrieve an auto-incremented primary key after an insert.

Should I use mysqlclient or PyMySQL? mysqlclient (C-extension based) is generally faster; PyMySQL (pure Python) is easier to install in environments without a C compiler and is fully portable. Both implement a very similar DB-API-style interface.

Do transactions actually work in MySQL? Yes, but only for tables using a transactional storage engine like InnoDB — MyISAM tables do not support transactions, and rollback() has no effect on changes made to them.

Summary

Accessing MySQL from Python via MySQLdb (installed as the mysqlclient package) follows the same general DB-API 2.0 pattern shared across Python’s database drivers — parameterized queries with %s placeholders, explicit commits, and cursor-based result fetching — with a few MySQL-specific details worth knowing, like the passwd parameter name, cursor.lastrowid for auto-increment values instead of a RETURNING clause, and the crucial distinction between transactional InnoDB tables and non-transactional MyISAM tables. Getting the fundamentals right — parameterization, commits, and proper error handling — carries directly over to every other database driver you’ll encounter in Python.

References

Exit mobile version