Now that you know how to create a SQLite table, you’re ready to retrieve existing data. As mentioned earlier, Python is excellent at analyzing, visualizing, manipulating, and testing data. For this reason, you will pull SQLite data into your Python environment.
SQL Commands Used for Data Retrieval
For all data retrieval methods in this exercise, you will need to use the SQL commands SELECT * FROM table_name
to identify which table in the database you will be pulling data from.
.fetchone()
method
The first method used for data extraction is the .fetchone()
method. This method, in combination with cursor.execute()
, will fetch one row of the data. Specifically, it will pull the first row of the data table. Here is an example below:
# Return first row in students cursor.execute("SELECT * FROM students").fetchone()
This will output a tuple representing the row data.
# Output (101, 'Alex', 32, '2022-05-16', 'Pass')
.fetchmany()
method
If you want to pull more than one row, you can use the .fetchmany()
method. This method will return the first set of specified rows.
# Return first three rows in students cursor.execute("SELECT * FROM students").fetchmany(3)
This will output a list of tuples where each tuple is a separate row.
# Output [(101, 'Alex', 32, '2022-05-16', 'Pass'), (102, 'Joe', 32, '2022-05-16', 'Pass'), (103, 'Stacy', 10, '2022-05-16', 'Pass')]
.fetchall()
method
The last fetch method is .fetchall()
, and it does just that. This method will fetch every row of data from the data table.
# Return all rows in students cursor.execute("SELECT * FROM students").fetchall()
Instructions
The module sqlite3
, connection object, and cursor object are already in your text editor.
Use .fetchone()
to pull the first row of the titanic
table from the titanic.db database, and save this to the object one
.
Be sure to print one
to view the output.
Now retrieve the first 10
rows of the Titanic data table using .fetchmany()
, and save these rows to the object ten
.
Once again, print ten
to view the output. You may want to comment out your previous print statement. This time you should see 10 tuples inside of a list in your output.
So far, you have returned the first row and the first ten rows. Now return all the rows.
Use .fetchall()
to retrieve every row from the Titanic data table, and save these rows to the object all_rows
. Print all_rows
to view the output. You should see a lot of data outputted into your terminal.
Now that you know how to use the fetch methods, it’s time to get a little picky with the data you fetch.
Here is an example using the students
data table.
# Return the rows of students with a passing grade cursor.execute('''SELECT * FROM students WHERE Grade = 'Pass';''').fetchall()
This line of code will retrieve all the rows that meet the WHERE
SQL condition. The output will only consist of the students that have a passing grade. (Grade
is the field name).
Use the .fetchall()
method to retrieve all the passenger data for those who survived from the titanic
data table. Save this data to the object all_survived
. Remember, for the field Survived
, a 1
means the passenger survived, and a 0
means they did not. You may want to comment out all of your previous print statements.