PYnative

Python Programming

Home » Python » Databases » Python cursor’s fetchall, fetchmany(), fetchone() to read records from database table

Python cursor’s fetchall, fetchmany(), fetchone() to read records from database table

Updated on:  | 23 Comments

This article demonstrates the use of Python’s cursor class methods fetchall(), fetchmany(), and fetchone() to retrieve rows from a database table. This article applies to all the relational databases, for example, SQLite, MySQL, PostgreSQL.

Also Read:

Python Database API Specification v2.0 (PEP 249) has been designed to encourage and maintain similarity between the Python modules used to access databases. So it doesn’t matter which database you use. Be it MySQL, PostgreSQL, and SQLite syntax, the syntax of functions and how to access the relational database are the same in all database modules.

We generally use the following Python module to work with Databases.

DatabaseModule
MySQLMySQL Connector Python
PostgreSQLPsycopg2
SQLitesqlite3

Above all modules adhere to Python Database API Specification v2.0 (PEP 249).

This lesson will show how to use fetchall(), fetchmany(), and fetchone() to retrieve data from MySQL, PostgreSQL, SQLite database.

First understand what is the use of fetchall, fetchmany(), fetchone().

cursor.fetchall() fetches all the rows of a query result. It returns all the rows as a list of tuples. An empty list is returned if there is no record to fetch.

cursor.fetchmany(size) returns the number of rows specified by size argument. When called repeatedly, this method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list.

cursor.fetchone() method returns a single record or None if no more rows are available.

I have created a database_developers table in my database. Now, it contains five rows. let see how to use fetchall to fetch all the records.

Let see the examples now.

sqlitedb_developers table with data
sqlitedb_developers table with data

Table of contents

Fetch all rows from database table using cursor’s fetchall()

Now, let see how to use fetchall to fetch all the records. To fetch all rows from a database table, you need to follow these simple steps: –

  • Create a database Connection from Python. Refer Python SQLite connection, Python MySQL connection, Python PostgreSQL connection.
  • Define the SELECT query. Here you need to know the table and its column details.
  • Execute the SELECT query using the cursor.execute() method.
  • Get resultSet (all rows) from the cursor object using a cursor.fetchall().
  • Iterate over the ResultSet using for loop and get column values of each row.
  • Close the Python database connection.
  • Catch any SQL exceptions that may come up during the process.

Let try to fetch all rows from the table.

import sqlite3

def getAllRows():
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to SQLite")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchall()
        print("Total rows are:  ", len(records))
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if connection:
            connection.close()
            print("The Sqlite connection is closed")

getAllRows()
Code language: Python (python)

Output:

Connected to database
Total rows are:   5

Printing each row
Id:  1
Name:  Emma
Email:  emma@pynative.com
Salary:  12000.0


Id:  2
Name:  Scott
Email:  scott@pynative.com
Salary:  22000.0


Id:  3
Name:  Jessa
Email:  jessa@pynative.com
Salary:  18000.0


Id:  4
Name:  Mike
Email:  mike@pynative.com
Salary:  13000.0


Id:  5
Name:  Ricky
Email:  ricky@pynative.com
Salary:  19000.0


The Sqlite connection is closed

Also read:

Retrieve a few rows from a table using cursor.fetchmany(size)

One thing I like about Python DB API is the flexibility. In the real world, fetching all the rows at once may not be feasible. So Python DB API solves this problem by providing different versions of the fetch function of the Cursor class. The most commonly used version is the cursor.fetchmany(size).

The syntax of the cursor’s fetchmany()

rows = cursor.fetchmany([size=cursor.arraysize])Code language: Python (python)
  • Here size is the number of rows to be retrieved. This method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list.
  • Cursor’s fetchmany() method returns the number of rows specified by size argument. the default value is 1. If the specified size is 100, then it returns 100 rows.

Let try to fetch 3 rows from table using a cursor.fetchmany(size)

import sqlite3

def getlimitedRows(size):
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchmany(size)
        print("Fetching Total ", size," rows")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if (connection):
            connection.close()
            print("The Sqlite connection is closed")

getlimitedRows(3)Code language: Python (python)

Output:

Connected to database
Fetching Total  3  rows
Printing each row

Id:  1
Name:  Emma
Email:  emma@pynative.com
Salary:  12000.0

Id:  2
Name:  Scott
Email:  scott@pynative.com
Salary:  22000.0

Id:  3
Name:  Jessa
Email:  jessa@pynative.com
Salary:  18000.0

