在SQLite中导航游标行(例如，我们可以回退/重置游标，例如回到第一行吗?) [英] Navigating cursor rows in SQLite (Can we rewind/reset the cursor i.e. go back to first row for example?)

问题描述

我试图了解当顺序处理游标行时以下内置函数是如何工作的.这些描述来自Python 3.1手册(使用SQLite3)

I am trying to understand how the following builtin functions work when sequentially processing cursor rows. The descriptions come from the Python 3.1 manual (using SQLite3)

Cursor.fetchone()

获取查询结果集的下一行，返回单个序列.

Fetches the next row of a query result set, returning a single sequence.

Cursor.fetchmany()

获取查询结果的下一组行，并返回一个列表.

Fetches the next set of rows of a query result, returning a list.

Cursor.fetchall()

获取查询结果的所有(剩余)行，并返回列表.

Fetches all (remaining) rows of a query result, returning a list.

因此，如果我有一个循环，其中我使用cursor.fetchone()一次处理一行，并且稍后的代码要求我返回第一行，或使用fetchall()提取所有行，该怎么办是吗?

So if I have a loop in which I am processing one row at a time using cursor.fetchone(), and some later code requires that I return to the first row, or fetch all rows using fetchall(), how do I do it?

这个概念对我来说有点奇怪，特别是来自Foxpro背景，它具有记录指针的概念，可以将记录指针移到光标的第一行或最后一行(转到顶部/底部)，或者转到第n行(转到n)

The concept is a bit strange to me, especially coming from a Foxpro background which has the concept of a record pointer which can be moved to the 1st or last row in a cursor (go top/bottom), or go to the nth row (go n)

任何帮助将不胜感激.

艾伦

推荐答案

Python 3.1中的SQLite接口基于 PEP 249 ，它仅指定游标必须支持对查询结果记录的顺序访问.没有回头路了.如果您需要返回到先前获取的行，则应在首次获取该行时将其保存，例如创建获取数据的列表(或者实际上，只需使用fetchall).然后，您可以使用列表，并根据需要在行之间来回移动.

The SQLite interface in Python 3.1 is based on PEP 249, which only specifies that cursors have to support sequential access to the records of a query result. There's no way to go back. If you need to return to a previously fetched row, you should save it when you first fetch it, e.g. create a list of the fetched data (or actually, just use fetchall). Then you can work with the list and go back and forth between rows as much as you want.

DB API设计背后的想法是支持代码的高效执行，您只需要处理每一行一次.一个典型的循环如下所示:

The idea behind the design of the DB API is to support efficient execution of code where you only need to process each row once. A typical loop looks like this:

for a,b,c in cursor.fetchone():
    # process it

这样，一旦循环的相应迭代完成，游标就可以丢弃对每一行数据的引用.它实际上并不会丢失任何内容，因为如果您想保留所有数据，则始终可以列出其中的一个列表，但是处理大型数据集的应用程序仍然可以一次处理一个行.这有点像在需要逐个处理可迭代元素时使用生成器表达式而不是列表的原因.

This way, the cursor can discard the references to each row's data once the corresponding iteration of the loop is finished. It doesn't really lose anything because if you want to keep all the data, you can always make a list out of it, but applications dealing with large data sets still have the ability to process rows one at a time. It's kind of like the reasoning behind using generator expressions rather than lists when you need to process elements of an iterable one by one.

这篇关于在SQLite中导航游标行(例如，我们可以回退/重置游标，例如回到第一行吗?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！