JIYIK CN >

This article aims to describe fetchall()the working methods of extracting elements from a database using and how to display them correctly. This article will also discuss list(cursor)how functions can be used in programs.

fetchall()Extract elements from a database file using Python

The program will establish a secure SQL connection to a database file with a .db extension. Once the connection is established, the program will retrieve the data stored in the database tables.

Since it is a fetchall()program that uses to extract elements, fora loop will be used to extract and display the data.

Import Sqlite3 and establish a connection with the database

sqlite3 is an import package for accessing databases in Python. It is a built-in package; it does not require the installation of additional software to use it, and can be import sqlite3imported directly.

The program uses a block to test for errors when loading the database tryand exceptionthrows an error message when establishing a connection using a block. Finally, the program closes finallythe connection with a block.

However, before you learn how to fetchall()retrieve items using , you must first understand how SQLite establishes a connection. This program declares a method allrowsfetched(), inserts trya block within it, and declares a variable database_connecter.

This variable will establish a connection with the database and load its contents as shown in the following 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)

Once the connection is established, you need to create a cursor for the database, which is a connector that helps execute commands for SQL databases using Python.

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

fetchall()To create a cursor object using the method

To use fetchall()extract elements, we must determine the database content. The database used in the program stores multiple tables.

The program needs to specifically extract a employeestable called . It must generate a query:

1. The query is generated using the syntax SELECT * from table_name. In the program, the query is to find a employeestable named from the database, which is stored in the variable query_for_sqlite.
2. After building the query, cursor.execute()the method executes it against the database.
3. Finally, cursor.fetchall()the syntax uses fetchall()to extract elements and load a specific table into a cursor and store the data in the variable required_records.
4. The variable required_recordsstores the entire table itself, so returning the length of the variable provides the number of rows in the table.
5. Use len(required_records)the syntax to print line numbers.

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

Use forto loop through the row elements

After being started with fetchall()the step that extracts the elements, the program uses fora loop to print the elements. forThe loop runs the number of times required_recordsthe row appears in the variable .

Inside this, the individual elements are printed using the row's index. In this database, there are 8 rows (index counting starts at 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")

Handling Exceptions

Once the purpose of the program has been achieved, i.e. fetchall()extracting elements using , the data loaded in the cursor and connection variables need to be released from memory.

1. First, we use cursor.close()the syntax to release cursorfordatabasethe memory stored in the cursor variable .
2. The program then needs to describe exception handling, which is the exceptand finallyblocks of the program, tryimmediately following the block.
3. exceptblocks for sqlite3 errors. So when a connection is not established with the database, the program displays an error message instead of crashing at runtime.
4. finallyThe block is executed last, after one of the two blocks is executed, tryor except. It closes the SQLite connection and prints a relevant message.

finallyThe execution of the block occurs last, regardless of which blocks executed before it, providing a closing gesture for 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 for extracting elements from a database file using Python

The working code of 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 successfully found,

"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 does not exist,

"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 salarycreated by using the table name as query, query_for_samplefile = """SELECT * from salary"""e.g.

Use in Python list(cursor)as an alternative to extracting elements from the database

The method of extracting elements using fetchall()has been discussed so far, although there are other methods such as fetchone()and fetchmany().

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

This method saves memory usage. fetchall()Instead of loading the entire table, list(cursor)you run a loop that successively extracts elements and then prints them from the database without storing them anywhere.

The following code shows how to use it.

All the steps are similar to the above program, except that no fetchall()new variable is initialized to store the table. The cursor cursorfordatabaseis put forinto a loop, and the row is printed.

Since the cursor object only stores the query, it takes up 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)

Indexing can also get ordered lists, just like in the previous program.

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

in conclusion

This article focuses on showing how to use extract elements in Python programs fetchall(). You have learned cursor()concepts such as and syntax functions such as cursor.execute(), sqlite3.connectand handle exception blocks.

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