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.
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))
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': '[email protected]'},
