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.
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')
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')]
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()
sqlite3, connection object, and cursor object are already in your text editor.
.fetchone() to pull the first row of the
titanic table from the titanic.db database, and save this to the object
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
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.
.fetchall() to retrieve every row from the Titanic data table, and save these rows to the object
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).
.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
1 means the passenger survived, and a
0 means they did not. You may want to comment out all of your previous print statements.