Executing Queries using Connector/Python

In the last lesson we have learned how to connect MySQL database using Connector/Python. In this lesson we will learn how to execute queries.

Creating Cursor Object

The cursor object allows us to execute queries and retrieve rows. The cursor object is an instance of MySQLCursor class.

We can create the cursor object by either by using the cursor() method of the connection object (i.e MySQLConnection) or by instantiating the MySQLCursor class directly.

Just like the connection object, when you are finished working with the cursor you have to close it by calling the close() method.

Here is an example which illustrates two way different ways of creating cursors.

Expected Output:

We are now ready to execute queries to the database.

A simple SELECT statement

There are several methods to execute the query. The most common being the execute() method.
Its syntax is as follows:

Parameter Description
query (required) SQL query or command to execute
params (optional) Sequence or mapping used to provide values for the query parameters.

Let’s look at some examples now:

The following listing select first five rows from the city table.

Example 1:

Expected Output:

After executing the query we use cursor object to iterate over the results. Notice that each row is returned in the form of tuple. To access the data in the individual column use the index. Also notice that the data in column is converted to its equivalent Python type.

Here is another example which access the data in the individual column and prints it’s type.

Example 2:

Expected Output:

Remember to read the rows before closing the cursor object, otherwise you will get an error.

Fetch Methods

Instead of iterating over the cursor object we can use the following methods to access one or more rows at time.

Method Description
fetchone() Returns the next row from the result set as tuple. If there are no more rows to retrieve, None is returned.
fetchmany([size]) Returns the specified number of rows (as a list of tuple) from the result set. If there are no more rows to retrieve, [] is returned. The default size is 1.
fetchall() Returns the all (or remaining) rows from the result set.

Here are some examples:

Example 1: Retrieving rows using fetchone().

Expected Output:

In line 5, we read the first row from the result set. Then, we use while loop to access the remaining rows. The loop stops when the fetchone() method returns None.

Example 2:

Expected Output:

In line 1, we call fetchone() to read first row from the result set. Next, we call fetchmany() to read the next 2 rows and finally we call fetchall() to fetch the remaining row. The result set is empty now, so the next call to fetchmany() returns an empty list.

Buffered and Unbuffered Cursor

By default, the cursor object is unbuffered. What does that mean?

It simply means that the rows from the database server are not fetched until you call a row fetching method or iterate over the cursor object. In other words, unbuffered cursor produces result on demand. On the other hand, when a query is executed using a buffered cursor, the rows are read immediately into the buffer and the row fetching methods return rows from the set of buffered rows.

Use buffered cursor only when:

  1. You already know that the result set is small. or,
  2. You want to use the result of one query in another query

Keep in mind that, a buffered cursor will load all the rows into the memory at once. If you have a large result set then this can take quite a bit of memory and may even cause the program to crash if you don’t have sufficient memory. For this reason, when working with lthe arge result set you should always load results on demand (i.e use unbuffered cursor).

In the case of an unbuffered cursor, trying to close the cursor object or executing a new query before reading the result set from the old one will result in InternalError: Unread result found exception.

Here are two examples which demonstrate these facts in action:

Example 1: Closing cursor object before reading the result set

Expected Output:

Example 2: Executing a new query before reading the result set from the old one.

Expected Output:

With the buffered cursors, however, you are allowed to execute a new query but the result set from the previous query will be discarded.

We will learn how to create buffered cursor later in this chapter.

Cursor Attributes

The following table list some read-only attributes that help us to get the relevant information about the last executed query.

Attribute Description
column_names Returns column names as a tuple from which the data is returned.
with_rows A boolean attribute which returns True if the query produces the result set, otherwise it returns False.
description Returns a list of tuples containing information about the columns in the result set. The tuple of the form (name, type_code, display_size, internal_ size, precision, scale, null_ok). The name refers to the column name, type_code is an integer indicating the type of the column and null_ok determines whether a column can accept NULL values or not (1 means True and 0 means False). Rest of the fields are always set to None.
lastrowid Returns the id of the last modified or inserted row, or None when no such value is available (e.g for SELECT statements). If multiple rows are inserted or updated then it returns the id of the first row only.
statement Returns the last executed query as a string.
rowcount Returns the number of rows produced or affected by the last query. For unbuffered cursors, rowcount is initially set to -1, and increments as rows are read.

Here is an example which demonstrates these attributes in action:

Expected Output:

Note that in the above output, the column type is printed as an integer, which is not very helpful.

We can get the actual type of the column using the FieldType class. The FieldType class provides all supported data types of MySQL. To convert integer type codes to its string representation, we use the get_info() method of the FieldType class, as follows:

Expected Output:

Query Parameters

We have executed only static queries so far. To create SQL queries dynamically, we need to pass user-supplied data into our queries. We do this using Query parameter.

A Query parameter is simply a placeholder for the value and will be replaced with the actual value when the query is executed. The following are two common styles used to specify query parameters.

  1. format – %s, %d
  2. pyformat – %(name)s

In format style, we use format codes like %s, %d, etc. as a placeholder. The values to these placeholders are specified using a sequence (like a list or tuple).

In pyformat style, the extended format codes, such as %(name)s is used as a placeholder and values are specified using a mapping (like a dictionary).

The following listing demonstrates each parameter style in action:

Expected Output:

Executing multiple queries with execute()

