Like we used the fetch methods, we can also use a for loop to retrieve data. The following code will iterate through each row in the students
table and print each row where the Grade
field is 'Pass'
.
for row in cursor.execute('''SELECT * FROM students WHERE Grade = 'Pass';'''): print(row)
You can also use a for loop to iterate through a table field and calculate a measurement.
# save all rows from a field with .fetchall() then use a for loop to find some sort of result.` major_codes = cursor.execute("SELECT major_code FROM students;").fetchall() # Obtain the average of the tuple list by using for loops sum = 0 for num in major_codes: sum = sum + num[0] average = sum / len(major_codes) # Show average print(average)
Let’s walk through this example code:
- We used a SQL statement to retrieve the
major code
field from thestudents
table. - We created the variable
sum
initialized at 0, to sum up the total values in the data. - We used a for loop to iterate through every number in
major_code
to create the mean averagemajor_code
. - We add
num[0]
tosum
at each iteration. Note thatnum
is a tuple of length 1 (such as(12,)
) sonum[0]
will allow us to access the actual integer. - We find the average by dividing
sum
and the length ofmajor_codes
.
Instructions
The module sqlite3
, connection object, cursor object are already in your text editor. The object age
, which is also in your text editor, includes a list of every age from the passengers of the titanic
data table.
Create a variable called sum
, set it equal to zero. You will use this to sum all passengers below the age of 18.
Create a for loop that iterates through each age in the Age
tuple and counts the number of people who were younger than 18. In your for loop, you should:
- Use an if statement to check if each age within
age
is less than 18 years old. - Increment sum by one each time there is a passenger less than 18 years old.
When your for loop is complete, print sum
to see the results.
Check out the Advanced Tip in the hint below to learn more about pulling and analyzing SQLite data!