MySQLConverter: Automatic Conversion into Native Python Types

MySQLConverter: converter.row_to_python

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 row_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.3

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

 

Вас заинтересует / Intresting for you:

Types of JDBC drivers and MySQ...
Types of JDBC drivers and MySQ... 1618 views egurutol Sun, 28 Oct 2018, 17:01:03
Kotlin: Programming with Lambd...
Kotlin: Programming with Lambd... 730 views Боба Fri, 12 Feb 2021, 12:00:01
First Simple Java Program: ent...
First Simple Java Program: ent... 1386 views natalia Thu, 21 Jun 2018, 14:10:35
The Rise of DevOps: make softw...
The Rise of DevOps: make softw... 913 views Семен Mon, 04 Nov 2019, 05:58:13