The execute() method accepts an optional keyword argument named multi. By default, it is set to False. If set to True, allows execute() to execute multiple queries separated by semicolons. When called with multi=True, the execute() method returns an iterator which can be used to access the result set produced by the queries.

Here is an example in action:

Expected Output:

The drawback of this approach is that all the query parameters must be passed in a single sequence or mapping. If you have a large number of parameters then you might lose track of them. A much better and straightforward way would be to execute each query on its own.

On occasions, you might want to execute the same query with a different set of parameters. One way to achieve this is to call execute() method in a loop, like this:

This script updates the life expectancy and population of Aruba, Afghanistan and Angola.

However, if you look at the country table after executing the script you will find that the table is not updated at all, this is because after executing any DML statement you will have to call commit() method of the connection object to commit the transaction and save the changes to the database. We will learn more about the transaction in chapter Handling Transactions with Connector/Python.

Introducing executemany()

The executemany() method is another way to execute a query with different sets of parameters. It internally calls the execute() method. Its Syntax is as follows:

Parameter Description
query Query to execute
parameter_sequence Sequence containing parameters to use within the query

The executemany() method discards the result set returned by the queries, so don’t use it to execute the SELECT statement.

The code in the preceding listing can be rewritten using executemany() as follows:

We have just removed the for header and replaced the execute() call with executemany(), rest of the code is exactly the same.

For most of the queries there is no real difference between calling executemany() or calling execute() inside a loop. But for INSERT and REPLACE statements optimizations are applied.

Consider the following INSERT statements:

The executemany() would convert the above 4 statements into a single INSERT statement, as follows:

Since the latter form will insert the four rows in a single transaction (instead of 4), it is significantly faster than the single-row INSERT statement.

The same is true for the REPLACE statement.

Here is an example which creates a new table and inserts some rows into it using the executemany() method.

Expected Output:

Customizing the Cursor Object

Cursor subclasses are used to customize the way the result set is returned by the cursor or modify the object behavior in some other way. To create objects of these class we can either call the subclass directly or pass a one-off argument to the cursor() method of the connection object.

The following is a list subclass which inherits from the MySQLCursor class.

  • MySQLCursorBuffered
  • MySQLCursorRaw
  • MySQLCursorBufferedRaw
  • MySQLCursorDict
  • MySQLCursorBufferedDict
  • MySQLCursorNamedTuple
  • MySQLCursorBufferedNamedTuple
  • MySQLCursorPrepared

Let’s discuss them in detail one by one.

MySQLCursorBuffered class

The MySQLCursorBuffered class creates a buffered cursor.

To create buffered cursor pass buffered=True to the cursor() method of the connection object. Alternatively, pass buffered=True to the connect() function, this will turn on buffering for all cursor objects created from the connection object.

Here is an example:

Expected Output:

Notice that after executing the query, the rowcount attribute returns 3, this is because we are using a buffered cursor and rows are fetched immediately after executing the query. Had we been using an unbuffered cursor, the rowcount would have returned -1.

MySQLCursorRaw class

By default, the cursor object automatically converts MySQL types to its equivalent Python types when rows are fetched. If you want to turn off this conversion use MySQLCursorRaw cursor.

To create a raw cursor, pass raw=True to the cursor() method of the connection object. Alternatively, pass raw=True to the connect() function, this will make all cursors created from the connection raw by default.

Here is an example:

Expected Output:

MySQLCursorBufferedRaw class

The MySQLCursorBufferedRaw class creates a raw buffered cursor.

To MySQLCursorBufferedRaw cursor pass raw=True and buffered=True to the cursor() method of the connection object. Alternatively, pass raw=True and buffered=True to the connect() function, this will make all the cursors created from the connection raw and buffered by default.

Here is an example:

Expected Output:

MySQLCursorDict class

The MySQLCursorDict class allows us to access rows as dictionaries instead of a tuple.

To create MySQLCursorDict cursor pass dictionary=True to the cursor() method of the connection object.

Expected Output:

MySQLCursorBufferedDict class

The MySQLCursorBufferedDict works exactly like MySQLCursorDict but returns a buffered cursor.

To create MySQLCursorBufferedDict cursor pass buffered=True and dictionary=True to the cursor() method of the connection object.

Here is an example:

Expected Output:

MySQLCursorNamedTuple class

The MySQLCursorNamedTuple class create a cursor that returns rows as namedtuple.

To create MySQLCursorNamedTuple pass named_tuple=True to the cursor() method of the connection object.

Expected Output:

MySQLCursorBufferedNamedTuple class

Similar to MySQLCursorNamedTuple but creates a buffered cursor.

To create MySQLCursorBufferedNamedTuple pass named_tuple=True and buffered=True to the cursor() method of the connection object.

Expected Output:

MySQLCursorPrepared class

The MySQLCursorPrepared class creates a cursor to execute the prepared statement.

A prepared statement a feature which allows us to reuse a SQL statement, without incurring the cost of compiling it multiple times. They are useful when you want to repeatedly execute a query with a different set of parameters.

Unlike, a non-prepared statement where you can use format (%s) or pyformat (%(name)s) parameter styles. With prepared statements, you can only use format(%d) or qmark (?) parameter styles.

To create MySQLCursorPrepared cursor pass prepared=True to the cursor() method of the connection object.

Here is an example:

Expected Output:

In the next lesson we will learn how to handle errors:

Leave a Comment