OverIQ.com

  1. Home
  2. MySQL Connector/Python Tutorial
  3. Executing Queries using Connector/Python

Executing Queries using Connector/Python

Last updated on July 27, 2020

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 call 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor1 = db.cursor()  # 1st method
cursor2 = MySQLCursor(db)   # 2nd method

print(db)

print("cursor1: ", cursor1)
print("cursor2: ", cursor2)

cursor1.close()
cursor2.close()

db.close()

Expected Output:

1
2
3
<mysql.connector.connection.MySQLConnection object at 0x7fcf99799da0>
cursor1:  MySQLCursor: (Nothing executed yet)
cursor2:  MySQLCursor: (Nothing executed yet)

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:

`execute(query, params=None)`
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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("select * from city limit 5")

# iterate over result
for row in cursor:
    print(row)

cursor.close()
db.close()

Expected Output:

1
2
3
4
5
(1, 'Kabul', 'AFG', 'Kabol', 1780000)
(2, 'Qandahar', 'AFG', 'Qandahar', 237500)
(3, 'Herat', 'AFG', 'Herat', 186800)
(4, 'Mazar-e-Sharif', 'AFG', 'Balkh', 127800)
(5, 'Amsterdam', 'NLD', 'Noord-Holland', 731200)

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()


cursor.execute("select * from city limit 5")

for row in cursor:
    print(row[0], type(row[0]), row[1], type(row[1]) )

cursor.close()
db.close()

Expected Output:

1
2
3
4
5
1 <class 'int'> Kabul <class 'str'>
2 <class 'int'> Qandahar <class 'str'>
3 <class 'int'> Herat <class 'str'>
4 <class 'int'> Mazar-e-Sharif <class 'str'>
5 <class 'int'> Amsterdam <class 'str'>

Remember to read the rows before consuming the res

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().

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

r = cursor.execute("select * from city limit 5")

# first row
row = cursor.fetchone()

# loop over the remaining result set
while row:
    print(row)
    row = cursor.fetchone()

cursor.close()
db.close()

Expected Output:

1
2
3
4
5
(1, 'Kabul', 'AFG', 'Kabol', 1780000)
(2, 'Qandahar', 'AFG', 'Qandahar', 237500)
(3, 'Herat', 'AFG', 'Herat', 186800)
(4, 'Mazar-e-Sharif', 'AFG', 'Balkh', 127800)
(5, 'Amsterdam', 'NLD', 'Noord-Holland', 731200)

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: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

r = cursor.execute("select * from city limit 5")

print(cursor.fetchone())  # fetch the first row
print(cursor.fetchmany(2))  # fetch the next 2 rows
print(cursor.fetchall())  # fetch all the remaining rows
print(cursor.fetchmany())  # the result set is now empty

cursor.close()
db.close()

Expected Output:

1
2
3
4
(1, 'Kabul', 'AFG', 'Kabol', 1780000)
[(2, 'Qandahar', 'AFG', 'Qandahar', 237500), (3, 'Herat', 'AFG', 'Herat', 186800)]
[(4, 'Mazar-e-Sharif', 'AFG', 'Balkh', 127800), (5, 'Amsterdam', 'NLD', 'Noord-Holland', 731200)]
[]

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 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("select * from city limit 5")

cursor.close() # closing cursor before reading the result set

db.close()

Expected Output:

1
2
3
4
Traceback (most recent call last):
...
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("select * from city limit 5")

# result set from the above query is not read

# now we are executing a new query

cursor.execute("""select Code, Name, Region, Population, Continent from 
country where Continent='North America' limit 5""")

for row in cursor:
    print(row)

cursor.close()
db.close()

Expected Output:

1
2
3
4
Traceback (most recent call last):
...
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

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:

 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
import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("""select Code, Name, Region, Population, Continent from
country where Continent='North America' limit 5""")

print("rowcount (initial):", cursor.rowcount, end='\n\n')

# reading rows but not displaying
for row in cursor: pass

print("column_names:", cursor.column_names, end='\n\n')
print("with_rows:", cursor.with_rows, end='\n\n')

print("description: ", end="")
pprint(cursor.description)

