The cmd_query() method is very simple. It takes one argument, which is the query to execute, and it returns a dictionary with information about the executed query. The exact content of the returned dictionary depends on the query. For example, for a SELECT query, the dictionary will include information about the selected columns. For all queries, the status of the query is also included. The examples in this section will include the content of the result dictionary.
A simple example of using cmd_query() to execute a SELECT query that returns a single row is shown 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")
# Execute a query
result = db.cmd_query(
"""SELECT *
FROM world.city
WHERE ID = 130"""
)
# Print the result dictionary
print("Result Dictionary\n" + "="*17)
printer.pprint(result)
db.close()Listing 1 Executing a Simple SELECT Using cmd_query()
Caution
This example (and several of the other examples in this blog) has the query parameter (130 for the value of ID in this example). This is acceptable since it is a fixed query. However, never inline user-submitted data into the queries.
The program creates a connection, as you saw in this blog. After the connection has been established, the query is executed using the cmd_query() method and the returned dictionary is stored in the result variable, which is printed using the pretty printing module (pprint):
Result Dictionary
=================
{'columns': [('ID', 3, None, None, None, None, 0, 49667),
('Name', 254, None, None, None, None, 0, 1),
('CountryCode', 254, None, None, None, None, 0, 16393),
('District', 254, None, None, None, None, 0, 1),
('Population', 3, None, None, None, None, 0, 32769)],
'eof': {'status_flag': 16385, 'warning_count': 0}}The columns part of the result dictionary will be discussed in detail in the next blog; for now, just know that the first element of the tuple for a column is the column name. The second part of the result dictionary, the eof element, includes some details for the query; the fields included depend on the query. The values you get for the last integer in the column tuples and for the status_flag may be different from the example output because they depend, for example, on whether the C Extension is used or not.
Common fields in the eof element are the status_flag and warning_count fields. The status flag is not nearly as useful as it sounds; in fact, the value is undocumented, and no significance should be taken from its value. The warning count, on the other hand, shows the number of warnings that occurred during the query.
For queries without a result set (i.e. not returning rows), the eof information is an “OK package,” which includes information about the query. For example, the following information is the result of an UPDATE statement that updates 14 rows using the pure Python implementation:
Result Dictionary
=================
{'affected_rows': 14,
'field_count': 0,
'info_msg': 'Rows matched: 14 Changed: 14 Warnings: 0',
'insert_id': 0,
'status_flag': 1,
'warning_count': 0}The two most important parameters are
affected_rows: This shows the number of affected rows. In this case, 14 rows were updated.insert_ id: ForINSERTandREPLACEstatements inserting data into a table with an auto-increment column, theinsert_idis the ID of the first row inserted by the statement.
When use_pure = False, the info_msg parameter is not present and the status_flag is replaced with server_status.
A sibling to cmd_query() is the cmd_query_iter() method, which can be used to send multiple queries to MySQL. Executing multiple queries in one call and handling multiple result sets are topics of the next article.
It is all well and good to execute queries like in the example just discussed, but without retrieving the results, queries like the SELECT statement in Listing 1 are not very interesting. To fetch the rows found, the get_rows() and get_row() methods are used.
