Python fetchone, fetchall, and fetchmany: Fetching Records from MySQL

Interactive Google Colab Practice Notebook

Practice retrieving database records using fetchone(), fetchall(), fetchmany(), dictionary cursors, and SQLAlchemy result proxies interactively inside Google Colab.

View on GitHub

When executing a SELECT query in Python, MySQL returns a dataset proxy that can be consumed in multiple ways. Depending on memory constraints and application logic, you can retrieve data line-by-line with fetchone(), in fixed-size chunks with fetchmany(), or all at once with fetchall().

We defined my_conn in our database connection guide and rely on the sample student table.

Using fetchone() to Collect Next Available Record

The fetchone() method fetches the single next available row from the active result set. If no more rows are present, it returns None.

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")

my_result = my_cursor.fetchone() # Returns a single tuple

# Print full tuple
print(my_result)

# Print each column value on a new line
for x in my_result:
    print(x)

The returned row is structured as a tuple. We can verify its data type using type(my_result):

print(type(my_result)) # Output: <class 'tuple'>

Output for the first record in the student table:

(1, 'John Deo', 'Four', 75, 'female')
1
John Deo
Four
75
female

Accessing Specific Columns by Index

Because the result is a tuple, individual fields can be accessed via zero-based integer indices matching the table schema:

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")

my_result = my_cursor.fetchone()
print("Student id =", my_result[0])
print("Student Name =", my_result[1])
print("Student Class =", my_result[2])
print("Student Mark =", my_result[3])
print("Student Gender =", my_result[4])

Output:

Student id = 1
Student Name = John Deo
Student Class = Four
Student Mark = 75
Student Gender = female

Iterating Through Records with fetchone() in a While Loop

To process records line-by-line until the result set is exhausted, use a while loop checking for None:

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")
my_result = my_cursor.fetchone()
while my_result is not None:
    print(my_result)
    my_result = my_cursor.fetchone()

• Tutorial Video: MySQL SELECT Query in Python

• MySQL SELECT Query in Python using fetchall(), fetchmany(), fetchone(), and first() methods
  

Direct Cursor Iteration

Instead of manually invoking fetchone() inside a loop, Python cursors are iterators. You can loop directly over the cursor object:

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")
for my_result in my_cursor:
    print(my_result)

Using fetchall() to Retrieve All Records

The fetchall() method retrieves all remaining rows of a query result set as a list of tuples.

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")
my_result = my_cursor.fetchall()
for row in my_result:
    print(row)

Extracting Lists and Mapping Columns

You can transform fetched tuples into standalone Python lists for analytics or dropdown generation using list comprehensions:

q = "SELECT id, name FROM student WHERE class='Four'"
my_cursor.execute(q)
my_result = my_cursor.fetchall()

my_ids = [row[0] for row in my_result]   # List of IDs
my_names = [row[1] for row in my_result] # List of names

print(my_ids)
print(my_names)

Getting Column Headers / Metadata

To inspect active column names dynamically, use cursor metadata keys or column descriptions:

q = "SELECT id, name, class, mark, gender FROM student WHERE class='Four'"
my_cursor.execute(q)

# Fetch column headers from cursor description
columns = [desc[0] for desc in my_cursor.description]
print(columns) # Output: ['id', 'name', 'class', 'mark', 'gender']

Using fetchmany() for Chunked Data Processing

The fetchmany(size) method collects a specified number of rows. This is essential for processing large datasets in memory-efficient batches.

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")
my_result_top = my_cursor.fetchmany(size=3)

for row in my_result_top:
    print(row)

Output:

(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')

Combining fetchmany() and fetchall()

When you call fetchmany() followed by fetchall(), the cursor resumes reading from where the previous fetch stopped:

my_cursor = my_conn.cursor()

my_cursor.execute("SELECT * FROM student")

# Fetch first 3 records
my_result_top = my_cursor.fetchmany(size=3)
print("--- Top 3 Records ---")
for row in my_result_top:
    print(row)

# Fetch remaining records from record #4 onward
my_result = my_cursor.fetchall()
print("--- Remaining Records ---")
for row in my_result:
    print(row)

Memory Insight: Notice that we do not re-execute the SQL statement. After execution, rows are stored in client buffer memory, and calls to fetchmany() or fetchall() simply consume records sequentially from that buffer.

SQL-Level Filtering vs Client-Side Fetching

Filtering rows in Python after running a full SELECT * FROM table statement wastes network bandwidth and memory. Always use SQL clauses (LIMIT, WHERE) to restrict data at the database level before fetching:

# Preferred: Database returns only 10 rows
my_cursor.execute("SELECT * FROM student LIMIT 0, 10")
my_result = my_cursor.fetchall()

Building Dictionaries from Result Sets (SQLAlchemy 2.0+)

To convert raw SQL result tuples into dictionary maps indexed by primary key or column names, use modern SQLAlchemy connection contexts:

from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://demo_user:demo_password@localhost/my_db")

query = "SELECT id, name, class, mark, gender FROM student LIMIT 0, 5"

my_dict = {}
my_list = []

with engine.connect() as conn:
    result = conn.execute(text(query))
    for row in result:
        # row is a Row object supporting tuple unpacking and key indexing
        my_dict[row.id] = row
        my_list.append(row.name)

print("Dictionary by ID:", my_dict)
print("Name List:", my_list)

# Look up record details for 'Arnold'
for student_id, student_data in my_dict.items():
    if student_data.name == "Arnold":
        print(f"Found Arnold: ID={student_id}, Class={student_data.class}, Gender={student_data.gender}")

Frequently Asked Questions & Common Errors

Related Tutorials & Next Steps

• Python MySQL Connection Guide

  Learn how to establish database connections using MySQL Connector, PyMySQL, and SQLAlchemy.

• Python MySQL cursor.rowcount

  Understand how row counts behave across SELECT statements, buffered cursors, and data mutation queries.

• Read MySQL Tables into Pandas DataFrames

  Use pd.read_sql() to read MySQL database tables directly into Pandas DataFrames for data science.

• Student Table SQL Dump File

  Download the complete student.sql database dump file containing sample records with the updated gender column schema.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: