为什么在查询sqlite数据库时需要创建一个游标？ [英] Why do you need to create a cursor when querying a sqlite database?

问题描述

我完全是新的python的sqlite3模块（和SQL在一般情况下），这只是完全堵塞了我。对光标对象的描述的充分缺乏（相反，它们的必要性）也似乎很奇怪。

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?

推荐答案

只是一个错误的抽象， db游标是一个用于
数据集遍历的抽象：

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

http://en.wikipedia.org/wiki/Cursor_%28databases%29

在电脑科学和技术，数据库游标是一个控制
结构，允许遍历数据库中的记录。
光标便于后续处理与
遍历，例如检索，添加和删除数据库
记录。遍历的数据库游标特性使游标
类似于迭代器的编程语言概念。

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.

和：

游标不仅可用于将数据从DBMS提取到
应用程序中，还可用于标识要更新的表中的行，
已删除。 SQL：2003标准定义了定位更新，
定位了删除SQL语句的目的。这样的语句做
不使用带谓词的常规WHERE子句。相反，游标
标识该行。

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.

如果您检查了文档，则必须通过FETCH语句打开并且已经定位
的游标。 Python sqlite模块 http://docs.python.org/2/library/sqlite3.html ，您可以看到，即使对于create table语句，也需要一个python模块游标，因此它用于只有连接对象应该足够 - 如OP正确指出的情况。这样的抽象不同于人们理解的db游标，因此是用户部分的混乱/沮丧。不管效率如何，它只是一个概念开销。如果在文档中指出python模块游标与SQL和数据库中的游标有点不同，将会很好。

If you check the docs on Python sqlite module http://docs.python.org/2/library/sqlite3.html, 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.

这篇关于为什么在查询sqlite数据库时需要创建一个游标？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！