ZetCode

Python PyMySQL

last modified November 18, 2026 Python 3.15

In this article we show how to work with MySQL databases in Python using the PyMySQL module.

PyMySQL is a MySQL client library written in pure Python. It follows the Python Database API Specification v2.0 (PEP 249) and has no dependencies on C libraries, so it installs anywhere Python runs. It works with MySQL and MariaDB servers. Its API is largely compatible with mysqlclient, the maintained successor of the old MySQLdb module.

The examples were tested with Python 3.15, PyMySQL 1.2.4, and MySQL 9.7 on Linux. We manage the project and its dependencies with uv.

MySQL

MySQL is a leading open source relational database management system. It is a multiuser, multithreaded database server, especially popular in web applications. MariaDB is a community-developed fork of MySQL; all examples in this article work with it too.

For the examples, we need a running MySQL server with a testdb database and a user that can access it. The quickest way to get one is a Docker container. The official mysql image creates the database given in MYSQL_DATABASE and the user given in MYSQL_USER and MYSQL_PASSWORD, and grants the user all privileges on that database.

$ docker run -d --name mysql -e MYSQL_ROOT_PASSWORD='r00t' \
    -e MYSQL_DATABASE=testdb -e MYSQL_USER=user7 \
    -e MYSQL_PASSWORD='s$cret' -p 3306:3306 mysql:9

Setting up the project with uv

We create a new project with uv and add PyMySQL to it. The uv init --bare command creates only the pyproject.toml file, without sample code. The uv add command creates a virtual environment in the .venv directory, installs the package into it, and records the dependency in pyproject.toml and in the uv.lock lockfile.

We run the examples with uv run. It makes sure the virtual environment matches the lockfile and runs the script with the project's interpreter; there is no need to activate the environment.

$ uv init --bare mysqltut
Initialized project `mysqltut` at `/home/user/mysqltut`
$ cd mysqltut
$ uv add pymysql
Using CPython 3.15.2
Creating virtual environment at: .venv
Resolved 2 packages in 1ms
Installed 1 package in 1ms
 + pymysql==1.2.4
$ cat pyproject.toml
[project]
name = "mysqltut"
version = "0.1.0"
requires-python = ">=3.15"
dependencies = [
    "pymysql>=1.2.4",
]

MySQL version

In the first example, we get the version of the MySQL server.

version.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('SELECT VERSION()')
        version = cur.fetchone()

        print(f'Database version: {version[0]}')

print('PyMySQL version:', pymysql.VERSION_STRING)
print('Connection open:', con.open)

We connect to the testdb database, execute a query, and print the result.

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

The connect function opens a connection to the server and returns a Connection object. Since PyMySQL 1.0, the arguments must be passed as keywords; the positional form connect('localhost', 'user7', ...) found in older code fails with a TypeError. The port defaults to 3306.

with con:
    with con.cursor() as cur:

When used in a with statement, the connection is closed at the end of the block. Note that it does not commit; we must call commit ourselves when we change data. The cursor method returns a cursor, which executes statements and fetches results. The cursor is closed at the end of its own with block.

cur.execute('SELECT VERSION()')
version = cur.fetchone()

The execute method runs the SQL statement. The fetchone method returns the next row of the result as a tuple.

print('PyMySQL version:', pymysql.VERSION_STRING)
print('Connection open:', con.open)

The VERSION_STRING constant holds the PyMySQL version. (The pymysql.__version__ attribute contains a fake mysqlclient version for compatibility.) The open attribute shows that the connection was closed by the with statement.

$ uv run version.py
Database version: 9.7.4
PyMySQL version: 1.2.4
Connection open: False

Connection details

The connection object provides several methods to inspect the session.

connection_info.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    print('server:', con.get_server_info())
    print('host:', con.get_host_info())
    print('charset:', con.charset)
    print('autocommit:', con.get_autocommit())

    with con.cursor() as cur:
        cur.execute("SHOW SESSION STATUS LIKE 'Ssl_version'")
        print(cur.fetchone())

The get_server_info method returns the server version, get_host_info describes the transport, and the charset attribute shows the connection character set. PyMySQL uses utf8mb4 by default, which covers all Unicode characters. The get_autocommit method tells us that autocommit is off.

cur.execute("SHOW SESSION STATUS LIKE 'Ssl_version'")

