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.
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.
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.
[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).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.)
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
PEP 249 – Python Database API Specification v2.0
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
List all Python tutorials.