OverIQ.com

  1. Home
  2. MySQL Connector/Python Tutorial
  3. Using Connector/Python C Extension

Using Connector/Python C Extension

Last updated on July 27, 2020

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 in two different 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.

_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:

    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.

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:

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:

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:

All rows deleted.

MySQL Connector/Python Tutorial

Recent Posts