Without any TLS options, PyMySQL 1.2 uses the preferred mode: if the server supports TLS, the connection is encrypted, but the server certificate is not verified. To require a verified connection, we pass ssl_ca with the CA certificate and ssl_verify_identity=True; ssl_disabled=True turns encryption off.

MySQL 8 and later authenticate users with the caching_sha2_password plugin. Over an unencrypted connection, this plugin needs RSA encryption, and PyMySQL then fails with 'cryptography' package is required. In such a case, we install the extra with uv add "pymysql[rsa]".

$ uv run connection_info.py
server: 9.7.4
host: socket localhost:3306
charset: utf8mb4
autocommit: False
('Ssl_version', 'TLSv1.3')

Option files

Instead of writing the connection parameters in every program, we can keep them in a MySQL option file. The same file can be used by the mysql command line client.

my.cnf
[client]
host = localhost
user = user7
password = s$cret
database = testdb

The [client] group holds the parameters. Since the file contains a password, it should be readable only by its owner (chmod 600 my.cnf).

option_file.py
import pymysql

con = pymysql.connect(read_default_file='my.cnf')

with con:
    with con.cursor() as cur:
        cur.execute('SELECT CURRENT_USER(), DATABASE()')
        print(cur.fetchone())

The read_default_file parameter tells PyMySQL to read the parameters from the given file; the group is selected with read_default_group, which defaults to client. Keyword arguments passed to connect override the values from the file.

$ uv run option_file.py
('user7@%', 'testdb')

Creating a table

In this example, we create the cities table and fill it with data. The table is used in the following examples.

create_table.py
import pymysql

cities = [
    ('Bratislava', 432000), ('Budapest', 1759000),
    ('Prague', 1280000), ('Warsaw', 1748000),
    ('Los Angeles', 3971000), ('New York', 8550000),
    ('Edinburgh', 464000), ('Berlin', 3671000),
]

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('DROP TABLE IF EXISTS cities')
        cur.execute('''CREATE TABLE cities(
                           id INT PRIMARY KEY AUTO_INCREMENT,
                           name VARCHAR(255) NOT NULL UNIQUE,
                           population INT)''')

        cur.executemany('INSERT INTO cities(name, population) VALUES(%s, %s)',
                        cities)
        print(f'{cur.rowcount} rows inserted')

    con.commit()

We drop the table if it exists, create it, and insert eight rows.

cur.execute('''CREATE TABLE cities(
                   id INT PRIMARY KEY AUTO_INCREMENT,
                   name VARCHAR(255) NOT NULL UNIQUE,
                   population INT)''')

The id column gets its values automatically. The name column has a unique constraint, which we use later to provoke errors.

cur.executemany('INSERT INTO cities(name, population) VALUES(%s, %s)',
                cities)

The executemany method executes the statement for each tuple in the sequence. For INSERT and REPLACE statements, PyMySQL is smart: it rewrites them into a single multi-row INSERT ... VALUES (...), (...) statement, which is much faster than separate round trips. The rowcount attribute holds the number of inserted rows.

con.commit()

Autocommit is off by default, so the inserted rows become permanent only after we call commit. The DROP TABLE and CREATE TABLE statements are committed implicitly by MySQL.

$ uv run create_table.py
8 rows inserted

Fetching all rows

The fetchall method retrieves all (remaining) rows of a query result.

fetch_all.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('SELECT * FROM cities')
        rows = cur.fetchall()

        for row in rows:
            print(f'{row[0]} {row[1]} {row[2]}')

We retrieve all cities from the table. The fetchall method returns a tuple of tuples; each inner tuple represents one row.

$ uv run fetch_all.py
1 Bratislava 432000
2 Budapest 1759000
3 Prague 1280000
4 Warsaw 1748000
5 Los Angeles 3971000
6 New York 8550000
7 Edinburgh 464000
8 Berlin 3671000

Fetching one row

The fetchone method returns the next row, or None when there are no more rows.

fetch_one.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('SELECT name, population FROM cities WHERE id < 4')

        while (row := cur.fetchone()) is not None:
            print(row)

        print(cur.fetchone())

We fetch the rows one by one in a while loop with the walrus operator. After the last row, fetchone keeps returning None.

$ uv run fetch_one.py
('Bratislava', 432000)
('Budapest', 1759000)
('Prague', 1280000)
None

Fetching rows in batches

The fetchmany method returns the next batch of rows. Its size is given by the argument or, if omitted, by the arraysize attribute of the cursor, which is 1 by default.

