In the previous examples, the rows returned by the query were handled manually. This can be a great way to understand what is going on, but in more real-world cases, it is usually preferred to get the result returned as native Python types.
Note
As with the previous example, this discussion is only required for MySQL Connector/Python 8.0.11 and earlier including version 2.1. In later versions, the conversion happens automatically; however, it is safe to call r
ow_to_python()as it will just be a null-operation if the conversion has already happened.
MySQL Connector/Python includes the conversion module that provides tools for doing conversions of the results returned by MySQL Server. Specifically, the row_to_python() method in the MySQLConverter class can convert all values in a row. Listing 1 shows the equivalent of the example in Listing 4, but this time using row_to_python() to handle the conversion.
import mysql.connector
from mysql.connector.conversion import MySQLConverter
# 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()
# Initialize the converter
converter = MySQLConverter(
db.charset, True)
# Print the rows found
print(__file__ + " - Using MySQLConverter:")
print("")
print(
"{0:15s} {1:7s} {2:3s}".format(
"City", "Country", "Pop"
)
)
for city in cities:
values = converter.row_to_python(
city, result["columns"])
print(
"{0:15s} {1:^7s} {2:4.1f}".format(
values[0],
values[1],
values[2]/1000000.0
)
)
db.close()Listing 1 Converting Query Results Using MySQLConverter.row_to_python()
The important parts of the example in Listing 1 are those involving the MySQLConverter class. First, the class is imported; then the class is instantiated when the result set is ready to be printed; and finally, the rows are converted by using the row_to_python() method.
When the MySQLConverter class is instantiated, two arguments are required: the character set and whether Unicode is used in Python. That it is possible to configure both when creating the connection. The character set is exposed through the charset property of the connection so, as before, that is used to ensure that a change of connection character set does not require code changes when converting the row. The MySQLConverter class knows how to handle utf8mb4, so there is no need to take care of that explicitly. There is no property for the use of Unicode in Python, so it is necessary to specify it explicitly.
With an instance of the MySQLConverter class available, the rows can be converted one at a time. The column information from the result of the cmd_query() call is passed as an argument along the values to be converted; this ensures that MySQL Connector/Python knows the data type for each column. The output is the same as for the example in Listing 4 except the information in the eof part has be removed:
listing_1.py - Using MySQLConverter
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.3The examples thus far have fetched all rows in the result set and then used the rows. That is great for a small result, but it is not efficient for a large number of rows with large values.
