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.
# 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.
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.
results = cursor.fetchall()
Then, we will simply print the results:
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:
cursor.execute("SELECT * FROM guests WHERE id = 2")
We can now use the cursor.fetchone() method to retrieve the result.
result = cursor.fetchone()
Followed by a simple print() statement to display the result.
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!
