How to Extract Elements From a Database Using fetchall() in Python

1. Extract Elements From a Database File Using fetchall() in Python
2. Use list(cursor) as an Alternative to Extract Elements From a Database in Python
3. Conclusion

This article aims to represent the working method to extract elements from a database using fetchall() and how to display them correctly. This article will also discuss how the list(cursor) function is used in a program.

Extract Elements From a Database File Using fetchall() in Python

This program will establish a secure SQL connection with a database file with the .db extension. After establishing a connection, the program will fetch the data stored in a table in this database.

As it is a program to extract elements using fetchall(), the data will be extracted and displayed using a for loop.

Import Sqlite3 and Establish Connection With the Database

sqlite3 is an import package in Python for accessing databases. It is an in-built package; it does not require installing additional software to be used and can be directly imported using import sqlite3.

The program uses a try block to test for errors while loading the database and throws an error message when a connection does not get established using the exception block. At last, the program closes the connection with the finally block.

However, before understanding how to use fetchall() to retrieve items, it must first be understood how SQLite establishes a connection. The program declares a method allrowsfetched(), and inside it, the try block is inserted and declares a variable database_connecter.

This variable will establish a connection with the database and load its contents, as shown below in the code snippet.

import sqlite3


def allrowsfetched():
    try:
        database = sqlite3.connect("samplefile.db")
        cursorfordatabase = database.cursor()
        print("Connection is established")
    except Exception as e:
        print(e)

After establishing a connection, the cursor needs to be created for the database, a type of connector that aids in executing commands for SQL databases using Python.

In the above program, the cursor is created and stored in the variable cursorfordatabase using the syntax database.cursor(). If all steps above are executed correctly, the program will print a success message.

Create Cursor Object for Using the fetchall() Method

To extract elements using fetchall(), we must ascertain the database contents. The database used in the program has multiple tables stored inside it.

The program needs to extract a table named employees specifically. It must generate a query:

1. A query is generated using the syntax SELECT * from table_name. In the program, the query is for finding a table named employees from the database, and it is stored in the variable query_for_sqlite.
2. Once the query is generated, the cursor.execute() method executes that query against the database.
3. Finally, cursor.fetchall() syntax extracts elements using fetchall(), and the specific table is loaded inside the cursor and stores the data in the variable required_records.
4. The variable required_records stores the whole table itself, so returning the length of this variable provides the number of rows inside the table.
5. The number of rows is printed using the len(required_records) syntax.

query_for_sqlite = """SELECT * from employees"""
cursorfordatabase.execute(query_for_sqlite)
required_records = cursorfordatabase.fetchall()
print("Rows Present in the database:  ", len(required_records))

Display the Row Elements Using the for Loop

After the steps to extract elements using fetchall() are initiated, the program uses a for loop to print the elements. The for loop runs for the number of times the rows are present inside the variable required_records.

Inside this, the individual elements are printed using the index of the rows. In this database, there are 8 rows (index count starts from 0 and ends at 7).

print("Data in an ordered list")
for row in required_records:
    print("Id: ", row[0])
    print("Last Name: ", row[1])
    print("First Name ", row[2])
    print("Title: ", row[3])
    print("Reports to: ", row[4])
    print("dob: ", row[5])
    print("Hire-date: ", row[6])
    print("Address: ", row[7])
    print("\n")

Handle the Exceptions

Once the program’s purpose is fulfilled, which is to extract elements using fetchall(), it is required to release the loaded data inside the cursor and connection variable from memory.

1. At first, we use the cursor.close() syntax to release the memory stored inside the cursor variable cursorfordatabase.
2. The program then needs to state the exception handling, which is the except and finally blocks of the program, followed after the try block.
3. The except block is used for sqlite3 errors. So, when the connection is not established with the database, the program displays an error message instead of crashing at run time.
4. The finally block gets executed last, after execution of one out of the two blocks, try or except. It closes the SQLite connection and prints a relevant message.

The execution of the finally block happens at the end no matter which block is executed before it, providing a closing stance to the program.

cursorfordatabase.close()

   except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if database:
            database.close()
            print("Connection closed")

Complete Code to Extract Elements From a Database File in Python

The working code for the program is provided below to understand the concepts better.

import sqlite3


def allrowsfetched():
    try:
        database = sqlite3.connect("samplefile.db")
        cursorfordatabase = database.cursor()
        print("Connection established")

        query_for_samplefile = """SELECT * from employees"""
        cursorfordatabase.execute(query_for_samplefile)
        required_records = cursorfordatabase.fetchall()
        print("Rows Present in the database:  ", len(required_records))
        print("Data in an ordered list")
        print(required_records)
        for row in required_records:
            print("Id: ", row[0])
            print("Last Name: ", row[1])
            print("First Name ", row[2])
            print("Title: ", row[3])
            print("Reports to: ", row[4])
            print("dob: ", row[5])
            print("Hired on: ", row[6])
            print("Address: ", row[7])
            print("\n")

        cursorfordatabase.close()

    except sqlite3.Error as error:
        print("Failed to read data from table,", error)
    finally:
        if database:
            database.close()
            print("The Sqlite connection is closed")


allrowsfetched()

Output: When the table is found successfully,

"C:/Users/Win 10/main.py"

Connection established
Rows Present in the database:   8
Data in an ordered list

Id:  1
Last Name:  Adams
First Name  Andrew
Title:  General Manager
Reports to:  None
Birthdate:  1962-02-18 00:00:00
Hire-date:  2002-08-14 00:00:00
Address:  11120 Jasper Ave NW
.
.
.
Connection closed

Process finished with exit code 0

Output: When the required table is not present,

"C:/Users/Win 10/main.py"

Connection established
Failed to read data from table, no such table: salary
Connection closed

Process finished with exit code 0

Here, the error is created by using the table name salary as a query, e.g., query_for_samplefile = """SELECT * from salary""".

Use list(cursor) as an Alternative to Extract Elements From a Database in Python

The methods to extract element using fetchall() has been discussed till now, though there are other methods as well like fetchone() and fetchmany().

We can also extract elements without using the fetch() method; instead, we can use list(cursor). This process extracts all elements just like fetchall().

The method saves memory footprint. Unlike fetchall(), which loads the whole table, list(cursor) runs a loop, extracts elements serially, and prints them from the database without storing them anywhere.

The code below gives an understanding of how to use it.

All the steps are similar to the above program, except where no new variable is initialized to store the table using fetchall(). The cursor cursorfordatabase is put inside a for loop, and the row is printed.

As the cursor object only stores the query, it takes minimal to no space in the memory footprint.

query_for_sqlite = """SELECT * from employees"""
cursorfordatabase.execute(query_for_sqlite)
for row in cursorfordatabase:
    print("\n", row)

The index can also fetch an ordered list, just like the last program.

for row in cursorfordatabase:
    print("id:", row[0])
    print("l_name:", row[1])

Conclusion

This article focused on demonstrating how to extract elements using fetchall() in a Python program. You have learned concepts like cursor() and functions of syntax like cursor.execute(), sqlite3.connect, and handling exception blocks.

You also learned about the list(cursor) method and how it can be an alternative to extract elements from a database.