为什么查询SQLite数据库时，你需要创建一个光标？

I'm completely new to Python's sqlite3 module (and SQL in general for that matter), and this just completely stumps me. The abundant lack of descriptions of cursor objects (rather, their necessity) also seems odd.

我对Python的sqlite3模块（以及一般的SQL）完全不熟悉，这完全让我感到困惑。光标对象的大量缺乏描述（相反，它们的必要性）似乎也很奇怪。

This snippet of code is the preferred way of doing things:

这段代码是首选方式：

import sqlite3
conn = sqlite3.connect("db.sqlite")
c = conn.cursor()
c.execute('''insert into table "users" values ("Jack Bauer", "555-555-5555")''')
conn.commit()
c.close()

This one isn't, even though it works just as well and without the (seemingly pointless) cursor:

这个不是，即使它工作得很好，没有（看似毫无意义）光标：

import sqlite3
conn = sqlite3.connect("db.sqlite")
conn.execute('''insert into table "users" values ("Jack Bauer", "555-555-5555")''')
conn.commit()

Can anyone tell me why I need a cursor?
It just seems like pointless overhead. For every method in my script that accesses a database, I'm supposed to create and destroy a cursor?
Why not just use the connection object?

谁能告诉我为什么需要光标？这看起来似乎毫无意义。对于我的脚本中访问数据库的每个方法，我应该创建并销毁游标？为什么不直接使用连接对象？

4 个解决方案

#1

Just a misapplied abstraction it seems to me. A db cursor is an abstraction, meant for data set traversal.

只是误用的抽象，在我看来。一个DB光标是一种抽象，意味着数据集遍历。

From Wikipedia article on subject:

来自*关于主题的文章：

In computer science and technology, a database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator.

在计算机科学与技术，数据库光标是一个控制结构，使穿越了在数据库中的记录。游标促进结合随后的处理与遍历，诸如检索，添加和删除的数据库记录。遍历数据库光标特性使得光标类似于迭代器的编程语言的概念。

And:

和：

Cursors can not only be used to fetch data from the DBMS into an application but also to identify a row in a table to be updated or deleted. The SQL:2003 standard defines positioned update and positioned delete SQL statements for that purpose. Such statements do not use a regular WHERE clause with predicates. Instead, a cursor identifies the row. The cursor must be opened and already positioned on a row by means of FETCH statement.

游标不仅可用于将数据从DBMS提取到应用程序中，还可用于标识要更新或删除的表中的行。 SQL：2003标准为此目的定义了定位更新和定位删除SQL语句。此类语句不使用带谓词的常规WHERE子句。相反，游标标识该行。必须通过FETCH语句打开游标并将其定位在一行上。

If you check the docs on Python sqlite module, you can see that a python module cursor is needed even for a CREATE TABLE statement, so it's used for cases where a mere connection object should suffice - as correctly pointed out by the OP. Such abstraction is different from what people understand a db cursor to be and hence, the confusion/frustration on the part of users. Regardless of efficiency, it's just a conceptual overhead. Would be nice if it was pointed out in the docs that the python module cursor is bit different than what a cursor is in SQL and databases.

如果检查Python sqlite模块上的文档，您可以看到即使对于CREATE TABLE语句也需要python模块游标，因此它用于仅仅连接对象应该足够的情况 - 正如OP正确指出的那样。这种抽象不同于人们理解db游标的原因，因此也是用户的混淆/挫败感。无论效率如何，它只是一个概念上的开销。如果在文档中指出python模块游标与SQL和数据库中的游标有点不同，那将会很好。

#2

You need a cursor object to fetch results. Your example works because it's an INSERT and thus you aren't trying to get any rows back from it, but if you look at the sqlite3 docs, you'll notice that there aren't any .fetchXXXX methods on connection objects, so if you tried to do a SELECT without a cursor, you'd have no way to get the resulting data.

您需要一个游标对象来获取结果。您的示例有效，因为它是一个INSERT，因此您不会尝试从中获取任何行，但是如果您查看sqlite3文档，您会注意到连接对象上没有任何.fetchXXXX方法，所以如果你试图在没有光标的情况下进行SELECT，你无法得到结果数据。

Cursor objects allow you to keep track of which result set is which, since it's possible to run multiple queries before you're done fetching the results of the first.

Cursor对象允许您跟踪哪个结果集，因为在您获取第一个结果之前可以运行多个查询。

#3

According to the official docs connection.execute() is a nonstandard shortcut that creates an intermediate cursor object.

根据官方文档connection.execute（）是一个非标准的快捷方式，它创建一个中间游标对象。

#4

12.6.8. Using sqlite3 efficiently

12.6.8。有效地使用sqlite3

12.6.8.1. Using shortcut methods

12.6.8.1。使用快捷方式

Using the nonstandard execute(), executemany() and executescript() methods of the Connection object, your code can be written more concisely because you don’t have to create the (often superfluous) Cursor objects explicitly. Instead, the Cursor objects are created implicitly and these shortcut methods return the cursor objects. This way, you can execute a SELECT statement and iterate over it directly using only a single call on the Connection object.

使用Connection对象的非标准execute（），executemany（）和executioncript（）方法，可以更简洁地编写代码，因为您不必显式创建（通常是多余的）Cursor对象。而是隐式创建Cursor对象，这些快捷方法返回游标对象。这样，您可以执行SELECT语句并仅使用Connection对象上的单个调用直接迭代它。

(sqlite3 documentation; emphasis mine.)

（sqlite3文档;强调我的。）

Why not just use the connection object?

为什么不直接使用连接对象？

Because those methods of the connection object are nonstandard, i.e. they are not part of Python Database API Specification v2.0 (PEP 249).

因为连接对象的那些方法是非标准的，即它们不是Python数据库API规范v2.0（PEP 249）的一部分。

As long as you use the standard methods of the Cursor object, you can be sure that if you switch to another database implementation that follows the above specification, your code will be fully portable. Perhaps you will only need to change the import line.

只要你使用Cursor对象的标准方法，你可以肯定的是，如果你切换到遵循上述规范的另一个数据库实现，你的代码将完全便携。也许你只需要更改导入线。

But if you use the connection.execute there is a chance that switching won't be that straightforward. That's the main reasons you might want to use cursor.execute instead.

但是如果你使用connection.execute，切换的可能性就不那么简单了。这是您可能想要使用cursor.execute的主要原因。

However if you are certain that you're not going to switch, I'd say it's completely OK to take the connection.execute shortcut and be "efficient".

但是，如果你确信你不会切换，我会说这是完全可以采取connection.execute快捷方式，是“有效的”。

#1