标签归档:cursor

查询sqlite数据库时为什么需要创建游标?

问题:查询sqlite数据库时为什么需要创建游标?

我对Python的sqlite3模块(以及与此相关的SQL)完全陌生,这完全让我感到困惑。对cursor对象的大量描述不足(而是其必要性)似乎也很奇怪。

此代码段是首选的处理方式:

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()

即使它工作得很好,并且没有(似乎毫无意义),它也不是这样cursor

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

谁能告诉我为什么我需要一个cursor
似乎没有意义的开销。对于脚本中访问数据库的每个方法,都应该创建并销毁一个cursor
为什么不只是使用connection对象?

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.

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?


回答 0

在我看来,这只是一个错误应用的抽象。数据库游标是一种抽象,用于遍历数据集。

维基百科有关主题的文章

在计算机科学和技术中,数据库游标是一种控制结构,可以遍历数据库中的记录。游标有助于与遍历一起进行的后续处理,例如数据库记录的检索,添加和删除。数据库游标的遍历特性使游标类似于迭代器的编程语言概念。

和:

游标不仅可以用于将数据从DBMS提取到应用程序中,还可以标识表中要更新或删除的行。为此,SQL:2003标准定义了定位更新和定位删除SQL语句。这样的语句不使用带谓词的常规WHERE子句。而是用光标标识该行。游标必须已打开,并且已经通过FETCH语句定位在一行上。

如果查看Python sqlite模块上文档,您会发现cursor即使是一条CREATE TABLE语句也需要一个python模块,因此它用于仅一个connection对象就足够的情况-正如OP正确指出的那样。这种抽象与人们理解db游标的方式不同,因此与用户的困惑/挫败感不同。无论效率如何,这只是概念上的开销。如果在文档中指出python模块cursor与SQL和数据库中的游标有点不同,那将是很好的。

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

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.

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.


回答 1

您需要一个游标对象来获取结果。您的示例可以正常运行,因为它是一个INSERT,因此您没有尝试从中获取任何行,但是如果您查看sqlite3docs,则会注意到.fetchXXXX连接对象上没有任何方法,因此,如果您尝试这样做如果SELECT没有光标,您将无法获取结果数据。

游标对象使您可以跟踪哪个结果集是哪个结果集,因为可以在获取第一个结果之前运行多个查询。

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.

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.


回答 2

根据官方文档,这 connection.execute()是一个创建中间光标对象的非标准快捷方式

Connection.execute
这是一个非标准的快捷方式,它通过调用cursor()方法创建游标对象,使用给定的参数调用游标的execute()方法,然后返回游标。

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

Connection.execute
This is a nonstandard shortcut that creates a cursor object by calling the cursor() method, calls the cursor’s execute() method with the parameters given, and returns the cursor.


回答 3

12.6.8。使用sqlite3的高效 LY

12.6.8.1。使用快捷方式

使用非标准的 execute()executemany()并且executescript()Connection对象的方法,您可以编写代码更简洁 LY,因为你没有创建(通常是多余的)光标明确对象。而是,隐式创建Cursor对象,并且这些快捷方式方法返回游标对象。这样,您可以执行SELECT语句并直接使用Connection对象上的单个调用直接对其进行迭代。

sqlite3文档;重点是我的。)

为什么不只使用连接对象?

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

只要您使用Cursor对象的标准方法,就可以确保如果切换到遵循上述规范的另一个数据库实现,您的代码将完全可移植。也许您只需要更改import行。

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

但是,如果您确定不打算切换,那么我想选择connection.execute快捷方式并“高效” 是完全可以的。

12.6.8. Using sqlite3 efficiently

12.6.8.1. Using shortcut methods

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.

(sqlite3 documentation; emphasis mine.)

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

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.

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

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


回答 4

它使我们能够通过与数据库的同一连接来拥有多个单独的工作环境。

It gives us the ability to have multiple separate working environments through the same connection to the database.