The previous blog discussed how to connect to MySQL from the Python program. However, there is not much point in creating a connection just to get a connection ID or do nothing. The whole point of MySQL Connector/Python, after all, is to execute queries. This article will look at the basics of query execution.
First, you will learn how to execute queries using the cmd_query() method of the connection object. Then you will explore the more advanced concept of cursors. Lastly, you’ll see how to handle user input.
Tip
There are a number of example programs in this chapter. All example programs that appear in a listing are available for download. See the discussion of example programs in the previous article for more information about using the example programs.
Simple Execution
There are a few different methods to execute queries through MySQL Connector/Python. The simplest, but also least powerful, is the cmd_query() method of the connection object. I will also discuss the get_rows() and get_row() methods to fetch the result of a SELECT statement.
Before diving into the three methods for querying and fetching results, it is useful to consider the relationship between them so take a look at Figure 1.
Figure 1 The flow of executing queries through the connection object
Figure 1 shows that once the connection has been created, a query can be executed using the cmd_query() method. If there is a result (rows are returned), either the get_rows() or get_row() method can be used to read the rows. The connection can be reused for more queries. At the end, when there are no more queries, the connection is closed using the close() method. This is a little simplified compared to real-world programs; for example, there is no consideration about transactions. However, it serves as a useful high-level overview.
The cmd_query(), get_rows(), and get_row() methods, as well as how to handle the results, are the main topics of this section.
