How to use Python cursor’s fetchall, fetchmany(), fetchone() to read records from SQL

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

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

• MySQL — — — — MySQL Connector Pytho
• PostgreSQL — — — — Psycopg2
• SQLite — — — — sqlite3

Above all, interfaces or modules are adhere to Python Database API Specification v2.0 (PEP 249). In this article, I will show how to use fetchall, fetchmany(), fetchone() to retrieve data from MySQL, PostgreSQL, SQLite database.

Before proceeding further first understand what is the use of fetchall(), fetchmany(), fetchone().

cursor.fetchall() returns 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.

I have created a MySQL_Test table in my database. Now, it contains three rows. let see how to use fetchall to fetch all the records.

Let see the examples now.

Fetch all rows from the 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:

1. Create a database Connection from Python.
2. Define the SELECT query. Here you need to know the table, and it’s column details.
3. Execute the SELECT query using the cursor.execute() method.
4. Get resultSet (all rows) from the cursor object using a cursor.fetchall().