fetchall() in Python

We will demonstrate Python’s cursor class method fetchall() and how to use it in applications to retrieve data from databases in Python.

Use fetchall() to Retrieve Data From Databases in Python

Most of the time, we work with the database in our applications. The database is the most important part of our application that stores data.

Python uses the cursor to retrieve data from the database. The fetchall() is one of Python’s cursor methods used to retrieve all the rows for a certain query.

When we want to display all data from a certain table, we can use the fetchall() method to fetch all the rows. This method returns a list of tuples.

If the query has no rows, it will return an empty list. Let’s go through an example, create an example table, and try to fetch data using Python’s cursor methods.

For this example, we will use MySQL database, so we have to install the mysql-connector-python module. But if you want to work with other databases such as PostgreSQL, you need to use Psycopg2, or if you are working in SQLite, you need to use sqlite3.

So we will install mysql-connector-python by running the following command.

pip install mysql-connector-python

Let’s create a new example database and a table inside MySQL.

Our table structure will be as shown below.

Now, let’s add some demo data inside it, as shown below.

We will import mysql-connector-python and create a database connection inside a function in Python, as shown below.

# python
import mysql.connector


def getRecords():
    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="yourusername",
            password="yourpassword"
        )
        mycursor = mydb.cursor()
        print("Connected to MySQL")

We will create a select query to get the data from our table.

# python
query = "SELECT * FROM test"
mydb.commit()
records = mycursor.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("Country: ", row[3])
    print("\n")
mycursor.close()

And in the end, we will call our function.

# python
getRecords()

Output:

The above result shows that using the fetchall() method can easily fetch all the rows from the query.