fetch_many.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('SELECT id, name FROM cities ORDER BY id')

        while rows := cur.fetchmany(3):
            print(rows)

We fetch the rows in batches of three. The last batch is shorter, and then fetchmany returns an empty tuple, which ends the loop.

$ uv run fetch_many.py
((1, 'Bratislava'), (2, 'Budapest'), (3, 'Prague'))
((4, 'Warsaw'), (5, 'Los Angeles'), (6, 'New York'))
((7, 'Edinburgh'), (8, 'Berlin'))

Iterating over a cursor

A cursor is iterable; it yields the rows of the result one by one.

iterate_cursor.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('SELECT name, population FROM cities ORDER BY population DESC')

        for name, population in cur:
            print(f'{name:<12} {population:>10_}')

We loop over the cursor and unpack each row into two variables. The _ option in the format specification adds thousands separators.

$ uv run iterate_cursor.py
New York      8_550_000
Los Angeles   3_971_000
Berlin        3_671_000
Budapest      1_759_000
Warsaw        1_748_000
Prague        1_280_000
Edinburgh       464_000
Bratislava      432_000

Dictionary cursor

The default cursor returns rows as tuples. A dictionary cursor returns them as dictionaries, so we can refer to the values by column names.

dict_cursor.py
import pymysql
import pymysql.cursors

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb',
                      cursorclass=pymysql.cursors.DictCursor)

with con:
    with con.cursor() as cur:
        cur.execute('SELECT * FROM cities LIMIT 3')
        rows = cur.fetchall()

        print(rows[0])

        for row in rows:
            print(row['id'], row['name'])

We pass the pymysql.cursors.DictCursor class to the cursorclass parameter of connect, so all cursors of the connection return dictionaries. We can also choose the class for a single cursor with con.cursor(pymysql.cursors.DictCursor).

$ uv run dict_cursor.py
{'id': 1, 'name': 'Bratislava', 'population': 432000}
1 Bratislava
2 Budapest
3 Prague

Column headers

The column names are part of the result metadata. We get them from the description attribute of the cursor.

column_headers.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('SELECT * FROM cities')
        rows = cur.fetchall()
        desc = cur.description

        print(f'{desc[0][0]:<8} {desc[1][0]:<15} {desc[2][0]:>10}')

        for row in rows:
            print(f'{row[0]:<8} {row[1]:<15} {row[2]:>10}')

The description attribute is a sequence with one tuple per result column; the first item of each tuple is the column name. We print the names as a header and then the data, formatted into columns.

$ uv run column_headers.py
id       name            population
1        Bratislava          432000
2        Budapest           1759000
3        Prague             1280000
4        Warsaw             1748000
5        Los Angeles        3971000
6        New York           8550000
7        Edinburgh           464000
8        Berlin             3671000

Column metadata

Each item of description is a seven-item tuple, as defined by PEP 249: name, type code, display size, internal size, precision, scale, and whether the column accepts NULL.

column_info.py
import pymysql
from pymysql.constants import FIELD_TYPE

types = {v: k for k, v in vars(FIELD_TYPE).items() if k.isupper()}

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('SELECT * FROM cities LIMIT 1')

        for col in cur.description:
            name, type_code, _, size, _, _, null_ok = col
            print(f'{name:<12} {types[type_code]:<12} {size:>5} {null_ok}')

The type codes are numbers; we translate them to names with the constants of the pymysql.constants.FIELD_TYPE module. MySQL reports INT as LONG and VARCHAR as VAR_STRING. The size of the name column is 1020 bytes, because a utf8mb4 character takes up to four bytes.

$ uv run column_info.py
id           LONG            11 False
name         VAR_STRING    1020 False
population   LONG            11 True

Parameterized queries

Values should never be put into SQL statements with string formatting. We pass them as a second argument to execute; PyMySQL escapes them and puts them into the placeholders.

parameters.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

city_id = 4
min_pop, max_pop = 1_000_000, 2_000_000

with con:
    with con.cursor() as cur:
        cur.execute('SELECT * FROM cities WHERE id = %s', (city_id,))
        print(cur.fetchone())

        cur.execute('''SELECT name FROM cities
                       WHERE population BETWEEN %(low)s AND %(high)s''',
                    {'low': min_pop, 'high': max_pop})
        print(cur.fetchall())

