Contents
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:
- Call the
mysql.connector.connect()
function withuse_pure
argument set toFalse
. But, since version 8.0, theuse_pure
argument defaults toFalse
, so you don’t actually need to pass it to theconnect()
function. However, if you are using an older version of Connector/Python then you will need to passuse_pure=False
to theconnect()
function, otherwise, the pure Python implementation will be used, by default. If theuse_pure
is set toFalse
and C extension is not installed on the system, then the pure Python implementation will be used. - Another way to use the C extension is to import
_mysql_connector
module instead ofmysql.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 thanmysql.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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
import _mysql_connector from mysql.connector.constants import ClientFlag conn_args = dict( user='root', password='pass', host='127.0.0.1', database='world' ) try: db = _mysql_connector.MySQL() db.connect(**conn_args) db.query('select * from city limit 5') print("Rows:", db.num_rows()) if db.have_result_set: row = db.fetch_row() while row: print(row) row = db.fetch_row() print("Rows:", db.num_rows()) db.free_result() db.close() except _mysql_connector.MySQLInterfaceError as e: print(e) |
Expected Output:
1 2 3 4 5 6 7 |
Rows: 0 (1, b'Kabul', b'AFG', b'Kabol', 1780000) (2, b'Qandahar', b'AFG', b'Qandahar', 237500) (3, b'Herat', b'AFG', b'Herat', 186800) (4, b'Mazar-e-Sharif', b'AFG', b'Balkh', 127800) (5, b'Amsterdam', b'NLD', b'Noord-Holland', 731200) Rows: 5 |
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:
MySQLError
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.
Argument | Description |
---|---|
buffered |
If set to True , the rows are immediately fetched after executing the query, otherwise the rows are fetched on demand. It defaults to False . |
raw |
If set to True , conversion between MySQL types to its equivalent Python type, will not take place and the results will be returned as it is. It defaults to True . |
charset_name |
Character set to use when sending the data to and from the database server. |
use_unicode |
If set to True , CHAR , VARCHAR and TEXT are returned as unicode strings, using the configured character set (via the charset_name argument) |
Let’s see some examples on how to use these arguments nows:
Example 1: Using buffered
argument
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import _mysql_connector conn_args = dict( user='root', password='pass', host='127.0.0.1', database='world' ) try: db = _mysql_connector.MySQL(buffered=True) db.connect(**conn_args) db.query('select * from city limit 5') print("Rows:", db.num_rows()) if db.have_result_set: row = db.fetch_row() while row: print(row) row = db.fetch_row() print("Rows:", db.num_rows()) db.free_result() db.close() except _mysql_connector.MySQLError as e: print(e) |
Expected Output:
1 2 3 4 5 6 7 |
Rows: 5 (1, b'Kabul', b'AFG', b'Kabol', 1780000) (2, b'Qandahar', b'AFG', b'Qandahar', 237500) (3, b'Herat', b'AFG', b'Herat', 186800) (4, b'Mazar-e-Sharif', b'AFG', b'Balkh', 127800) (5, b'Amsterdam', b'NLD', b'Noord-Holland', 731200) Rows: 5 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import _mysql_connector conn_args = dict( user='root', password='pass', host='127.0.0.1', database='world' ) try: db = _mysql_connector.MySQL(buffered=True, raw=True,) db.connect(**conn_args) db.query('select * from city limit 5') print("Rows:", db.num_rows()) if db.have_result_set: row = db.fetch_row() while row: print(row) row = db.fetch_row() print("Rows:", db.num_rows()) db.free_result() db.close() except _mysql_connector.MySQLError as e: print(e) |
Expected Output:
1 2 3 4 5 6 7 |
Rows: 5 (bytearray(b'1'), bytearray(b'Kabul'), bytearray(b'AFG'), bytearray(b'Kabol'), bytearray(b'1780000')) (bytearray(b'2'), bytearray(b'Qandahar'), bytearray(b'AFG'), bytearray(b'Qandahar'), bytearray(b'237500')) (bytearray(b'3'), bytearray(b'Herat'), bytearray(b'AFG'), bytearray(b'Herat'), bytearray(b'186800')) (bytearray(b'4'), bytearray(b'Mazar-e-Sharif'), bytearray(b'AFG'), bytearray(b'Balkh'), bytearray(b'127800')) (bytearray(b'5'), bytearray(b'Amsterdam'), bytearray(b'NLD'), bytearray(b'Noord-Holland'), bytearray(b'731200')) Rows: 5 |
Example 3: Using charset_name
and use_unicode
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import _mysql_connector conn_args = dict( user='root', password='pass', host='127.0.0.1', database='world' ) try: db = _mysql_connector.MySQL(buffered=True, charset_name='utf8', use_unicode=True) db.connect(**conn_args) db.query('select * from city order by name desc limit 5') print("Rows:", db.num_rows()) if db.have_result_set: row = db.fetch_row() while row: print(row) row = db.fetch_row() print("Rows:", db.num_rows()) db.free_result() db.close() except _mysql_connector.MySQLError as e: print(e) |
Expected Output:
1 2 3 4 5 6 7 |
Rows: 5 (548, 'Šumen', 'BGR', 'Varna', 94686) (3736, 'Štšolkovo', 'RUS', 'Moskova', 104900) (3479, 'Šostka', 'UKR', 'Sumy', 90000) (2450, 'Šiauliai', 'LTU', 'Šiauliai', 146563) (3665, 'Šahty', 'RUS', 'Rostov-na-Donu', 221800) Rows: 5 |
If you don’t pass charset_name
and use_unicode
arguments to MySQL()
constructor, then the output will look like this:
1 2 3 4 5 6 7 |
Rows: 5 (548, b'\x8aumen', b'BGR', b'Varna', 94686) (3736, b'\x8at\x9aolkovo', b'RUS', b'Moskova', 104900) (3479, b'\x8aostka', b'UKR', b'Sumy', 90000) (2450, b'\x8aiauliai', b'LTU', b'\x8aiauliai', 146563) (3665, b'\x8aahty', b'RUS', b'Rostov-na-Donu', 221800) Rows: 5 |
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..
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
import _mysql_connector from mysql.connector.constants import FieldType conn_args = dict( user='root', password='pass', host='127.0.0.1', database='world' ) try: db = _mysql_connector.MySQL(buffered=True) db.connect(**conn_args) db.query('select * from city limit 5') fields_info = db.fetch_fields() print("{:15} {:10} {:15}".format("Column Name", "Type", "NULL_OK", )) for info in fields_info: # print(info) print("{:15} {:<10} {:<15}".format( info[4].decode('utf-8'), FieldType.get_info(info[-3]), info[-1])) db.free_result() db.close() except _mysql_connector.MySQLInterfaceError as e: print(e) |
Expected Output:
1 2 3 4 5 6 |
Column Name Type NULL_OK ID LONG 0 Name STRING 0 CountryCode STRING 0 District STRING 0 Population LONG 0 |
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:
1 2 3 4 5 6 7 8 |
>>> >>> db.escape_string("delete from category;") b'delete from category;' >>> >>> db.escape_string("'Another string'") b"\\'Another string\\'" >>> >>> |
We can convert bytes
type to str
as follows:
1 2 3 4 5 6 7 8 9 |
>>> >>> db.escape_string("'").decode("utf8") "\\'" >>> >>> >>> db.escape_string("'Another string'").decode("utf8") "\\'Another string\\'" >>> >>> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
import _mysql_connector conn_args = dict( user='root', password='pass', host='127.0.0.1', database='world' ) try: db = _mysql_connector.MySQL(buffered=True, charset_name='utf8', use_unicode=True) db.connect(**conn_args) city = input("Enter keyword: ") cleaned_city = db.escape_string(city).decode('utf-8') # escape the string entered db.query('select * from city where name ' 'like "%{}%" limit 5'.format(cleaned_city)) if db.num_rows(): print("\nList of cities containing '{}' in their " "name.\n".format(cleaned_city)) # print("Rows:", db.num_rows()) print("{:<5} {:<20} {:10} {:15} {:10}".format ("ID", "City", "Country", "District", "Population")) if db.have_result_set: row = db.fetch_row() while row: print("{:<5} {:<20} {:10} {:15} {:10}".format(row[0], row[1], row[2], row[3], row[4])) row = db.fetch_row() print("\nRows:", db.num_rows()) else: print("Nothing Found :(") db.free_result() db.close() except _mysql_connector.MySQLInterfaceError as e: print(e) |
Expected Output:
1 2 3 4 5 6 7 8 9 10 11 12 |
Enter keyword: de List of cities containing 'de' in their name. ID City Country District Population 15 Enschede NLD Overijssel 149544 24 Leiden NLD Zuid-Holland 117196 29 Ede NLD Gelderland 101574 30 Delft NLD Zuid-Holland 95268 73 Lomas de Zamora ARG Buenos Aires 622013 Rows: 5 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
import _mysql_connector from mysql.connector.constants import ClientFlag conn_args = dict( user='root', password='pass', host='127.0.0.1', database='world' ) db = _mysql_connector.MySQL(buffered=True, charset_name='utf8', use_unicode=True) db.connect(**conn_args, client_flags=ClientFlag.MULTI_STATEMENTS) db.query('select * from city ORDER BY name desc limit 5;' ' select * from city ORDER BY id desc limit 6') print("Rows:", db.num_rows()) # row count in the first result set if db.have_result_set: # loop over the first result set row = db.fetch_row() while row: print(row) row = db.fetch_row() print() print("Does another result set exists? ", db.more_results()) print("Prepare the next result set: ", db.next_result()) print() print("Rows:", db.num_rows()) # row count in the second result set if db.have_result_set: # loop over the second result set row = db.fetch_row() while row: print(row) row = db.fetch_row() print("\nDoes another result set exists? ", db.more_results()) db.free_result() db.close() |
Expected Output:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Rows: 5 (548, 'Šumen', 'BGR', 'Varna', 94686) (3736, 'Štšolkovo', 'RUS', 'Moskova', 104900) (3479, 'Šostka', 'UKR', 'Sumy', 90000) (2450, 'Šiauliai', 'LTU', 'Šiauliai', 146563) (3665, 'Šahty', 'RUS', 'Rostov-na-Donu', 221800) Does another result set exists? True Prepare the next result set: True Rows: 6 (4081, 'Kabul', 'AFG', 'Kabol', 1780000) (4079, 'Rafah', 'PSE', 'Rafah', 92020) (4078, 'Nablus', 'PSE', 'Nablus', 100231) (4077, 'Jabaliya', 'PSE', 'North Gaza', 113901) (4076, 'Hebron', 'PSE', 'Hebron', 119401) (4075, 'Khan Yunis', 'PSE', 'Khan Yunis', 123175) |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
import _mysql_connector from mysql.connector import errorcode from collections import OrderedDict conn_args = dict( user='root', password='pass', host='127.0.0.1', database='world' ) db_name = 'blog' tables = OrderedDict() tables['category'] = ''' create table category ( id int not null auto_increment primary key, name varchar(50) not null ) ''' tables['post'] = ''' create table post ( id int not null auto_increment PRIMARY KEY, title varchar(200) not null, content text not null, date TIMESTAMP not null, category_id int not null, FOREIGN key (category_id) REFERENCES category(id) ) ''' def create_db(conn): try: conn.query("create database {}".format(db_name)) print("Database created.") except _mysql_connector.MySQLInterfaceError as err: print("Database creation failed:", err) exit(1) try: db = _mysql_connector.MySQL(buffered=True) db.connect(**conn_args) try: db.select_db(db_name) print('Database {} already exist.'.format(db_name)) except _mysql_connector.MySQLInterfaceError as err: # database doesn't exist, create one if errorcode.ER_BAD_DB_ERROR == err.errno: create_db(db) db.select_db(db_name) for k, v in tables.items(): try: db.query(v) print('Table {} created.'.format(k)) except _mysql_connector.MySQLInterfaceError as err: if errorcode.ER_TABLE_EXISTS_ERROR == err.errno: print('Table {} already exists.'.format(k)) db.close() except _mysql_connector.MySQLInterfaceError as e: print(e) |
Inserting Rows
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import _mysql_connector conn_args = dict( user='root', password='pass', host='127.0.0.1', database='blog' ) try: db = _mysql_connector.MySQL() db.connect(**conn_args) category_list = [ ('python',), ('css', ), ('java',), ('c',), ('php',), ] stmt = "INSERT INTO category(name) values('{}')" for category in category_list: db.query(stmt.format(category[0])) print("Row ID:", db.insert_id()) # return last inserted id db.close() except _mysql_connector.MySQLInterfaceError as e: print(e) |
Updating Rows
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
import _mysql_connector conn_args = dict( user='root', password='pass', host='127.0.0.1', database='blog' ) try: db = _mysql_connector.MySQL() db.connect(**conn_args) stmt = "UPDATE category set name = upper(name)" db.query(stmt) print("Affects rows:", db.affected_rows()) # return affected rows db.close() except _mysql_connector.MySQLInterfaceError as e: print(e) |
Expected Output:
1 |
Affects rows: 5 |
Transactions
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import _mysql_connector conn_args = dict( user='root', password='pass', host='127.0.0.1', database='blog' ) try: db = _mysql_connector.MySQL() db.connect(**conn_args) stmt1 = "INSERT INTO category(name) values('shell scripting')" stmt2 = "INSERT INTO post(title, content, category_id) values('{}', '{}', '{}')" db.query('START TRANSACTION') # start transaction db.query(stmt1) category_id = db.insert_id() db.query(stmt2.format('Title', 'Content', category_id)) db.commit() # commit transaction print("Transaction committed.") db.close() except _mysql_connector.MySQLInterfaceError as e: print(e) |
Expected Output:
1 |
Transaction committed. |
Deleting Rows
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
import _mysql_connector conn_args = dict( user='root', password='pass', host='127.0.0.1', database='blog' ) try: db = _mysql_connector.MySQL() db.connect(**conn_args) db.query('DELETE FROM post') db.query('DELETE FROM category') print('All rows deleted.') db.close() except _mysql_connector.MySQLInterfaceError as e: print(e) |
Expected Output:
1 |
All rows deleted. |