Python db-api: fetchone vs fetchmany vs . fetchall

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

我今天刚刚和一些同事讨论了python的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?

我确定每个用例都依赖于我正在使用的db-api的实现，但是总的来说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

2 个解决方案

#1

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.

我认为这确实取决于实现，但是您可以通过查看MySQLdb源了解不同之处。根据选项的不同，mysqldb fetch*将当前的行集保存在内存或服务器端，因此fetchmany vs fetchone在这里有一些灵活性，可以知道在(python的)内存中保留什么，以及在db服务器端保留什么。

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.

PEP 249没有提供太多细节，所以我猜这是为了根据数据库进行优化，而确切的语义是由实现定义的。

#2

These are implementation specific.

这些是特定于实现的。

fetchall
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.

将从表中获得所有结果。当表的大小很小的时候，这将会更好。如果表的大小较大，在这些情况下fetchall将失败。

Will use most of the memory.

将使用大部分内存。

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

如果查询是在网络上进行的，将会引起一些问题。

fetchmany
fetchmany

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

fetchmany只会得到所需的结果数量。您可以生成结果和过程。fetchmany实现的简单片段。

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

#1