MySQL for Python Developers
Sequence
Querying data

Retrieving data from a MySQL database using Python

In the last lesson, we covered how to handle SQL injection by using placeholders. In this lesson, we will learn how to get data back from the database using SQL SELECT statements and Python.

Previously, we learned how to insert data into the database. Now, we will learn how to retrieve data using SELECT statements in a slightly different way.

Setting up sample data

First, let's comment out the cursor execute for the insert statement and guest name and email. Then, we'll write a new statement to retrieve data from the database.

Python
# cursor.execute("INSERT INTO guests (guest_name, email) VALUES (?, ?)", (guest_name, email))

Executing a SELECT statement

To start, we will use the cursor.execute() method to run a simple SELECT query. This method will allow us to run the query against the database and return all the rows from the table.

Python
cursor.execute("SELECT * FROM guests")

To get the rows from the script, we can now use the cursor again. There are two methods to achieve this — cursor.fetch1() or cursor.fetchall(). The fetchall() method will return all the results, while fetch1() will return just one row.

cursor.fetchall() has a return value, so we can assign it to a variable.

Python
results = cursor.fetchall()

Then, we will simply print the results:

Python
print(results)

Running the script, we should now see a list of dictionaries, which comes from the dictionary=True mentioned in the previous video. The first dictionary represents the first row, and so on.

For example:

[{'id': 2, 'guest_name': 'Anthony', 'email': 'anthony@gmail.com'},
{'id': 3, 'guest_name': 'Brittany', 'email': 'brittany@yahoo.com'}]

Writing a query to return a single row

Now, let's write another query to return just one row:

Python
cursor.execute("SELECT * FROM guests WHERE id = 2")

We can now use the cursor.fetchone() method to retrieve the result.

Python
result = cursor.fetchone()

Followed by a simple print() statement to display the result.

Python
print(result)

Running the script again, we now get a single dictionary instead of a list of dictionaries.

Example:

{'id': 2, 'guest_name': 'Anthony', 'email': 'anthony@gmail.com'}

Recap and next steps

In this lesson, we learned how to work with a MySQL database from a Python script. We used cursor.execute() to run SELECT statements and cursor.fetchone() or cursor.fetchall() to retrieve the results.

We are now ready to extend this knowledge by building a web app using these same concepts, alongside MySQL statements that you are already familiar with. In the next section, we will create a Flask web app that uses a database to store and retrieve information. Stay tuned!

About this lesson

Learn how to fetch data from a MySQL database with Python fetchone() and fetchall() methods.

03:02
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.