Learn

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.

Sign up to start coding

By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?