Difference between fetchone and fetchall in Python

Hey!, navigating through database stuff? Let’s chat about the differences between using fetchone() and fetchall() methods when pulling data from a database. This quick guide’s here to help you out!

fetchone() fetches one row of data from the database and returns it as a tuple, while fetchall() fetches all remaining rows of data and returns them as a list of tuples. Use fetchone() when processing large result sets, and fetchall() when working with small result sets or when all the data is needed at once.

Introduction

Python is a popular programming language for working with databases. It has a large number of database modules that can be used to interact with different types of databases, such as SQLite, MySQL, PostgreSQL, and Oracle.

One of the common tasks when working with databases is to retrieve data from a table. In Python, we can use the fetchone() and fetchall() methods provided by the DB-API to retrieve rows from a database cursor after executing a SQL query.

In this blog post, we will discuss the difference between these two methods.

Python Database API (DB-API)

The Python DB-API is an interface for connecting to databases from Python. It defines a set of methods, constants, and exceptions that are used by Python modules to interact with databases.

All Python modules that implement the DB-API should provide these methods, constants, and exceptions. This makes it easy to switch between different database modules without having to change the code that interacts with the database.

The fetchone() method

The fetchone() method retrieves the next row of a query result set and returns it as a tuple or None if there are no more rows.

cursor.fetchone()

Example

Here is an example of using the fetchone() method to retrieve rows from a MariaDB database:

For MariaDB, Need to install the MariaDB module to proceed further, I am using Python 3 for this example.

# pip3 install mariadb

Collecting mariadb

  Downloading https://files.pythonhosted.org/packages/37/0a/ce5724f852f2937c6955bcea09659aa2d85d487df1c9de6711344b71527d/mariadb-1.0.11.zip (85kB)

    100% |################################| 92kB 21.7MB/s 

Installing collected packages: mariadb

  Running setup.py install for mariadb ... done

Successfully installed mariadb-1.0.11

# cat py_mariadb_fetchone.py 

import mariadb
import sys

# establish connection to MariaDB server
mariadb.connect(host='Hostname',user='root',password='password',database='Db_name')

# create cursor object
cursor = connection.cursor()

# execute SELECT statement to retrieve data from a table
cursor.execute("select * from TBLS")

# fetch one row at a time using fetchone()
row = cursor.fetchone()
while row is not None:
    print(row)
    row = cursor.fetchone()

# close cursor and connection
cursor.close()
connection.close()

In this above example, We are creating a connection with MariaDB with the connection details and executing a select query. Using fetchone() we are retrieving one row at a time and printing it in the console. As you can see, We have a while loop “while row is not None:” where it will print records one by one, and if there is no record fetchone will give us None which will stop the While loop

Output:

# python3 py_mariadb_fetchone.py 

(1, 2678232971, 2, 0, 'test', 'USER', 0, 1, 'tbl1', 'EXTERNAL_TABLE', None, None, b'\x00', 0)

(2, 3678142972, 2, 0, 'test', 'USER', 0, 2, 'tbl2', 'EXTERNAL_TABLE', None, None, b'\x00', 0)

(3, 4678439972, 2, 0, 'test', 'USER', 0, 3, 'tbl3', 'EXTERNAL_TABLE', None, None, b'\x00', 0)

(4, 6678123472, 2, 0, 'test', 'USER', 0, 4, 'tbl4', 'EXTERNAL_TABLE', None, None, b'\x00', 0)

The fetchall() method

The fetchall() method returns a list of tuples, each tuple representing a row from the query result set.

cursor.fetchall()

Example

Here is an example of using the fetchall() method to retrieve and process rows from a Mariadb database:

# cat py_mariadb.py 

import mariadb
import sys
# establish connection to MariaDB server
connection = mariadb.connect(host='Hostname',user='root',password='password',database='Db_name')

# create cursor object
cursor = connection.cursor()

# execute SELECT statement to retrieve data from a table
cursor.execute("select * from TBLS")


# fetch all rows at once using fetchall()
rows = cursor.fetchall()
for row in rows:
    print(row)

# close cursor and connection
cursor.close()
connection.close()

In the above example, We are using fetchall, Which will return all the rows at a time and we are publish it in the console line by line using for loop

Output:

# python3 py_mariadb_fetchall.py 

(1, 2678232971, 2, 0, 'test', 'USER', 0, 1, 'tbl1', 'EXTERNAL_TABLE', None, None, b'\x00', 0)

(2, 3678142972, 2, 0, 'test', 'USER', 0, 2, 'tbl2', 'EXTERNAL_TABLE', None, None, b'\x00', 0)

(3, 4678439972, 2, 0, 'test', 'USER', 0, 3, 'tbl3', 'EXTERNAL_TABLE', None, None, b'\x00', 0)

(4, 6678123472, 2, 0, 'test', 'USER', 0, 4, 'tbl4', 'EXTERNAL_TABLE', None, None, b'\x00', 0)

You will not see any difference in the output for fetchone and fetchall as it only depends on how it is fetching the results

Comparison between fetchone() and fetchall()

The choice between using fetchone() and fetchall() depends on the requirements of your program. Here are some factors to consider when choosing between these two methods:

Memory usage

The fetchall() method retrieves all the rows of the query result set and stores them in memory as a list of tuples. This can be memory-intensive if the result set is large.

On the other hand, the fetchone() method

retrieves only one row at a time, so it is more memory-efficient. However, if you need to process all the rows at once, you will have to use a loop and call fetchone() repeatedly.

Performance

The fetchall() method can be faster than fetchone() when the result set is small. This is because the fetchone() method requires multiple calls to retrieve all the rows, whereas the fetchall() method retrieves all the rows in a single call.

However, if the result set is large, the fetchall() method can be slower because it retrieves all the rows at once and stores them in memory.

On the other hand, the fetchone() method can be faster than fetchall() when processing large result sets, because it retrieves and processes one row at a time. This can be more efficient in terms of both memory usage and processing time.

Mysql Example: To connect mysql from Python

Before starting, Install mysql connector below

pip install mysql-connector-python

Once done, You can able to import the mysql connector, Which will help you to connect mysql db

fetchone()

import mysql.connector

# establish connection to MySQL server
connection = mysql.connector.connect(
    host='mysq_hostname',
    user='test',
    password='password',
    database='mysql_db'
)

# create cursor object
cursor = connection.cursor()

# execute SELECT statement to retrieve data from a table
cursor.execute("SELECT * FROM your_table")

# fetch one row at a time using fetchone()
row = cursor.fetchone()
while row is not None:
    print(row)
    row = cursor.fetchone()

# close cursor and connection
cursor.close()
connection.close()

fetchall()

import mysql.connector

# establish connection to MySQL server
connection = mysql.connector.connect(
    host='mysq_hostname',
    user='test',
    password='password',
    database='mysql_db'
)

# create cursor object
cursor = connection.cursor()

# execute SELECT statement to retrieve data from a table
cursor.execute("SELECT * FROM your_table")

# fetch all rows at once using fetchall()
rows = cursor.fetchall()
for row in rows:
    print(row)

# close cursor and connection
cursor.close()
connection.close()

You can test this in your LAB and let us know if you are facing any issues

Conclusion

In this blog post, we discussed the difference between the fetchone() and fetchall() methods in Python’s DB-API. We learned that the fetchone() method retrieves one row at a time, while the fetchall() method retrieves all the rows at once with multiple examples by connecting MariaDB, and Mysql. We also discussed the factors to consider when choosing between these two methods, such as memory usage and performance.

Both these methods have their own pros and cons, If you need to process all the rows at once and the result set is small, fetchall() might be the better option. However, if you need to process a large result set, or if memory usage is a concern, fetchone() might be the better option.

Good Luck with your Learning !!

How to Calculate SHA256 Hash of a File in Python

AES 256 Encryption and Decryption in Python

How to Calculate MD5 Hash of a File in Python

About
Latest Posts

Follow me

Jerry Richard R

Cloud Premier Engineer at Cloudera

Jerry is a Python developer with over a decade of experience in the IT industry, backed by a strong foundation with a Bachelor's in Computer Engineering. Leveraging his expertise, he created codingspell.com, a resourceful platform where he shares hard-earned knowledge and common pitfalls to avoid in Python development. Apart from being a reliable guide for burgeoning developers through his website, Jerry embodies a balance between work and family, cherishing his time off-screen with loved ones. His journey, which began in a small town with a keen interest in video games, has blossomed into a fulfilling career guiding others in the Python development landscape.

Follow me

Latest posts by Jerry Richard R (see all)