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

问题描述

我对 Python 的 sqlite3 模块(以及一般的 SQL)完全陌生就此而言)，这完全让我难堪.cursor 对象 的大量缺乏描述a>(而是它们的必要性)似乎也很奇怪.

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

这个不是，即使它也能正常工作并且没有(看似毫无意义的)cursor:

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

谁能告诉我为什么需要 cursor?
这似乎是毫无意义的开销.对于访问数据库的脚本中的每个方法，我应该创建和销毁一个 cursor?
为什么不直接使用 connection 对象?

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 cursor 是一种抽象，用于数据集遍历.

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

来自关于主题的维基百科文章:

在计算机科学技术中，数据库游标是一个控件允许遍历数据库中的记录的结构.游标有助于后续处理与遍历，如数据库的检索、添加和删除记录.遍历的数据库游标特性使得游标类似于迭代器的编程语言概念.

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 子句.相反，一个游标标识行.游标必须打开并且已经定位通过 FETCH 语句在一行上.

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.

如果您查看 关于 Python sqlite 模块的文档，您可以看到即使对于 CREATE TABLE 语句，也需要 python 模块 cursor，因此它用于仅 connection 对象就足够的情况 - 正如正确指出的那样被 OP 淘汰.这种抽象与人们对 db 游标的理解不同，因此也与用户的困惑/沮丧不同.不管效率如何，这只是一个概念上的开销.如果在文档中指出 python 模块 cursor 与 SQL 和数据库中的游标有点不同，那就太好了.

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.

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