The Sqlite connection is closed

Also read:

Note:

  • fetchmany() returns an empty list when no more rows are available in the table.
  • A ProgrammingError raised if the previous call to execute*() did not produce any result set or no call issued yet.
  • fetchmany() returns fewer rows if the table contains the fewer number of rows specified by the SIZE argument.

What will happen if the cursor’s fetchmany(size) called repeatedly

What will happen if we called cursor.fetchmany(size) repeatedly after executing a SQL query.

For example, we ran a query, and it returned a query result of 10 rows. Next, we fetched the first two rows using cursor.fetchmany(2). Again, we called the cursor.fetchmany(2), then it will return the next two rows. Let see the example to understand it better.

import sqlite3

def getlimitedRows(size):
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchmany(size)
        print("Fetching Total ", size, " rows")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        records = cursor.fetchmany(size)
        print("Fetching next 2 rows from table")
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("Salary: ", row[3])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if connection:
            connection.close()
            print("The Sqlite connection is closed")

getlimitedRows(2)
Code language: Python (python)

Output:

Connected to database
Fetching Total  2  rows
Printing each row

Id:  1
Name:  Emma
Email:  emma@pynative.com
Salary:  12000.0


Id:  2
Name:  Scott
Email:  scott@pynative.com
Salary:  22000.0


Fetching next 2 rows from a table

Printing each row
Id:  3
Name:  Jessa
Email:  jessa@pynative.com
Salary:  18000.0


Id:  4
Name:  Mike
Email:  mike@pynative.com
Salary:  13000.0


The Sqlite connection is closed

Retrieve a single row from a table using cursor.fetchone

  • Python DB API allows us to fetch only a single row. To fetch a single row from a result set we can use cursor.fetchone(). This method returns a single tuple.
  • It can return a none if no rows are available in the resultset. cursor.fetchone() increments the cursor position by one and return the next row.

Let see the example now.

import sqlite3

def getSingleRows():
    try:
        connection = sqlite3.connect('SQLite_Python.db')
        cursor = connection.cursor()
        print("Connected to database")

        sqlite_select_query = """SELECT * from database_developers"""
        cursor.execute(sqlite_select_query)
        print("Fetching single row")
        record = cursor.fetchone()
        print(record)

        print("Fetching next row")
        record = cursor.fetchone()
        print(record)

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from table", error)
    finally:
        if connection:
            connection.close()
            print("The Sqlite connection is closed")

getSingleRows()
Code language: Python (python)

Output:

Connected to database

Fetching single row
(1, 'Emma', 'emma@pynative.com', 12000.0)

Fetching next row
(2, 'Scott', 'scott@pynative.com', 22000.0)
The Sqlite connection is closed

Next Steps:

To practice what you learned in this article, Solve a Python SQLite Exercise project to practice database operations.

TweetF  sharein  shareP  Pin

About Vishal

I’m Vishal Hule, the Founder of PYnative.com. As a Python developer, I enjoy assisting students, developers, and learners. Follow me on Twitter.

Related Tutorial Topics:

Python Python Databases

All Coding Exercises:

C Exercises
C++ Exercises
Python Exercises

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 25+ questions
  • Each Quiz contains 25 MCQ
Exercises
Quizzes

Comments

  1. Joe McTigue says

    I’m trying to reteive the number of instances of a column value in a table in a MySQL database from a Python Flask Rest API like the following:

    data = request.get_json()
    exam_id = data.get(‘exam_id’)

    sql = ‘SELECT count(ExamID) AS NumberOfQuestions FROM exams WHERE ExamID={}’.format(exam_id)
    cursor.execute(sql)
    NumberOfQuestions = cursor.fetchone()
    result = [{‘NumberOfQuestion’: NumberOfQuestions}]
    return jsonify({result})

    but I get the following error:
    {
    “error”: “unhashable type: ‘list'”
    }

    But when I run this query in mysql “SELECT count(ExamID) FROM exams WHERE ExamID = 1;
    it returns 3 as expected.
    Could you offer any insight?

    Reply

  2. Jason says

    Quick question:
    Why do you wait to close the cursor and connection until after your loop here:

    connection = sqlite3.connect(‘SQLite_Python.db’)
    cursor = connection.cursor()
    print(“Connected to SQLite”)

    sqlite_select_query = “””SELECT * from database_developers”””
    cursor.execute(sqlite_select_query)
    records = cursor.fetchall()
    print(“Total rows are: “, len(records))
    print(“Printing each row”)
    for row in records:
    print(“Id: “, row[0])
    print(“Name: “, row[1])
    print(“Email: “, row[2])
    print(“Salary: “, row[3])
    print(“\n”)

    cursor.close()

    Can’t you close the cursor and the connection before looping and printing, since you put the results in a local object?

    Reply

  3. Ferhad Ceferli says

    Bilgi icin tesekkurler
    Bir soru : mesela ben input da ismi yazarak 1 kisinin bilgilerine ulasmak istiyorum o zaman nasil olucak?

    Reply

  6. jason Maiden says

    is there a way to say only show let’s say Jessa Name and Email instead of all her values

    Name:  Jessa
