Ebooks

PyMySQL tutorial

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.

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.

version.py
#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17', 
    's$cret', 'mydb')

with con:
    
    cur = con.cursor()
    cur.execute("SELECT VERSION()")

    version = cur.fetchone()
    
    print("Database version: {}".format(version[0]))

In MySQL, we can use SELECT VERSION() to get the version of MySQL.

import pymysql

We import the pymysql module.

con = pymysql.connect('localhost', 'user17', 
    's$cret', 'mydb')

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:

With the with keyword, the Python interpreter automatically releases the resources. It also provides error handling.

cur = con.cursor()

From the connection object, we create a cursor. The cursor is used to traverse the records from the result set.

cur.execute("SELECT VERSION()")

We call the execute() method of the cursor and execute the SQL statement.

version = cur.fetchone()

The fetchone() method fetches the next row of a query result set, returning a single sequence, or None when no more data is available.

print("Database version: {}".format(version[0]))

We print the version of the database.

$ ./version.py 
Database version: 5.7.23-0ubuntu0.16.04.1

This is the output.

PyMySQL fetchAll

The fetchAll() method retrieves all (remaining) rows of a query result, returning them as a sequence of sequences.

retrieve_all.py
#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17', 
    's$cret', 'testdb')

with con: 

    cur = con.cursor()
    cur.execute("SELECT * FROM cities")

    rows = cur.fetchall()

    for row in rows:
        print("{0} {1} {2}".format(row[0], row[1], row[2]))

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() method 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("{0} {1} {2}".format(row[0], row[1], row[2]))

We print the data to the console, row by row.

$ ./retrieve_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

This is the output.

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.

dictionary_cursor.py
#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql
import pymysql.cursors

con = pymysql.connect(host='localhost',
        user='user17',
        password='s$cret',
        db='mydb',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM cities")

    rows = cur.fetchall()

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

In this example, we get the first rows of the cities table using the dictionary cursor.

con = pymysql.connect(host='localhost',
        user='user17',
        password='s$cret',
        db='mydb',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)

In the connect() method, 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.

column_headers.py
#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17', 
    's$cret', 'testdb')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM cities")

    rows = cur.fetchall()

    desc = cur.description

    print("{0:>3} {1:>10}".format(desc[0][0], desc[1][0]))

    for row in rows:    
        print("{0:3} {1:>10}".format(row[0], row[2]))

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("{0:>3} {1:>10}".format(desc[0][0], desc[1][0]))

Here we print and format the table column names.

    
for row in rows:    
    print("{0:3} {1:>10}".format(row[0], row[2]))

We traverse and print the data.

$ ./column_headers.py 
 id       name
  1     432000
  2    1759000
  3    1280000
  4    1748000
  5    3971000
  6    8550000
  7     464000
  8    3671000

This is the output.

PyMySQL prepared statements

When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance.

prepared_statement.py
#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17', 
    's$cret', 'testdb')

# user input
myid = 4

with con:    

    cur = con.cursor()
        
    cur.execute("SELECT * FROM cities WHERE id=%s", myid) 
    
    cid, name, population  = cur.fetchone()
    print(cid, name, population)

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.

$ ./prepared_statement.py 
4 Warsaw 1748000

This is the output.

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.

affected_rows.py
#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17',
   's$cret', 'mydb')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM cities WHERE id IN (1, 2, 3)")

    # rows = cur.fetchall()

    # for row in rows:
    #     print("{0} {1} {2}".format(row[0], row[1], row[2]))

    print("The query affected {} rows".format(cur.rowcount))

In the example, we have a SELECT statement that selects three rows.

print("The query affected {} rows".format(cur.rowcount))

We build a message that shows the number of affected rows.

$ ./affected_rows.py 
The query affected 3 rows

This is the output.

In this tutorial, we have have been programming MySQL database in Python with PyMySQL module.

You might also be interested in the following related tutorials: PyMongo tutorial, pyDAL tutorial, and Python tutorial.