85

I just had a discussion today with some coworkers about python's db-api fetchone vs fetchmany vs fetchall.

I'm sure the use case for each of these is dependent on the implementation of the db-api that I'm using, but in general what are the use cases for fetchone vs fetchmany vs fetchall?

In other words are the following equivalent? or is there one of these that is preferred over the others? and if so in which situations?

cursor.execute("SELECT id, name FROM `table`")
for i in xrange(cursor.rowcount):
    id, name = cursor.fetchone()
    print id, name


cursor.execute("SELECT id, name FROM `table`")
result = cursor.fetchmany()
while result:
    for id, name in result:
        print id, name
    result = cursor.fetchmany()


cursor.execute("SELECT id, name FROM `table`")
for id, name in cursor.fetchall():
    print id, name
Alex Q
  • 3,080
  • 2
  • 27
  • 29

3 Answers3

23

As per official psycopg2 documentation

fetchone()

Fetch the next row of a query result set, returning a single tuple, or None when no more data is available:

>>> cur.execute("SELECT * FROM test WHERE id = %s", (3,))
>>> cur.fetchone()

(3, 42, 'bar')

A ProgrammingError is raised if the previous call to execute*() did not produce any result set or no call was issued yet.

fetchmany([size=cursor.arraysize])

Fetch the next set of rows of a query result, returning a list of tuples. An empty list is returned when no more rows are available.

The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor’s arraysize determines the number of rows to be fetched. The method should try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned:

>>> cur.execute("SELECT * FROM test;")
>>> cur.fetchmany(2)
[(1, 100, "abc'def"), (2, None, 'dada')]
>>> cur.fetchmany(2)
[(3, 42, 'bar')]
>>> cur.fetchmany(2)
[]

A ProgrammingError is raised if the previous call to execute*() did not produce any result set or no call was issued yet.

Note there are performance considerations involved with the size parameter. For optimal performance, it is usually best to use the arraysize attribute. If the size parameter is used, then it is best for it to retain the same value from one fetchmany() call to the next.

List item

fetchall()

Fetch all (remaining) rows of a query result, returning them as a list of tuples. An empty list is returned if there is no more record to fetch.

>>> cur.execute("SELECT * FROM test;")
>>> cur.fetchall()
[(1, 100, "abc'def"), (2, None, 'dada'), (3, 42, 'bar')]

A ProgrammingError is raised if the previous call to execute*() did not produce any result set or no call was issued yet.

Shiva
  • 2,627
  • 21
  • 33
shreesh katti
  • 759
  • 1
  • 9
  • 23
20

I think it indeed depends on the implementation, but you can get an idea of the differences by looking into MySQLdb sources. Depending on the options, mysqldb fetch* keep the current set of rows in memory or server side, so fetchmany vs fetchone has some flexibility here to know what to keep in (python's) memory and what to keep db server side.

PEP 249 does not give much detail, so I guess this is to optimize things depending on the database while exact semantics are implementation-defined.

David Cournapeau
  • 78,318
  • 8
  • 63
  • 70
  • 8
    I find differences in the amount of memory these use, with fetchall() using the most memory. – Marc Maxmeister Dec 03 '12 at 21:16
  • These three fetch commands had me scratching my head, too. What if the Python in question IS running on the SAME server as the mySQL server? – SMGreenfield Jun 02 '15 at 05:11
  • 1
    My confusion here stems from the fact that a SQL select query can have `LIMIT` and therefore what is the purpose of `fetchone` and `fetchmany`? In fact if you selected a query with no limit, you would get the entire table. If I still only use `fetchone` does that mean a huge amount of rows was transferred from server to client for no reason? In that case why would you ever use `fetchone` when using `LIMIT 1` is always far more efficient. – CMCDragonkai Aug 04 '20 at 05:11
12

These are implementation specific.

  • fetchall

Will get all the results from the table. This will work better when size of the table is small. If the table size is bigger, fetchall will fail in those cases.

Will use most of the memory.

Will cause some issues will can occur if the queries is done on network.

  • fetchmany

fetchmany will get only required number of results. You can yield the results and process. Simple Snippet of implementation of fetchmany.

   while True:
    results = cursor.fetchmany(arraysize)
    if not results:
        break
    for result in results:
        yield result
Niranjan Sagar
  • 819
  • 1
  • 15
  • 17