This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, click here.

Applications of all types commonly access MySQL to retrieve, add, update, or delete data. The applications might be written in Python, Java, C#, or another programming language. Most languages support multiple methods for working with a MySQL database and manipulating its data.

The approach you take when accessing MySQL will depend on the programming language you’re using and the connector you choose for interfacing with the database. Whatever approach you take, the same basic principles generally apply to each environment. You must establish a connection to the database and then issue the commands necessary to retrieve or modify the data.

Because MySQL can play such an important role in application development, I wanted to provide you with an overview how to access MySQL data from within your application code. This article demonstrates how to use the MySQL Connector from within Python to establish a connection and run a query. Although I focus on only one approach to accessing MySQL, the examples should give you a general idea of what it takes to connect to a database and retrieve the information you need.

When I created the examples in this article, I used a Mac computer that was set up with the following components:

• MySQL 8.0
• Python 3.10
• PyCharm Community Edition IDE
• MySQL Connector/Python 8.0 module

If you want to try out the Python code snippets for yourself, you’ll need to set up a comparable environment, including the MySQL Connector module. You can find information about installing the module in the MySQL documentation. On my system, I used pip to add the module. Pip is a package installer for Python that makes it very easy to deploy a module.

Note: If you want to follow along, the table definition and initial set of data is included as an appendix to this article.

Defining a connection to MySQL

When connecting to a MySQL database in Python, you need to take several basic steps:

1. Import the connect method from the MySQL Connector module.
2. Use the connect method to create a connection object that includes your connection details.
3. Use the connection object to run your data-related code.
4. Close the connection.

When you create your connection, Python will generate either a MySQLConnection object or CMySQLConnection object. The object generated depends on whether you have the Connector C extension installed on your system. The extension interfaces with the MySQL C client library, which can help improve performance for large result sets. Because the extension was installed on my system, Python generated a CMySQLConnection object when I ran the example code; otherwise, it would have generated a MySQLConnection object.

You can find more information about the C extension here, but you don’t really need to worry about it for the exercises in this article. I point it out only so you’ll understand why your IDE might indicate one object type over the other, if you happen to notice the difference. With that in mind, take a look at the following Python code snippet, which establishes a connection to the travel database:

Note: Be careful where you include your password, especially the root user password in your code. A discussion of security is far beyond the scope of this article. It’s up to you to take the steps necessary to secure your application from the wide range of potential threats that can put your data at risk.

The code starts by running a from statement that imports the connect method from the MySQL Connector module (mysql.connector). The code then uses the method to create a connection object, which is assigned to the conn variable. In this case, the connect method takes four arguments: user, password, host, and database, all of which should be self-explanatory. The four arguments are enclosed in parentheses and separated by commas.

The connect method supports many more arguments than what I’ve included here. You can find details about the arguments in this MySQL topic, along with other information about connecting to MySQL.

After creating the connection object, I run a print statement. I added this statement primarily as a placeholder to show you where you would normally insert your data-related code, which I’ll be discussing later in the article. For now, the print statement simply returns a sentence stating that a connection object has been created.

The final step is to close the connection. For this, you need only call the close method on the connection object. Otherwise, that’s all you need to do to establish a connection with a MySQL database. In most cases, you should find the process fairly straightforward.

For more details on connecting to MySQL via the connect method of the connector object, check here in the MySQL documentation.

Reading connection data from a file

In the previous example, the connect method included the arguments necessary to connect to the MySQL instance. However, the method can also pull the arguments from a MySQL option file, which is a text file that usually takes the .cnf extension. To demonstrate how this works, I created a file named connectors.cnf and added the following information to the file:

These are the same arguments you saw in the previous example, without the quotation marks or commas. The arguments are grouped together under the [client] option group. By default, the connect method reads from the [client] and [connector_python] groups in the referenced option file.

Once you have your option file in place, you can access it from within your Python code by specifying the option_files argument in your connect method, as shown in the following example:

Instead of listing the individual arguments in the connect method, I’ve added the option_files argument, along with the path where I saved the connectors.cnf file. When I ran the Python code, it pulled the connection information from the file and used it when creating the connection object.

Note: My use of an option file is by no means meant to suggest that this approach is enough to secure your Python/MySQL connections. As stated previously, this code is to demo the capabilities that are possible.

For this example, I also modified the print statement. I did this to verify that I’m pulling the correct information from the option file. The statement returns the name of the user and database, which I assumed was enough to verify that everything was working properly. To retrieve this information, I used the conn variable to specify the user property and database property, which are defined on the connection object. I then concatenated these values with the string values necessary to make up the returned statement.

Catching errors in your MySQL connection

When accessing MySQL from your Python applications, you can include exception handling that’s specific to your MySQL connection. To add exception handling, enclose the connection code in a try block and then add an except statement to catch any MySQL errors, as shown in the following example:

Notice that the code now imports the Error method, along with the connect method. The code then includes a try block that contains the connection-related code and the print statement. This is the same code you saw in the previous example.

After the try block, the code includes an except statement that uses the Error method to catch the MySQL exception and create an error object, which is assigned to the err alias. This is followed by a print statement that returns the contents of the msg property that is defined on the error object.

When you run this code, Python will first try to execute the code in the try block. If there are no errors, the code runs until completion (when the connection is closed). If a MySQL error is encountered, code execution stops, and Python returns the error message. For example, if the database doesn’t exist, you’ll receive a message stating that the specified database is unknown.

