MySQL for Python Developers
Sequence
Using cursors
Sequence

Setting up a cursor in Python for MySQL

In this lesson, we'll be discussing how to set up a cursor in Python for interacting with a MySQL database. A cursor is an essential tool when working with databases, as it is used to send commands over to the database and retrieve data when requested. We will start by creating a connection to the database and setting up a cursor using the connection object. Then, we will learn how to close the cursor and the connection when we are done with it.

Prerequisites

Before we get started, you need to have a MySQL database and a Python client library installed. For this course, we've using PlanetScale for the database. We are also using the popular mysql-connector-python library. You can install it using the following command:

Terminal
pip install mysql-connector-python

Once the library is installed, you can establish a connection to your MySQL database using the connect method, as shown below:

Python
from mysql.connector import connect
# Replace with your own database credentials
config = {
"user": "username",
"password": "password",
"host": "localhost",
"database": "database_name",
}
connection = connect(**config)

Now that we have a connection to the database, let's move on to setting up the cursor.

Creating a cursor

To set up the cursor, we will create a variable called cursor and assign it the value of connection.cursor().

Python
cursor = connection.cursor()

Optionally, you can set the dictionary=True option to have the cursor return results as a list of dictionaries instead of the default tuple of tuples. This can make it easier to work with the data, especially when dealing with many columns.

Python
cursor = connection.cursor(dictionary=True)

If you are using a different MySQL client library, the process of setting up the dictionary cursor may be different. Consult the documentation of the library to learn how to set up the dictionary cursor.

Closing the cursor and connection

When you are done using the cursor, it is a good practice to close it using the close() method. This ensures that there are no issues if the script continues to run after this point.

Python
cursor.close()

In addition to closing the cursor, you should also close the connection to the database using the close() method.

Python
connection.close()

If you stop the script at this point, you wouldn't necessarily need to close the cursor and the connection, as they will automatically be closed when the script terminates. However, for the sake of clarity, it's a good practice to include the close() methods.

Conclusion

In this lesson, we learned how to set up a cursor in Python for interacting with a MySQL database. We covered creating a connection to the database, setting up a cursor using the connection object, closing the cursor and the connection, and working with the optional dictionary=True setting. In the next lesson, we will learn how to create a new table in the database using the cursor we just set up.

About this lesson

Learn how to set up a Python cursor to interact with a MySQL database.

01:30
Closed captioned

Meet your instructor, Anthony Herbert

Software Developer

I'm a software developer and educator focused on helping others build their dream software projects.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.