Email:  jessa@pynative.com
Salary:  18000.0
    Reply

  7. Joerie Judong says

    I have a question for you. I normally get the result back with
    Result = cursor.fetchone()[0]

    I know that for I can do a check statement with if Result == None (correct me if I am wrong here)

    But what if the column itself contains a null value? How do I check for that?

    If Result == Null?

    Reply

  8. Naeem says

    I am displaying one record performance. I tried to use the fetchone() and I am able to see the last record only. I cannot browse to other records. Can you help

    while True:
                my_data=c.fetchone()
                if my_data == None:
                    break
                else:
                    self.screen.ids.item1.text = my_data[1]
                    self.screen.ids.item2.text = my_data[9]
                    self.screen.ids.item3.text = my_data[2]
                    self.screen.ids.item4.text = my_data[3]
                    self.screen.ids.item5.text =  my_data[5]
            
        except c.DataError as e:
            print("DataError")
            print(e)
    Reply

  9. Eran says

    Thanks for the tutorial.
    There is a problem in the example.
    You performed select * from ….
    so according to the table structure you get 5 columns in the response. In the example you refer a 4 columns result_set.
    Is it a mistake?
    Is there an option to refer a column in the result_set by its name?

    Reply

  10. Errol says

    how can we fetch multiple-line sql error messages thrown by failed stored procedures? i am only able to return first row of error messages

    Reply

  11. Mark says

    Hello Vishal,
    Thank you for this tutorial about fetching rows. It explains very well how to use it. My doubt is where the cursor store the result set when you perform a fetchall. Let’s say the query result os about 1000 rows. Python gets all those 1000 records and store them internally or somehow the cursor creates an index for the result set and presents the rows as you need them ? I am worried about performance with large data being transferred. Thanks a lot.

    Reply

  13. Xijet13 says

    Good morning/afternoon/night, I have a problem with fetchall (), it does not receive any data, it prints ‘None’, ‘NoneType’ or “No result set to fetch form”. My queries are of the type like “SELECT name FROM Clients”.
    A month ago it was working properly.
    Please, I need a solution as soon as possible.

    Reply

  15. Srini says

    Hi Vishal, i have a challenge, my query extract has approximately 500K rows and cursor fetchall() is failing and no output is coming out kind of stuck, how do i overcome this. I need this data and i’m extracting to Excel and need to do some analytics/graphs

    Reply

    • Vishal says

      Hey Srini, You can use fetchmany() to fetch fewer rows. maybe you are facing timeout error as the query is taking time to return result

      Reply

    • Antonio Misericordia says

      you have to read a row and access next, in this simple way:

      import sqlite3

# Create a SQL connection to our SQLite database
con = sqlite3.connect("data/portal_mammals.sqlite")

cur = con.cursor()

# The result of a "cursor.execute" can be iterated over by row
for row in cur.execute('SELECT * FROM species;'):
    print(row)

# Be sure to close the connection
con.close()

      And now the bottle-neck is your hard disk, not your RAM! 😉

      Reply

  16. Sebastian says

    Hi! Great info.. just a question. If record = cursor.fetchone() returns no row, I get the error “TypeError: cannot unpack non-iterable NoneType object”. I tried validating it with ‘ if cursor:’ however it does not work. How would you check if the fecthone() returns a row, in order to print the row or a message “no info available”? Thanks!

    Reply

    • Vishal says

      The cursor.fetchone() method to retrieve the next row of a query result set.
      This method returns a single record or None if no more rows are available. Just simply do this.

      row = cursor.fetchone()
if row is not None:
    # print record
      Reply

Leave a Reply

your email address will NOT be published. all comments are moderated according to our comment policy.

Use <pre> tag for posting code. E.g. <pre> Your entire code </pre>

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

Explore Python

Coding Exercises

Legal Stuff

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our:

Copyright © 2018–2026 pynative.com