fetchall() in Python

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.

# python
pip install mysql-connector-python

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

fetchall in python database name

Our table structure will be as shown below.

fetchall in python database screenshot

fetchall in python table structure

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

fetchall in python table data

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:

fetchall in python example result

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

Rana Hasnain Khan avatar Rana Hasnain Khan avatar

Rana is a computer science graduate passionate about helping people to build and diagnose scalable web application problems and problems developers face across the full-stack.

LinkedIn

Related Article - Python MySQL

  • Connect to MySQL Database Using Python
  • EnvironmentError: Mysql_config Not Found in Python