Note that the error handling I’ve included here is specific to the MySQL Connector. If an error occurs for another reason, such as a problem in the Python syntax, the error will not be returned by the print statement. Python will treat the error as it would normally treat other types of errors. If you want to include error handling for the Python code itself, you must specifically add it to your application.

Querying data in a MySQL database

Up to this point, the examples in this article have been concerned only with connecting to a MySQL database, essentially verifying that such connections are possible. In the real world, however, you’ll want to do a lot more with your applications than simply check whether you can connect to a database. One of the most common scenarios is to retrieve data from the database, which you can do in a few basic steps:

1. Use the connection object to invoke the cursor method and create a cursor object.
2. Define your query and save it to a variable.
3. Use the cursor object to invoke the execute method and run your query.
4. Use the cursor object to invoke a method such as fetchall to capture the query results and save them to a variable.
5. Use the variable with the results to carry out other data-specific operations.
6. Close the cursor.

These steps are in addition to establishing the basic connection. In fact, you need the connection object to create your cursor, which makes it possible to run queries against the target database. The following Python code shows an example of how I used these steps to retrieve data from the airplanes table in the travel database:

After defining the connection, I used the conn variable to call the cursor method and create a cursor object, which I assigned to the cursor variable. I then defined a SELECT statement and assigned it to the query variable.

Next, I used the cursor object to call the execute method, passing in the query variable in as an argument. I then used the cursor object to call the fetchall method in order to retrieve all the rows returned by the SELECT statement. You can also use the fetchall method to fetch a certain number of rows if the results of the query are very large. Then you would fetch N rows, the output those rows, then fetch N more rows. Finally, I assigned the results to the result variable, which stores the rows of data as a series of tuples.

After you capture the result set in a variable, you can use the variable’s data in your application however you need to. For this example, I created a simple for loop that iterates through the result set and assigns data to the r variable one row at a time. The contents of the r variable are then printed out for each loop. Finally, I used the cursor object to call the close method in order to close the cursor. On my system, Python returned the following results after I ran the code:

Once you understand the logic of how to retrieve data, the process itself is fairly straightforward. In many cases, however, your SELECT statement will be much longer than the one in this example. If you want to spread your statement over multiple lines, you can enclose each line in quotes and then enclose all lines in parentheses, as shown in the following example:

When your query spans multiple lines in this way, be sure to include the necessary space at the end of each line (except the last) so your statement is submitted to MySQL correctly. For this example, the Python code returns the following results:

As you can see, the results include only airplanes with a max_weight value greater than 10000. The results are also sorted in descending order based on the max_weight values.

Passing variable values into your query

When using the MySQL Connector to query data, it can often be useful to pass variable values into your SELECT statement (or whichever SQL statement you’re defining). You can do this by including special %s markers as placeholders where the variable values should be inserted. The following example shows a SELECT statement that contains two of these markers:

I added the %s markers to the WHERE clause as part of a BETWEEN operator. The variable values will be inserted in place of the markers when the SELECT statement is executed.

For this example, I hard-coded the variable values and assigned them to the weight_start and weight_end variables. In most cases, you’ll likely want to capture these values through user interaction or other means and then assign them dynamically to the variables at runtime, but for now, hard-coding them is enough to demonstrate how this process works.

After you define your variables, you can pass them in as an argument to the execute method that runs your query. The variables should be added as a second argument, enclosed in parenthesis and separated with a comma. In addition, they must be specified in the same order that you want them applied to the query. The Python code should now return the following results:

As expected, the results include only those airplanes with a max_weight value between 1000 and 10000. If you had built your application to capture user input, the user might have specified different variable values, which would have been passed into the application dynamically.

Getting started with Python and MySQL

In this article, I’ve introduced you to how to use the MySQL Connector in Python to access a MySQL database and retrieve data from one of its tables. As with most aspects of application development, the approach I’ve taken here is only one of a variety of methods you can use to achieve the same results. You could have constructed your code differently, used a different connector, or even used a different programming language. Even so, what I’ve shown you should still give you a general idea of how to retrieve MySQL data within your application.

Be aware, however, that this article is by no means meant to be a comprehensive tutorial on how to use Python to develop a data-driven application. Although Python might be considered a lightweight language when compared to a one such as Java or C#, it is still a comprehensive language with many of its own complexities and nuances. Before you build an application that connects to MySQL, you should have a solid foundation in the language and what it takes to create an efficient and secure application. This article is meant only as a starting point for accessing MySQL data, although it should be enough to show you how easily you can interface with a MySQL database and retrieve the necessary data.

Appendix: Preparing your MySQL environment

The Python examples in this article connect to the travel database on a local MySQL instance and retrieve data from the airplanes table, which includes a foreign key that references the manufacturers table. This is the same database and tables you saw in previous articles in this series, although I’ve limited the data that I inserted into the tables to only a small amount, which is all that’s needed to demonstrate the concepts in this article. If you plan to try out the examples, start by running the following script against your MySQL instance:

The script will create the travel database and add the manufacturers and airplanes tables in the proper order to accommodate the foreign key defined in the airplanes table. After you the create the tables, you can then run the following INSERT statements:

The INSERT statements first populate the manufacturers table and then the airplanes table. Be sure to run the statements in the order shown here because of the foreign key.