Python db-api: fetchone vs fetchmany vs fetchall

Question

I recently had a discussion with some co-workers about python db-api fetchone vs fetchmany vs fetchall.

I am sure that the use case for each of them depends on the db-api implementation that I use, but in general, what are the use cases for fetchone vs fetchmany vs fetchall?

In other words, the next equivalent? or is there one of them that is preferable to others? and if so, in what 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 

Answer 1

I think it really depends on the implementation, but you can get an idea of ​​the differences by looking at MySQLdb sources. Depending on the settings, mysqldb fetch * stores the current set of lines in memory or on the server side, so fetchmany vs fetchone has some flexibility here to know what to store in memory (python) and what to support the db server.

PEP 249 does not give details, so I assume that it is to optimize things depending on the database, while the exact semantics are determined by the implementation.

Answer 2

It is implementation specific.

• fetchall

Get all the results from the table. This will work better if the table size is small. If the table is larger, fetchall will fail in these cases.

Will use most of the memory.

Some problems can be damaged if requests are executed on the network.

• fetchmany

fetchmany will only get the required number of results. You can give results and process. A simple snippet of fetchmany implementation.

  while True: results = cursor.fetchmany(arraysize) if not results: break for result in results: yield result