PyMySQL supports two placeholder styles. With a tuple of parameters, we use %s markers. With a dictionary, we use named markers such as %(low)s. The marker is always %s, whatever the type of the value; PyMySQL converts integers, strings, dates, and other types itself.

cur.execute('SELECT * FROM cities WHERE id = %s', (city_id,))

The parameters are given as a one-element tuple. PyMySQL also accepts a bare value for a single parameter, but a tuple is clearer and works with all PEP 249 drivers.

$ uv run parameters.py
(4, 'Warsaw', 1748000)
(('Budapest',), ('Prague',), ('Warsaw',))

IN and LIKE

A tuple or list passed as a parameter is converted to a parenthesized list of values, which is what the IN operator expects.

in_list.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

names = ('Prague', 'Berlin', 'Rome')

with con:
    with con.cursor() as cur:
        cur.execute('SELECT id, name FROM cities WHERE name IN %s', (names,))
        print(cur.fetchall())

        cur.execute('SELECT name FROM cities WHERE name LIKE %s', ('B%',))
        print(cur.fetchall())

We select the cities whose names are in the tuple; Rome is not in the table. Note the double wrapping: names is one parameter, so it goes into the parameters tuple. With LIKE, the wildcard characters are part of the parameter value, not of the SQL text.

$ uv run in_list.py
((8, 'Berlin'), (3, 'Prague'))
(('Berlin',), ('Bratislava',), ('Budapest',))

SQL injection

In this example, we compare a query built with an f-string with a parameterized query. The mogrify method returns the statement that would be sent to the server, after the parameters are escaped.

mogrify.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

user_input = "Bratislava' OR '1'='1"

with con:
    with con.cursor() as cur:
        unsafe = f"SELECT * FROM cities WHERE name = '{user_input}'"
        cur.execute(unsafe)
        print('formatted:', cur.rowcount, 'rows')

        sql = 'SELECT * FROM cities WHERE name = %s'
        print(cur.mogrify(sql, (user_input,)))

        cur.execute(sql, (user_input,))
        print('parameterized:', cur.rowcount, 'rows')

The user input contains a quote that ends the string literal and an OR condition that is always true.

unsafe = f"SELECT * FROM cities WHERE name = '{user_input}'"
cur.execute(unsafe)

When the input is pasted into the SQL text, the condition becomes name = 'Bratislava' OR '1'='1', and the query returns all rows of the table. This is SQL injection.

print(cur.mogrify(sql, (user_input,)))

The mogrify method shows that PyMySQL escapes the quotes with backslashes. The whole input is a single string literal, and no city has such a name, so the parameterized query returns no rows.

$ uv run mogrify.py
formatted: 8 rows
SELECT * FROM cities WHERE name = 'Bratislava\' OR \'1\'=\'1'
parameterized: 0 rows

Inserting a row

A new row is inserted with the INSERT INTO statement. The value of the AUTO_INCREMENT column is available after the insert.

insert_row.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('INSERT INTO cities(name, population) VALUES(%s, %s)',
                    ('Kyiv', 2952000))

        print('new city id:', cur.lastrowid)
        print('insert_id:', con.insert_id())

    con.commit()

We insert a new city and print its id.

print('new city id:', cur.lastrowid)
print('insert_id:', con.insert_id())

The lastrowid attribute of the cursor contains the id generated by the last statement of that cursor. The insert_id method of the connection returns the same value; it corresponds to the LAST_INSERT_ID SQL function.

con.commit()

Without the commit call, the new row would be discarded when the connection is closed.

$ uv run insert_row.py
new city id: 9
insert_id: 9

Affected rows

For UPDATE and DELETE statements, the rowcount attribute holds the number of affected rows. The execute method returns the same number.

update_rows.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        sql = 'UPDATE cities SET population = %s WHERE name = %s'

        n = cur.execute(sql, (1_300_000, 'Prague'))
        print('first update:', n)

        n = cur.execute(sql, (1_300_000, 'Prague'))
        print('second update:', n)

        cur.execute('DELETE FROM cities WHERE population < %s', (500_000,))
        print('deleted:', cur.rowcount)

    con.commit()

We update the population of Prague twice and delete small cities.

n = cur.execute(sql, (1_300_000, 'Prague'))
print('second update:', n)

MySQL counts only rows that were actually changed. The second update sets the same value again, so it reports zero rows, although the WHERE clause matched one row. To get the number of matched rows instead, we pass client_flag=pymysql.constants.CLIENT.FOUND_ROWS to connect.

