Summary: This tutorial shows you how to query data from a MySQL database in Python by using MySQL Connector/Python API such as fetchone() , fetchmany() , and fetchall() .
This tutorial picks up where the Connecting to a MySQL Database in Python left off.
To query data in a MySQL database from Python, you need to do the following steps:
- First, connect to the MySQL Database, you get a
MySQLConnectionobject. - Next, create a
MySQLCursorobject from theMySQLConnectionobject. - Then, use the cursor to execute a query by calling its
execute()method. - After that, use
fetchone(),fetchmany()orfetchall()method to fetch data from the result set. - Finally, close the cursor as well as the database connection by calling the
close()method of the corresponding objects.
We will show you how to use fetchone() , fetchmany() , and fetchall() methods in more detail in the following sections.
Querying data with fetchone() method
The fetchone() method returns the next row of a query result set or None in case there is no row left. Let’s take a look at the following code:
from mysql.connector import MySQLConnection, Error
from config import read_config
def query_with_fetchone(config):
# Initialize variables for cursor and connection
cursor = None
conn = None
try:
# Establish a connection to the MySQL database using the provided configuration
conn = MySQLConnection(**config)
# Create a cursor to interact with the database
cursor = conn.cursor()
# Execute a SELECT query to retrieve all rows from the 'books' table
cursor.execute("SELECT * FROM books")
# Fetch the first row
row = cursor.fetchone()
# Loop through all rows and print them
while row is not None:
print(row)
row = cursor.fetchone()
except Error as e:
# Print an error message if an error occurs during the execution of the query
print(e)
finally:
# Close the cursor and connection in the 'finally' block to ensure it happens
if cursor:
cursor.close()
if conn:
conn.close()
if __name__ == '__main__':
# Read the database configuration from the 'config' module
config = read_config()
# Call the function with the obtained configuration to execute the query
query_with_fetchone(config)
Code language: Python (python)How it works:
- First, connect to the database by creating a new
MySQLConnectionobject - Next, instantiate a new
MySQLCursorobject from theMySQLConnectionobject - Then, execute a query that selects all rows from the
bookstable. - After that, fetch the next row in the result set by calling the
fetchone(). In thewhileloop block, display the contents of the row, and move to the next row until all rows are fetched. - Finally, close both the cursor and connection objects by invoking the
close()method of the corresponding object.
Notice that we used the function read_config() from the config.py module created in the connecting to MySQL database tutorial.
Querying data with fetchall() method
If the number of rows in the table is relatively small, you can use the fetchall() method to fetch all rows from the table. For example:
from mysql.connector import MySQLConnection, Error
from config import read_config
def query_with_fetchall(config):
try:
# Establish a connection to the MySQL database using the provided configuration
conn = MySQLConnection(**config)
# Create a cursor to interact with the database
cursor = conn.cursor()
# Execute a SELECT query to retrieve all rows from the 'books' table
cursor.execute("SELECT * FROM books")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the total number of rows returned by the query
print('Total Row(s):', cursor.rowcount)
# Loop through all rows and print them
for row in rows:
print(row)
except Error as e:
# Print an error message if an error occurs during the execution of the query
print(e)
finally:
# Close the cursor and connection in the 'finally' block to ensure it happens
cursor.close()
conn.close()
if __name__ == '__main__':
# Read the database configuration from the 'config' module
config = read_config()
# Call the function with the obtained configuration to execute the query
query_with_fetchall(config)
Code language: Python (python)The logic is similar to the example with the fetchone() method except for the fetchall() method call part.
Because we fetched all rows from the books table into the memory, we can get the total rows returned by using the rowcount property of the cursor object.
Querying data with fetchmany() method
For a relatively large table, fetching all rows and returning the entire result set can be time-consuming. Furthermore, the fetchall() method requires allocating sufficient memory to store the complete result set in memory, posing efficiency concerns.
MySQL Connector/Python has the fetchmany() method that returns the next number of rows (n) of the result set, which allows you to balance between retrieval time and memory space.
Here’s the program that uses the fetchmany() method to fetch all rows from the result set of the query:
from mysql.connector import MySQLConnection, Error
from config import read_config
def iter_row(cursor, size=10):
# Infinite loop to fetch rows in chunks of 'size' from the result set
while True:
rows = cursor.fetchmany(size)
# Break the loop if there are no more rows to fetch
if not rows:
break
# Yield each row in the fetched chunk
for row in rows:
yield row
def query_with_fetchmany(config):
# Initialize variables for connection and cursor
conn = None
cursor = None
try:
# Establish a connection to the MySQL database using the provided configuration
conn = MySQLConnection(**config)
# Create a cursor to interact with the database
cursor = conn.cursor()
# Execute a SELECT query to retrieve all rows from the 'books' table
cursor.execute("SELECT * FROM books")
# Iterate over rows using the custom iterator function 'iter_row'
for row in iter_row(cursor, 10):
print(row)
except Error as e:
# Print an error message if an error occurs during the execution of the query
print(e)
finally:
# Close the cursor and connection in the 'finally' block to ensure it happens
if cursor:
cursor.close()
if conn:
conn.close()
if __name__ =='__main__' :
# Read the database configuration from the 'config' module
config = read_config()
# Call the function with the obtained configuration to execute the query
query_with_fetchmany(config)
Code language: PHP (php)First, define a generator that chunks the database calls into a series of fetchmany() calls:
def iter_row(cursor, size=10):
# Infinite loop to fetch rows in chunks of 'size' from the result set
while True:
rows = cursor.fetchmany(size)
# Break the loop if there are no more rows to fetch
if not rows:
break
# Yield each row in the fetched chunk
for row in rows:
yield rowCode language: Python (python)Second, use the iter_row() generator to fetch 10 rows at a time :
def query_with_fetchmany(config):
# Initialize variables for connection and cursor
conn = None
cursor = None
try:
# Establish a connection to the MySQL database using the provided configuration
conn = MySQLConnection(**config)
# Create a cursor to interact with the database
cursor = conn.cursor()
# Execute a SELECT query to retrieve all rows from the 'books' table
cursor.execute("SELECT * FROM books")
# Iterate over rows using the custom iterator function 'iter_row'
for row in iter_row(cursor, 10):
print(row)
except Error as e:
# Print an error message if an error occurs during the execution of the query
print(e)
finally:
# Close the cursor and connection in the 'finally' block to ensure it happens
if cursor:
cursor.close()
if conn:
conn.close()Code language: Python (python)Third, call the query_with_fetchmany() function with the database configuration:
if __name__ =='__main__' :
# Read the database configuration from the 'config' module
config = read_config()
# Call the function with the obtained configuration to execute the query
query_with_fetchmany(config)Code language: PHP (php)Summary
- Use
fetchone()method to retrieve a single row from a result set. - Use
fetchmany()method to retrieve a specified number of rows from a result set. - Use
fetchall()method to retrieve all rows from a result set.
