Querying Data from a Database using fetchone() and fetchall()

The fetchone() and fetchall() are the methods of Python MySQL connector and they are used to display data. This connector helps in enabling the Python programs to use MySQL databases. In the below code, we have used MySQL using Python for writing all the queries and fetching all the data from the databases.

1. Fetchone(): Fetchone() method is used when there is a need to retrieve only the first row from the table. The method only returns the first row from the defined table. The method is also used when we want to use the cursor() method for the iteration. This method increments the position of the cursor by 1 and after which it returns the next row.

This method cannot be used for the cursor object rather we run a query using a SQL statement i.e, “SELECT *” that fetches the first row/tuple from the table. After that, we use fetchone() method on the result variable returned by the “SELECT *” statement. Now the method fetches the first row from that result.

Syntax:

row = cursor.fetchone()

Steps for using fetchone() in Mysql using Python:

• First. import MySQL connector
• Now, create a connection with the MySQL connector using connect() method
• Next, create a cursor object with the cursor() method
• Now create and execute the query using “SELECT *” statement with execute() method to retrieve the data
• Use fetchone() method on the result variable.
• print the result

Example:

Suppose, there’s a table named “CUSTOMERS” and want to retrieve only the first row from it so, we have to run the below code.

Python

# Program to display the data
import mysql.connector

mydb = mysql.connector.connect(
  host = "localhost",
  user = "yourusername",
  password = "yourpass",
  database = "yourdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM CUSTOMERS")

result = mycursor.fetchone()

print(result)

Output:

2. Fetchall(): Fetchall() is a method that fetches all the remaining tuples from the last executed statement from a table (returns a list of tuples). The method only returns the first row from the defined table and If there are no tuples then it returns an empty list in the output.

This method cannot be used for the cursor object rather we run a query using a SQL statement i.e, “SELECT *” that fetches all the rows/tuples from the table. After that, we use fetchall() method on the result variable returned by the “SELECT *” statement.

Syntax:

row = cursor.fetchall()

Steps for using fetchall() in Mysql using Python:

• First. import MySQL connector
• Now, create a connection with the MySQL connector using connect() method
• Next, create a cursor object with the cursor() method
• Now create and execute the query using “SELECT *” statement with execute() method to retrieve the data
• Use fetchall() method on the result variable.
• print the result using for each loop to display all

Example:

Suppose, there’s a table named “CUSTOMERS” and want to retrieve all rows from it so, we have to run the below code.

Python

# Program to display the data
import mysql.connector

mydb = mysql.connector.connect(
  host = "localhost",
  user = "yourusername",
  password = "yourpass",
  database = "yourdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM CUSTOMERS")

# This SQL statement selects all data from the CUSTOMER table.
result = mycursor.fetchall()

# Printing all records or rows from the table.
# It returns a result set.
for all in result:
  print(all)

Output: