PyMySQL
last modified January 29, 2024
PyMySQL tutorial shows how to program MySQL in Python with PyMySQL module.
PyMySQL
PyMySQL is a pure-Python MySQL client library, based on PEP 249. Most public APIs are compatible with mysqlclient and MySQLdb. PyMySQL works with MySQL 5.5+ and MariaDB 5.5+.
MySQL is a leading open source database management system. It is a multiuser, multithreaded database management system. MySQL is especially popular on the web.
USE testdb;
DROP TABLE IF EXISTS cities;
CREATE TABLE cities(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), population INT);
INSERT INTO cities(name, population) VALUES('Bratislava', 432000);
INSERT INTO cities(name, population) VALUES('Budapest', 1759000);
INSERT INTO cities(name, population) VALUES('Prague', 1280000);
INSERT INTO cities(name, population) VALUES('Warsaw', 1748000);
INSERT INTO cities(name, population) VALUES('Los Angeles', 3971000);
INSERT INTO cities(name, population) VALUES('New York', 8550000);
INSERT INTO cities(name, population) VALUES('Edinburgh', 464000);
INSERT INTO cities(name, population) VALUES('Berlin', 3671000);
In the tutorial, we use the cities table.
PyMySQL installation
$ sudo pip3 install pymysql
We use the pip3 tool to install PyMySQL.
PyMySQL version example
In the following example, we get the version of MySQL.
#!/usr/bin/python
import pymysql
con = pymysql.connect('localhost', 'user7',
's$cret', 'testdb')
try:
with con.cursor() as cur:
cur.execute('SELECT VERSION()')
version = cur.fetchone()
print(f'Database version: {version[0]}')
finally:
con.close()
In MySQL, we can use SELECT VERSION to get the version of MySQL.
import pymysql
We import the pymysql module.
con = pymysql.connect('localhost', 'user7',
's$cret', 'testdb')
We connect to the database with connect. We pass four parameters:
the hostname, the MySQL user name, the password, and the database name.
with con.cursor() as cur:
Using the with keyword, the Python interpreter automatically
releases the resources. It also provides error handling. We get a cursor
object, which is used to traverse records from the result set.
cur.execute('SELECT VERSION()')
We call the execute function of the cursor and execute the SQL
statement.
version = cur.fetchone()
The fetchone function fetches the next row of a query
result set, returning a single sequence, or None when no
more data is available.
print(f'Database version: {version[0]}')
We print the version of the database.
finally:
con.close()
The pymysql module does not implement the automatic handling of the
connection resource; we need to explicitly close the connection with
close in the finally clause.
$ ./version.py Database version: 10.3.23-MariaDB-1
PyMySQL fetchAll
The fetchAll method retrieves all (remaining) rows of a query
result, returning them as a sequence of sequences.
#!/usr/bin/python
import pymysql
con = pymysql.connect('localhost', 'user7',
's$cret', 'testdb')
try:
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]}')
finally:
con.close()
In the example, we retrieve all cities from the database table.
cur.execute('SELECT * FROM cities')
This SQL statement selects all data from the cities table.
rows = cur.fetchall()
The fetchall function gets all records. It returns a result set.
Technically, it is a tuple of tuples. Each of the inner tuples represent a row
in the table.
for row in rows:
print(f'{row[0]} {row[1]} {row[2]}')
We print the data to the console, row by row.
$ ./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
PyMySQL dictionary cursor
The default cursor returns the data in a tuple of tuples. When we use a dictionary cursor, the data is sent in a form of Python dictionaries. This way we can refer to the data by their column names.
#!/usr/bin/python
import pymysql
import pymysql.cursors
con = pymysql.connect(host='localhost',
user='user7',
password='s$cret',
db='testdb',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with con.cursor() as cur:
cur.execute('SELECT * FROM cities')
rows = cur.fetchall()
for row in rows:
print(row['id'], row['name'])
finally:
con.close()
In this example, we get the first rows of the cities table using the dictionary cursor.
con = pymysql.connect(host='localhost',
user='user7',
password='s$cret',
db='testdb',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
In the connect function, we pass the
pymysql.cursors.DictCursor value to the cursorclass
parameter.
for row in rows:
print(row['id'], row['name'])
We refer to the data by column names of the cities table.
PyMySQL column headers
Next we will show how to print column headers with the data from the database table.
#!/usr/bin/python
import pymysql
con = pymysql.connect('localhost', 'user7',
's$cret', 'testdb')
try:
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}')
finally:
con.close()
The column names are considered to be the metadata. They are obtained from the cursor object.
desc = cur.description
The description attribute of the cursor returns information about
each of the result columns of a query.
print(f'{desc[0][0]:<8} {desc[1][0]:<15} {desc[2][0]:>10}')
Here we print and format the table column names.
for row in rows:
print(f'{row[0]:<8} {row[1]:<15} {row[2]:>10}')
We traverse and print the data.
$ ./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
PyMySQL escaping parameters
The parameters passed to the execute method are escaped for
security reasons; this is to prevent SQL injection attacks.
#!/usr/bin/python
import pymysql
con = pymysql.connect('localhost', 'user7',
's$cret', 'testdb')
# user input
myid = 4
try:
with con.cursor() as cur:
cur.execute('SELECT * FROM cities WHERE id=%s', myid)
cid, name, population = cur.fetchone()
print(cid, name, population)
finally:
con.close()
In the example, we get the row with the specified Id.
cur.execute('SELECT * FROM cities WHERE id=%s', myid)
We use a placeholder identified by the %s marker.
Before the SQL statement is executed, the values are bound to their
placeholders.
$ ./escaped.py 4 Warsaw 1748000
PyMySQL affected rows
The rowcount is a read-only cursor attribute which specifies the
number of rows that was produced by the the last SELECT, UPDATE, or
INSERT statement.
#!/usr/bin/python
import pymysql
con = pymysql.connect('localhost', 'user7',
's$cret', 'testdb')
try:
with con.cursor() as cur:
cur.execute('SELECT * FROM cities WHERE id IN (1, 2, 3)')
print(f'The query affected {cur.rowcount} rows')
finally:
con.close()
In the example, we have a SELECT statement that selects three rows.
print(f'The query affected {cur.rowcount} rows')
We build a message that shows the number of affected rows.
$ ./affected_rows.py The query affected 3 rows
PyMySQL insert row
A new row is inserted with the INSERT INTO SQL statement.
#!/usr/bin/python
import pymysql
con = pymysql.connect('localhost', 'user7',
's$cret', 'testdb')
city = (9, 'Kiev', 2887000)
try:
with con.cursor() as cur:
cur.execute('INSERT INTO cities VALUES(%s, %s, %s)',
(city[0], city[1], city[2]))
con.commit()
print('new city inserted')
finally:
con.close()
In the example, we insert a new city into the table.
cur.execute('INSERT INTO cities VALUES(%s, %s, %s)',
(city[0], city[1], city[2]))
con.commit()
In pymysql, the autocommit is off by default. We need to call
commit to execute the changes.
Source
In this article we have have been programming MySQL database in Python with PyMySQL module.
Author
List all Python tutorials.