print("\nstatement:", cursor.statement, end='\n\n')

print("lastrowid:", cursor.lastrowid, end='\n\n')

print("rowcount (final):", cursor.rowcount, end='\n\n')

cursor.close()
db.close()

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
rowcount (initial): -1

column_names: ('Code', 'Name', 'Region', 'Population', 'Continent')

with_rows: True

description: [('Code', 254, None, None, None, None, 0, 16387),
 ('Name', 254, None, None, None, None, 0, 1),
 ('Region', 254, None, None, None, None, 0, 1),
 ('Population', 3, None, None, None, None, 0, 1),
 ('Continent', 254, None, None, None, None, 0, 257)]

statement: select Code, Name, Region, Population, Continent from
country where Continent='North America' limit 5

lastrowid: None

rowcount (final): 5

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:

 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
from mysql.connector import FieldType
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("""select Code, Name, Region, Population, Continent from
country where Continent='North America' limit 5""")


cursor.fetchall() # read the result without printing anything

print("description: ", end="")
pprint(cursor.description)

print()

for desc in cursor.description:
    print("Column '{}' is of type {}".format( desc[0], FieldType.get_info(desc[1])))

cursor.close()
db.close()

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
description: [('Code', 254, None, None, None, None, 0, 16387),
 ('Name', 254, None, None, None, None, 0, 1),
 ('Region', 254, None, None, None, None, 0, 1),
 ('Population', 3, None, None, None, None, 0, 1),
 ('Continent', 254, None, None, None, None, 0, 257)]

Column 'Code' is of type STRING
Column 'Name' is of type STRING
Column 'Region' is of type STRING
Column 'Population' is of type LONG
Column 'Continent' is of type STRING

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:

 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
import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

# query with format parameter style
sql1 = """select Code, Name, Region, Population, Continent from
country where Continent=%s limit %s"""

data1 = ('Europe', 5)

cursor.execute(sql1, data1)

for row in cursor:
    print(row)


print('-' * 80)


# query with pyformat parameter style
sql2 = """select Code, Name, Region, Population, Continent from
country where Continent=%(continent)s limit %(limit)s"""

data2 = {
        'continent': 'Asia',
        'limit': 4
}

cursor.execute(sql2, data2)

for row in cursor:
    print(row)

cursor.close()
db.close()

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
('ALB', 'Albania', 'Southern Europe', 3401200, 'Europe')
('AND', 'Andorra', 'Southern Europe', 78000, 'Europe')
('AUT', 'Austria', 'Western Europe', 8091800, 'Europe')
('BEL', 'Belgium', 'Western Europe', 10239000, 'Europe')
('BGR', 'Bulgaria', 'Eastern Europe', 8190900, 'Europe')
--------------------------------------------------------------------------------
('AFG', 'Afghanistan', 'Southern and Central Asia', 22720000, 'Asia')
('ARE', 'United Arab Emirates', 'Middle East', 2441000, 'Asia')
('ARM', 'Armenia', 'Middle East', 3520000, 'Asia')
('AZE', 'Azerbaijan', 'Middle East', 7734000, 'Asia')

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:

 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
import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

# query with format parameter style

sql1 = "select version()"

sql2 = """select Code, Name, Region, Population, Continent from
country where Continent=%s limit %s"""

sql3 = "select 1+1"

queries = [sql1, sql2, sql3]

data = ('South America', 5)

# returns an iterator
results = cursor.execute(";".join(queries), data, multi=True)

count = 1

for result in results:

    # result is a cursor object i.e result == cursor
    # so we have access to all cursor attributes and methods 

    print("Query {0} - {1} :".format(count, result.statement))

    # does query has result?
    if result.with_rows:
        for row in result:
            print(row)
        count = count + 1
    else:
        print("No result found")

    print()

cursor.close()
db.close()

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
Query 1 - select version():
('5.7.22-0ubuntu0.16.04.1',)

Query 2 - select Code, Name, Region, Population, Continent from
country where Continent='South America' limit 5:
('ARG', 'Argentina', 'South America', 37032000, 'South America')
('BOL', 'Bolivia', 'South America', 8329000, 'South America')
('BRA', 'Brazil', 'South America', 170115000, 'South America')
('CHL', 'Chile', 'South America', 15211000, 'South America')
('COL', 'Colombia', 'South America', 42321000, 'South America')

Query 3 - select 1+1:
(2,)

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:

 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
import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """update country
set LifeExpectancy = %(life_expectency)s, 
Population = %(population)s
where Name = %(name)s"""

data_list = [
    {
        'life_expectency': 78.9,
        'population': 103500,
        'name': 'Aruba'
    },
    {
        'life_expectency': 46.9,
        'population': 22720400,
        'name': 'Afghanistan'
    },
    {
        'life_expectency': 38.3,
        'population': 12879000,
        'name': 'Angola'
    },
]


for data in data_list:
    cursor.execute(sql, data)

# db.commit()  # call commit() method to save the changes

cursor.close()
db.close()

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 Transactions.

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:

executemany(query, parameter_seqeunce)
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:

 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
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """update country
set LifeExpectancy = %(life_expectency)s,
Population = %(population)s
where Name = %(name)s"""


data_list = [
    {
        'life_expectency': 78.9,
        'population': 103500,
        'name': 'Aruba'
    },
    {
        'life_expectency': 46.9,
        'population': 22720400,
        'name': 'Afghanistan'
    },
    {
        'life_expectency': 38.3,
        'population': 12879099,
        'name': 'Angola'
    },
]

cursor.executemany(sql, data_list)

# db.commit()

cursor.close()
db.close()

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
insert into customer(name, age)
values('John', 45);

insert into customer(name, age)
values('Max', 25);

insert into customer(name, age)
values('Jane', 20);

insert into customer(name, age)
values('Bob', 34);

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

1
2
3
4
5
insert into customer(name, age)
values('John', 45),
('Max', 25),
('Jane', 20),
('Bob', 34);

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.

 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

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql1 = """
create temporary table customer(
  id int AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age SMALLINT NOT NULL     
)
"""

cursor.execute(sql1)

sql2 = """
insert into customer(name, age)
VALUES (%(name)s, %(age)s)
"""

data_list = [
    {
        'name': 'John',
        'age': 45
    },
    {
        'name': 'Max',
        'age': 25
    },
    {
        'name': 'Jane',
        'age': 20
    },
    {
        'name': 'Bob',
        'age': 34
    },
]

cursor.executemany(sql2, data_list)

print("Query:", cursor.statement)
print("Rowcount:", cursor.rowcount)  # rows inserted

db.commit()   # commit the changes

cursor.close()
db.close()

Expected Output:

1
2
3
Query: insert into customer(name, age)
VALUES ('John', 45),('Max', 25),('Jane', 20),('Bob', 34)
Rowcount: 4

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:

 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

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# turn on buffering for all cursor objects created from
# this connection
#
# db = mysql.connector.connect(
#     option_files='my.conf',
#     use_pure=True,
#     buffered=True
# )

# only this cursor will buffer results
cursor = db.cursor(buffered=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

Expected Output:

1
2
3
4
5
rowcount (initial): 3
('Australia', 'Oceania', 18886000, 79.8)
('Switzerland', 'Europe', 7160400, 79.6)
('Sweden', 'Europe', 8861400, 79.6)
rowcount (final): 3

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:

 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

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# all cursor objects created from
# this connection will be raw
#
# db = mysql.connector.connect(
#     option_files='my.conf',
#     use_pure=True,
#     raw=True
# )

# only this cursor is raw
cursor = db.cursor(raw=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

Expected Output:

1
2
3
4
5
rowcount (initial): -1
(bytearray(b'Australia'), bytearray(b'Oceania'), bytearray(b'18886000'), bytearray(b'79.8'))
(bytearray(b'Switzerland'), bytearray(b'Europe'), bytearray(b'7160400'), bytearray(b'79.6'))
(bytearray(b'Sweden'), bytearray(b'Europe'), bytearray(b'8861400'), bytearray(b'79.6'))
rowcount (final): 3

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:

 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
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# all cursor objects created from
# this connection will be raw and buffered
#
# db = mysql.connector.connect(
#     option_files='my.conf',
#     use_pure=True,
#     raw=True
#     buffered=True
# )

# only this cursor will be raw and buffered
cursor = db.cursor(raw=True, buffered=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

Expected Output:

1
2
3
4
5
rowcount (initial): 3
(bytearray(b'Australia'), bytearray(b'Oceania'), bytearray(b'18886000'), bytearray(b'79.8'))
(bytearray(b'Switzerland'), bytearray(b'Europe'), bytearray(b'7160400'), bytearray(b'79.6'))
(bytearray(b'Sweden'), bytearray(b'Europe'), bytearray(b'8861400'), bytearray(b'79.6'))
rowcount (final): 3

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.

 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
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# return rows as dictionary instead of tuple
cursor = db.cursor(dictionary=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)
    print(row['Name'], row['Continent'])   # access columns by names

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

Expected Output:

1
2
3
4
5
6
7
8
rowcount (initial): -1
{'Continent': 'Oceania', 'LifeExpectancy': 79.8, 'Population': 18886000, 'Name': 'Australia'}
Australia Oceania
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Population': 7160400, 'Name': 'Switzerland'}
Switzerland Europe
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Population': 8861400, 'Name': 'Sweden'}
Sweden Europe
rowcount (final): 3

MySQLCursorBufferedDict #

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:

 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
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# return a buffered MySQLCursorDict
cursor = db.cursor(dictionary=True, buffered=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)
    print(row['Name'], row['Continent'])

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

Expected Output:

1
2
3
4
5
6
7
8
rowcount (initial): 3
{'Continent': 'Oceania', 'LifeExpectancy': 79.8, 'Name': 'Australia', 'Population': 18886000}
Australia Oceania
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Name': 'Switzerland', 'Population': 7160400}
Switzerland Europe
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Name': 'Sweden', 'Population': 8861400}
Sweden Europe
rowcount (final): 3

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.

 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
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# return a unbuffered MySQLCursorNamedTuple
cursor = db.cursor(named_tuple=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)
    print(row.Name, row.Continent)  # access columns as attributes

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

Expected Output:

1
2
3
4
5
6
7
8
rowcount (initial): -1
Row(Name='Australia', Continent='Oceania', Population=18886000, LifeExpectancy=79.8)
Australia Oceania
Row(Name='Switzerland', Continent='Europe', Population=7160400, LifeExpectancy=79.6)
Switzerland Europe
Row(Name='Sweden', Continent='Europe', Population=8861400, LifeExpectancy=79.6)
Sweden Europe
rowcount (final): 3

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.

 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
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# return a buffered MySQLCursorNamedTuple
cursor = db.cursor(named_tuple=True, buffered=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)
    print(row.Name, row.Continent)  # access columns as attributes

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

Expected Output:

1
2
3
4
5
6
7
8
rowcount (initial): 3
Row(Name='Australia', Continent='Oceania', Population=18886000, LifeExpectancy=79.8)
Australia Oceania
Row(Name='Switzerland', Continent='Europe', Population=7160400, LifeExpectancy=79.6)
Switzerland Europe
Row(Name='Sweden', Continent='Europe', Population=8861400, LifeExpectancy=79.6)
Sweden Europe
rowcount (final): 3

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:

 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
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# return a buffered MySQLCursorNamedTuple
cursor = db.cursor(prepared=True)

sql1 = """
create temporary table customer(
  id int AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age SMALLINT NOT NULL     
)
"""

cursor.execute(sql1)

sql2 = """
insert into customer(name, age)
VALUES (%s, %s)
"""

data_list = [
    ('John', 45),
    ('Max', 25),
    ('Jane', 20),
    ('Bob', 34),
]

cursor.executemany(sql2, data_list)

print("rowcount:", cursor.rowcount)
print(cursor)

cursor.close()
db.close()

Expected Output:

1
2
3
4
rowcount: 4
MySQLCursorPrepared: 
insert into customer(name, age)
VALUES ..

In the next few lessons, we will see some practical examples of INSERT, UPDATE and DELTETE statements:

MySQL Connector/Python Tutorial

Recent Posts