$ uv run update_rows.py
first update: 1
second update: 0
deleted: 2

Inserting an image

Binary data is stored in BLOB columns. A BLOB holds up to 64 KB, MEDIUMBLOB up to 16 MB, and LONGBLOB up to 4 GB.

insert_image.py
import pymysql

with open('mountains.jpg', 'rb') as f:
    data = f.read()

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('DROP TABLE IF EXISTS images')
        cur.execute('''CREATE TABLE images(id INT PRIMARY KEY AUTO_INCREMENT,
                                           name VARCHAR(255), data MEDIUMBLOB)''')

        cur.execute('INSERT INTO images(name, data) VALUES(%s, %s)',
                    ('mountains.jpg', data))

        cur.execute('SELECT LENGTH(data) FROM images WHERE id = %s',
                    (cur.lastrowid,))
        print(f'stored {cur.fetchone()[0]} bytes')

    con.commit()

We read an image file and insert its contents into the images table. A bytes object is passed as a parameter like any other value; PyMySQL puts it into the statement as a hexadecimal literal (X'...').

cur.execute('SELECT LENGTH(data) FROM images WHERE id = %s',
            (cur.lastrowid,))

To check the result, we let the server compute the length of the stored data.

$ uv run insert_image.py
stored 23917 bytes

Reading an image

In this example, we read the image back from the table.

read_image.py
import hashlib
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

with con:
    with con.cursor() as cur:
        cur.execute('SELECT name, data FROM images WHERE id = 1')
        name, data = cur.fetchone()

print(type(data).__name__, len(data))

with open('copy.jpg', 'wb') as f:
    f.write(data)

for fname in (name, 'copy.jpg'):
    with open(fname, 'rb') as f:
        print(fname, hashlib.sha256(f.read()).hexdigest()[:16])

A BLOB value comes back as a bytes object. We write it to copy.jpg and compare the SHA-256 hashes of the two files. They are equal, so the data survived the round trip.

$ uv run read_image.py
bytes 23917
mountains.jpg dcdcb94c540268ad
copy.jpg dcdcb94c540268ad

Transactions

A transaction is a group of statements that either all succeed or have no effect. With autocommit off, PyMySQL statements run in a transaction that lasts until we call commit or rollback.

transaction.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

def count_cities(cur):
    cur.execute('SELECT COUNT(*) FROM cities')
    return cur.fetchone()[0]

with con:
    with con.cursor() as cur:
        print('before:', count_cities(cur))

        try:
            con.begin()
            cur.execute("INSERT INTO cities(name, population) VALUES('Vienna', 2006000)")
            cur.execute("INSERT INTO cities(name, population) VALUES('Berlin', 3878000)")
            con.commit()
        except pymysql.err.IntegrityError as e:
            con.rollback()
            print('rolled back:', e)

        print('after:', count_cities(cur))

We try to insert two cities in one transaction. The second insert fails, because Berlin is already in the table and the name column is unique.

con.begin()

The begin method starts a transaction explicitly. With autocommit off, it is optional, but it makes the start of the transaction clear.

except pymysql.err.IntegrityError as e:
    con.rollback()

The rollback method cancels the changes of the transaction, so Vienna is not stored either. The number of cities is the same before and after the transaction. The tables must use a transactional storage engine, which is the default InnoDB engine.

$ uv run transaction.py
before: 7
rolled back: (1062, "Duplicate entry 'Berlin' for key 'cities.name'")
after: 7

Uncommitted changes and autocommit

In this example, we show that uncommitted changes are not visible to other connections and that they are lost when the connection closes without a commit. Then we turn autocommit on.

uncommitted.py
import pymysql

def connect(**kwargs):
    return pymysql.connect(host='localhost', user='user7',
                           password='s$cret', database='testdb', **kwargs)

def count_cities():
    with connect() as con, con.cursor() as cur:
        cur.execute('SELECT COUNT(*) FROM cities')
        return cur.fetchone()[0]

with connect() as con, con.cursor() as cur:
    cur.execute("INSERT INTO cities(name, population) VALUES('Vienna', 2006000)")
    print('inside, not committed:', count_cities())

print('after close:', count_cities())

with connect(autocommit=True) as con, con.cursor() as cur:
    cur.execute("INSERT INTO cities(name, population) VALUES('Vienna', 2006000)")
    print('autocommit:', count_cities())

The count_cities function opens its own connection, so it sees only committed data. Two context managers in one with statement close the cursor first and then the connection.

