Using Connector/Python C Extension

So far in this series we have only used pure Python implementation of MySQL Connector/Python. In this lesson, we will see how we can leverage the C extension provided by the Connector/Python.

The C extension was introduced in version 2.1 of Connector/Python. Since version 8.0, the C extension is enabled by default. The C extension is commonly used in production environment when dealing with large result sets.

We can enable the C extension using any one of the following two ways:

1. Call the mysql.connector.connect() function with use_pure argument set to False. But, since version 8.0, the use_pure argument defaults to False, so you don’t actually need to pass it to the connect() function. However, if you are using an older version of Connector/Python then you will need to pass use_pure=False to the connect() function, otherwise, the pure Python implementation will be used, by default. If the use_pure is set to False and C extension is not installed on the system, then the pure Python implementation will be used.
2. Another way to use the C extension is to import _mysql_connector module instead of mysql.connector. The _mysql_connector module is simply a wrapper around the original MySQL C Client library. The disadvantage of using _mysql_connector module is that the its API is different than mysql.connector, and closely resembles to MySQL C client library.

Since all the examples we have seen so far, uses pure Python implementation of Connector/Python, the rest of the lesson only discusses how to use the C extension via the _mysql_connector module.

Simple Select

Expected Output:

Here is how it works:

1. The first step in connection to the database is to initialize an instance of MySQL class. The MySQL instance is used to open and manage connection. It is also used to execute statements and read result set.

2. In line 2, we call the connect() method of the MySQL instance to connect to the database. The connect() method takes connection credentials as arguments.

3. In line 4, we use the query() method to execute the SQL statement. Note that we are not creating any cursor object before sending the query to the database. On success, the query() method returns True, otherwise an exception is raised.

4. In line 5, we call num_rows() method to fetch the number of rows in the result set. The number of rows in the result set can only be determined after fetching the rows from the database. By default, the MySQL instance doesn’t buffers(stores) the result set. What this means is that the rows are not fetched until you call a row fetching method. In other words, rows are fetched on demand. At this point, we haven’t fetch any rows, so the call to num_rows() method will return 0. We will see how to create buffered MySQL instance in the next section.

5. In line 22, we use the have_result_set attribute to check whether the query returns rows or not. If the query returns rows, then the have_result_set returns True, otherwise False. Note that this attribute only indicates whether the query can produce rows or not. It doesn’t actually count the number of rows in the result set. This means that for SELECT statements returning zero results, the have_result_set attribute would return True.

6. In line 23 and 27, we use the fetch_row() method to fetch the rows from the result. The fetch_row() method returns the next row from the reset set or None if the result set is exhausted. The row is returned as a tuple.

7. In line 28, we call the num_rows() method again. But this time we have read all the rows (i.e 5) from the result set. So, the num_rows() will return the actual row count instead of 0.

8. In line 30, we call free_result() method to free the memory associated with the result set.

9. In line 32, we close the connection to the database by calling the close() method.

10. Finally, in line 34, we use _mysql_connector.MySQLError class to catch exceptions. Unlike, the mysql.connector module which consists of various exception classes. The _mysql_connector module only provides following two exception classes:

1. MySQLError
2. MySQLInterfaceError

The MySQLError is a subclass of Exception and MySQLInterfaceError is a subclass of MySQLError. Thus, we can use MySQLError for any error returned by MySQL server. If you want to catch some specific error use the MySQL error code as usual. We will see an example of this later in this lesson.

Common Arguments of MySQL constructor

The following table lists some common arguments that we can pass to the MySQL constructor to customize its default behavior.

Let’s see some examples on how to use these arguments nows:

Example 1: Using buffered argument

Expected Output:

Since, we are using buffered MySQL instance, the rows are fetched immediately after executing the query and the first call to num_rows() returns the actual row count rather than 0.

Example 2: Using raw argument

Expected Output:

Example 3: Using charset_name and use_unicode

Expected Output:

If you don’t pass charset_name and use_unicode arguments to MySQL() constructor, then the output will look like this:

Column Information

To get the info about columns in the table we use the fetch_fields() method. It returns a list of tuples, one tuple per column..

Expected Output:

Escaping Strings and Passing Arguments

Before passing the user input to your queries you must escape it. Escaping is done with with the help of escape_string() method. It takes a string and returns the escaped string as bytes. Here is an example:

We can convert bytes type to str as follows:

Once we have escaped the input, we can safely pass it to the query.

The code in the following listing takes a keyword from the user and returns all the cities whose name contain that keyword.

Expected Output:

Executing Multiple Queries

To execute multiple statements in a single query simply pass the flag MULTI_STATEMENTS to the connect() method.

Here is an example:

Expected Output:

Here are few things to notice:

1. After reading the first result set from the query, we call the more_results() method (in line 31). The more_results() method return True, if there are more result sets, otherwise False.

2. Before we can start reading rows in the next result set we must first prepare it i.e move the result pointer to the next result set. This is done via the next_result() method (in line 33).

3. Finally, we call more_results() again, but this time it returns False, as there are no more result sets.

In the following section, we demonstrates some examples of how create tables, insert, update and delete data.

Creating Tables

Inserting Rows

Updating Rows

Expected Output:

Transactions

Expected Output:

Deleting Rows

Expected Output: