Some queries, such as CREATE TABLE, ALTER TABLE, INSERT, UPDATE, and DELETE statements, do not return any result and checking whether the query succeeded is all that needs to be done. However, in general, the majority of queries in a program are SELECT queries that return a result set. For queries that return a result set, the rows must be fetched. When the query is executed with cmd_query(), the corresponding method for fetching the rows is get_rows(), which returns all rows found by the query.
The usage of get_rows() is simple. All that is required is to call it, and the rows are returned as a list of tuples, as illustrated in Listing 1.
import mysql.connector
import pprint
printer = pprint.PrettyPrinter(indent=1)
# Create connection to MySQL
db = mysql.connector.connect(
option_files="my.ini", use_pure=True)
# Execute a query
result = db.cmd_query(
"""SELECT Name, CountryCode,
Population
FROM world.city
WHERE Population > 9000000
ORDER BY Population DESC"""
)
# Fetch the rows
result_set = db.get_rows()
# Print the result dictionary
print("Result Dictionary\n" + "="*17)
printer.pprint(result)
# Print the rows
print("\nResult Set\n" + "="*10)
printer.pprint(result_set)
db.close()Listing 1 Fetching Rows with get_rows()
The program in Listing 1 is similar to the one in Listing 1, except in this case, it forces the use of the pure Python implementation by using use_pure = True. The query this time looks for the cities with a population greater than nine million and asks for the resulting rows to be sorted by the population in descending order. The output looks like Listing 2. The output is generated with MySQL Connector/Python version 8.0.11.
Note
An important change in MySQL Connector 8.0.12 is to align the behavior of
get_rows()andget_row()between the pure Python and the C Extenstion implementation. This means that in MySQL Connector/Python 8.0.12 and later, the pure Python implementation ofget_rows()andget_row()no longer return the result as byte arrays. The following discussion is never the less useful to illustrate what happens with the result.
Result Dictionary
=================
{'columns': [('Name', 254, None, None, None, None, 0, 1),
('CountryCode', 254, None, None, None, None, 0, 16393),
('Population', 3, None, None, None, None, 0, 1)],
'eof': {'status_flag': 33, 'warning_count': 0}}
Result Set
==========
([(bytearray(b'Mumbai (Bombay)'), bytearray(b'IND'), bytearray(b'10500000')),
(bytearray(b'Seoul'), bytearray(b'KOR'), bytearray(b'9981619')),
(bytearray(b'S\xc3\xa3o Paulo'), bytearray(b'BRA'), bytearray(b'9968485')),
(bytearray(b'Shanghai'), bytearray(b'CHN'), bytearray(b'9696300')),
(bytearray(b'Jakarta'), bytearray(b'IDN'), bytearray(b'9604900')),
(bytearray(b'Karachi'), bytearray(b'PAK'), bytearray(b'9269265'))],
{'status_flag': 33, 'warning_count': 0})Listing 2 The Output of Executing the Program in Listing 1
The result dictionary is similar to the previous example with the column information and the eof information. More interesting is the result set returned by get_rows(). The values are returned as strings represented by an array of their binary data (bytearray). While this is technically a correct representation of the result, it is not very useful. For example, the population is an integer, so it’s better to have the data as an integer rather than a string. Another issue is a city like São Paulo where the byte sequence is “S\xc3\xa3o Paulo”; note that the ã is represented as \xc3\xa3.
Note
If you use the C Extension or version 8.0.12 and later, the values are not returned as byte arrays but as Unicode string. This is an example where the two implementations were not identical in earlier versions.
In order for the data to be really useful in the program, it is necessary to convert the byte arrays to native Python data types. The exact way to convert depends on the data, and it is beyond the scope of this book to implement explicit conversion for each data type. However, it is also not required because MySQL Connector/Python already includes the code for it; more about this shortly. For now, see Listing 3 for an example of converting the strings and integer in the result of Listing 1.
Note
This example and the following examples where the output includes non-ASCII characters show how the difference in Unicode handling between Python 2 and Python 3 makes a difference. The examples assume Python 3 and MySQL Connector/Python 8.0.11. The examples will not work in version 8.0.12 and later.
import mysql.connector
# Create connection to MySQL
db = mysql.connector.connect(
option_files="my.ini", use_pure=True)
# Execute a query
result = db.cmd_query(
"""SELECT Name, CountryCode,
Population
FROM world.city
WHERE Population > 9000000
ORDER BY Population DESC"""
)
# Fetch the rows
(cities, eof) = db.get_rows()
# Print the rows found
print(__file__ + " – Using decode:")
print("")
print(
"{0:15s} {1:7s} {2:3s}".format(
"City", "Country", "Pop"
)
)
for city in cities:
print(
"{0:15s} {1:^7s} {2:4.1f}".format(
city[0].decode(db.python_charset),
city[1].decode(db.python_charset),
int(
city[2].decode(db.python_charset)
)/1000000.0
)
)
# Print the eof package
print("\nEnd-of-file:");
for key in eof:
print("{0:15s} = {1:2d}".format(
key, eof[key]
))
db.close()Listing 4 Converting the Result to Native Python Types
The main difference between Listing 1 and Listing 3 is in the handling of the result set. First, the result set is split into the returned rows (cities) and the end-of-file (eof) package. The cities are then printed while converting the values to native Python types.
String values are converted using the decode() method of the bytearray type. This requires parsing the character set of the connection. In this case, the character set is utf8 (using the default); however, to ensure that any character set can be handled, the python_charset property of the connection is used to set the character set to use in the conversion. Since utf8mb4 is a MySQL invention, it is necessary to catch that and use utf8 instead; this is the difference between the charset and python_charset properties. The population can be converted using the int() function and then divided by one million to get the population in millions.
Finally, the end-of-file part of the result set is printed. This is the same information as is available in the eof part of the results returned by cmd_query(). The output of the program is
listing_3_4.py – Using decode
City Country Pop
Mumbai (Bombay) IND 10.5
Seoul KOR 10.0
São Paulo BRA 10.0
Shanghai CHN 9.7
Jakarta IDN 9.6
Karachi PAK 9.3
End-of-file:
status_flag = 33
warning_count = 0Manually converting the arrays of bytes is not feasible in general and it is not necessary, as it will be shown in the next article, when automatic conversion of rows is discussed.