with connect() as con, con.cursor() as cur:
    cur.execute("INSERT INTO cities(name, population) VALUES('Vienna', 2006000)")
    print('inside, not committed:', count_cities())

The inserted row is not visible to the second connection. When the with block closes the connection, the server rolls back the open transaction and the row is gone.

with connect(autocommit=True) as con, con.cursor() as cur:

With autocommit=True, each statement is committed as soon as it is executed. Autocommit can also be switched on an open connection with the autocommit method.

$ uv run uncommitted.py
inside, not committed: 7
after close: 7
autocommit: 8

Handling errors

PyMySQL raises the exceptions defined by PEP 249. All of them are subclasses of pymysql.MySQLError. The exception arguments are the MySQL error code and the error message.

errors.py
import pymysql

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb')

statements = [
    "INSERT INTO cities(name, population) VALUES('Prague', 1)",
    'SELECT * FROM towns',
    'SELECT nme FROM cities',
    'SELECT 1 / 0',
]

with con:
    with con.cursor() as cur:
        for sql in statements:
            try:
                cur.execute(sql)
                print('ok:', cur.fetchall())
            except pymysql.MySQLError as e:
                code, message = e.args
                print(f'{type(e).__name__} {code}: {message}')

try:
    pymysql.connect(host='localhost', user='user7',
                    password='wrong', database='testdb')
except pymysql.err.OperationalError as e:
    print(e)

We execute several statements that fail for different reasons and print the exception class, the error code, and the message.

PyMySQL maps MySQL error codes to the exception classes. A duplicate key raises IntegrityError and a missing table ProgrammingError, but an unknown column is reported as OperationalError, so it is safer to catch MySQLError and look at the code. Division by zero is not an error in a SELECT; MySQL returns NULL and a warning. A failed login raises OperationalError with code 1045.

$ uv run errors.py
IntegrityError 1062: Duplicate entry 'Prague' for key 'cities.name'
ProgrammingError 1146: Table 'testdb.towns' doesn't exist
OperationalError 1054: Unknown column 'nme' in 'field list'
ok: ((None,),)
(1045, "Access denied for user 'user7'@'172.17.0.1' (using password: YES)")

Unbuffered cursor

The default cursor reads the whole result into memory when the query is executed. For large results, PyMySQL provides an unbuffered cursor, SSCursor, which reads the rows from the server as we fetch them. (The SSDictCursor class is its dictionary variant.)

unbuffered.py
import pymysql
import pymysql.cursors

con = pymysql.connect(host='localhost', user='user7',
                      password='s$cret', database='testdb',
                      cursorclass=pymysql.cursors.SSCursor)

sql = '''WITH RECURSIVE seq(n) AS (
             SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000000)
         SELECT n, n * n FROM seq'''

with con:
    with con.cursor() as cur:
        cur.execute('SET SESSION cte_max_recursion_depth = 1000000')
        cur.execute(sql)
        print('rowcount:', cur.rowcount)

        total = 0
        for n, square in cur:
            total += square

        print('rows read:', n)
        print('sum of squares:', total)

We generate a million rows with a recursive common table expression and sum the squares while iterating over the cursor. The memory use stays low, because only one row is held at a time. The cte_max_recursion_depth variable limits the recursion to 1000 levels by default, so we raise it for the session first.

print('rowcount:', cur.rowcount)

The number of rows is not known until all of them are read; the rowcount attribute holds the largest unsigned 64-bit number in the meantime. While an unbuffered result is being read, the connection cannot run other statements. If we execute another statement before all rows are read, PyMySQL discards the rest of the result and issues a Previous unbuffered result was left incomplete warning.

$ uv run unbuffered.py
rowcount: 18446744073709551615
rows read: 1000000
sum of squares: 333333833333500000

Source

PyMySQL documentation

PyMySQL on GitHub

PEP 249 – Python Database API Specification v2.0

MySQL 9.7 Reference Manual

uv documentation

In this article we have worked with MySQL in Python using PyMySQL. We connected to the server, fetched data with different cursors, used parameterized queries, stored binary data, and worked with transactions. PostgreSQL is covered in the Python psycopg 3 and Python PostgreSQL articles and SQLite in the Python SQLite article. For ORMs, see Peewee and SQLAlchemy; running MySQL-compatible MariaDB in a container is shown in Python Docker.

Author

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all Python tutorials.