Python cursor’s fetchall, fetchmany(), fetchone() to read records from database table

This article demonstrates the use of Python’s cursor class methods fetchall(), fetchmany(), and fetchone() to retrieve rows from a database table. This article applies to all the relational databases, for example, SQLite, MySQL, PostgreSQL.

Python Database API Specification v2.0 (PEP 249) has been designed to encourage and maintain similarity between the Python modules used to access databases. So it doesn’t matter which database you use. Be it MySQL, PostgreSQL, and SQLite syntax, the syntax of functions and how to access the relational database are the same in all database modules.

We generally use the following Python module to work with Databases.

Database	Module
MySQL	MySQL Connector Python
PostgreSQL	Psycopg2
SQLite	sqlite3

Above all modules adhere to Python Database API Specification v2.0 (PEP 249).

This lesson will show how to use fetchall(), fetchmany(), and fetchone() to retrieve data from MySQL, PostgreSQL, SQLite database.

First understand what is the use of fetchall, fetchmany(), fetchone().

cursor.fetchall() fetches all the rows of a query result. It returns all the rows as a list of tuples. An empty list is returned if there is no record to fetch.

cursor.fetchmany(size) returns the number of rows specified by size argument. When called repeatedly, this method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list.

cursor.fetchone() method returns a single record or None if no more rows are available.

Fetch all rows from database table using cursor’s fetchall()

Now, let see how to use fetchall to fetch all the records. To fetch all rows from a database table, you need to follow these simple steps: –

• Create a database Connection from Python. Refer Python SQLite connection, Python MySQL connection, Python PostgreSQL connection.
• Define the SELECT query. Here you need to know the table and its column details.
• Execute the SELECT query using the cursor.execute() method.
• Get resultSet (all rows) from the cursor object using a cursor.fetchall().
• Iterate over the ResultSet using for loop and get column values of each row.
• Close the Python database connection.
• Catch any SQL exceptions that may come up during the process.

Let try to fetch all rows from the table.

import sqlite3

def getAllRows():
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to SQLite")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchall()
        print("Total rows are:  ", len(records))
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

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

getAllRows()

Output:

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  emma@pynative.com
Salary:  12000.0


Id:  2
Name:  Scott
Email:  scott@pynative.com
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  jessa@pynative.com
Salary:  18000.0


Id:  4
Name:  Mike
Email:  mike@pynative.com
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  ricky@pynative.com
Salary:  19000.0


The Sqlite connection is closed

Also read:

• Fetch all rows from the MySQL table
• Fetch all rows from the PostgreSQL table

Retrieve a few rows from a table using cursor.fetchmany(size)

One thing I like about Python DB API is the flexibility. In the real world, fetching all the rows at once may not be feasible. So Python DB API solves this problem by providing different versions of the fetch function of the Cursor class. The most commonly used version is the cursor.fetchmany(size).

The syntax of the cursor’s fetchmany()

rows = cursor.fetchmany([size=cursor.arraysize])

• Here size is the number of rows to be retrieved. This method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list.
• Cursor’s fetchmany() method returns the number of rows specified by size argument. the default value is 1. If the specified size is 100, then it returns 100 rows.

Let try to fetch 3 rows from table using a cursor.fetchmany(size)

import sqlite3

def getlimitedRows(size):
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchmany(size)
        print("Fetching Total ", size," rows")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

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

getlimitedRows(3)

Output:

Connected to database
Fetching Total  3  rows
Printing each row

Id:  1
Name:  Emma
Email:  emma@pynative.com
Salary:  12000.0

Id:  2
Name:  Scott
Email:  scott@pynative.com
Salary:  22000.0

Id:  3
Name:  Jessa
Email:  jessa@pynative.com
Salary:  18000.0

The Sqlite connection is closed

Also read:

• Fetch limited rows from the MySQL table
• Fetch limited rows from the PostgreSQL table

What will happen if the cursor’s fetchmany(size) called repeatedly

What will happen if we called cursor.fetchmany(size) repeatedly after executing a SQL query.

For example, we ran a query, and it returned a query result of 10 rows. Next, we fetched the first two rows using cursor.fetchmany(2). Again, we called the cursor.fetchmany(2), then it will return the next two rows. Let see the example to understand it better.

import sqlite3

def getlimitedRows(size):
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchmany(size)
        print("Fetching Total ", size, " rows")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        records = cursor.fetchmany(size)
        print("Fetching next 2 rows from table")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

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

getlimitedRows(2)

Output:

Connected to database
Fetching Total  2  rows
Printing each row

Id:  1
Name:  Emma
Email:  emma@pynative.com
Salary:  12000.0


Id:  2
Name:  Scott
Email:  scott@pynative.com
Salary:  22000.0


Fetching next 2 rows from a table

Printing each row
Id:  3
Name:  Jessa
Email:  jessa@pynative.com
Salary:  18000.0


Id:  4
Name:  Mike
Email:  mike@pynative.com
Salary:  13000.0


The Sqlite connection is closed

Retrieve a single row from a table using cursor.fetchone

• Python DB API allows us to fetch only a single row. To fetch a single row from a result set we can use cursor.fetchone(). This method returns a single tuple.
• It can return a none if no rows are available in the resultset. cursor.fetchone() increments the cursor position by one and return the next row.

Let see the example now.

import sqlite3

def getSingleRows():
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        print("Fetching single row")
        record = cursor.fetchone()
        print(record)

        print("Fetching next row")
        record = cursor.fetchone()
        print(record)

        cursor.close()

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

getSingleRows()

Output:

Connected to database

Fetching single row
(1, 'Emma', 'emma@pynative.com', 12000.0)

Fetching next row
(2, 'Scott', 'scott@pynative.com', 22000.0)
The Sqlite connection is closed

Next Steps:

To practice what you learned in this article, Solve a Python SQLite Exercise project to practice database operations.