5.4 Querying Data Using Connector/Python

The following example shows how to query data using a cursor created using the connection's cursor() method. The data returned is formatted and printed on the console.

The task is to select all employees hired in the year 1999 and print their names and hire dates to the console.

import datetime
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

for (first_name, last_name, hire_date) in cursor:
  print("{}, {} was hired on {:%d %b %Y}".format(
    last_name, first_name, hire_date))

cursor.close()
cnx.close()

We first open a connection to the MySQL server and store the connection object in the variable cnx. We then create a new cursor, by default a MySQLCursor object, using the connection's cursor() method.

In the preceding example, we store the SELECT statement in the variable query. Note that we are using unquoted %s-markers where dates should have been. Connector/Python converts hire_start and hire_end from Python types to a data type that MySQL understands and adds the required quotes. In this case, it replaces the first %s with '1999-01-01', and the second with '1999-12-31'.

We then execute the operation stored in the query variable using the execute() method. The data used to replace the %s-markers in the query is passed as a tuple: (hire_start, hire_end).

After executing the query, the MySQL server is ready to send the data. The result set could be zero rows, one row, or 100 million rows. Depending on the expected volume, you can use different techniques to process this result set. In this example, we use the cursor object as an iterator. The first column in the row is stored in the variable first_name, the second in last_name, and the third in hire_date.

We print the result, formatting the output using Python's built-in format() function. Note that hire_date was converted automatically by Connector/Python to a Python datetime.date object. This means that we can easily format the date in a more human-readable form.

The output should be something like this:

..
Wilharm, LiMin was hired on 16 Dec 1999
Wielonsky, Lalit was hired on 16 Dec 1999
Kamble, Dannz was hired on 18 Dec 1999
DuBourdieux, Zhongwei was hired on 19 Dec 1999
Fujisawa, Rosita was hired on 20 Dec 1999
..