We will demonstrate Python’s
cursor class method
fetchall() and how to use it in applications to retrieve data from databases in Python.
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
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
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) print("Name: ", row) print("Email: ", row) print("Country: ", row) print("\n") mycursor.close()
And in the end, we will call our function.
# python getRecords()
The above result shows that using the
fetchall() method can easily fetch all the rows from the query.