Python DB-API:fetchone与fetchmany与fetchall [英] Python db-api: fetchone vs fetchmany vs fetchall

问题描述

我今天刚刚与一些同事讨论了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.

我确定每一个的用例都取决于我正在使用的db-api的实现，但是总体上fetchone，fetchmany，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

推荐答案

我认为这确实取决于实现，但是您可以通过研究MySQLdb源代码来了解差异.根据选项的不同，mysqldb fetch *将当前行集保留在内存或服务器端，因此fetchmany vs fetchone在此处具有一定的灵活性，可以知道要在(python)的内存中保留什么以及在数据库服务器端应保留什么.

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并没有提供太多细节，所以我想这是根据数据库来实现的，而确切的语义是由实现定义的.

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.

这篇关于Python DB-API:fetchone与fetchmany与fetchall的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！