Why do you need to create a cursor when querying a sqlite database?

by

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.

More Related Contents:

  1. Variable table name in sqlite
  2. SQLite parameter substitution problem
  3. Importing a CSV file into a sqlite3 database table using Python
  4. How do you escape strings for SQLite table/column names in Python?
  5. How to retrieve inserted id after inserting row in SQLite using Python?
  6. python 3.2 UnicodeEncodeError: ‘charmap’ codec can’t encode character ‘\u2013’ in position 9629: character maps to
  7. Sqlite insert query not working with python?
  8. Inserting a table name into a query gives sqlite3.OperationalError: near “?”: syntax error
  9. Python insert numpy array into sqlite3 database
  10. Python CSV to SQLite
  11. Python sqlite3 and concurrency
  12. What if I don’t close the database connection in Python SQLite
  13. HDF5 – concurrency, compression & I/O performance [closed]
  14. cursor.fetchall() vs list(cursor) in Python
  15. How to get a single result from a SQL query in python?
  16. Parameter unsupported when inserting int
  17. Executing an SQL query over a pandas dataset
  18. Is this Python code vulnerable to SQL injection? (SQLite3)
  19. SQLite Performance Benchmark — why is :memory: so slow…only 1.5X as fast as disk?
  20. pysqlite: Placeholder substitution for column or table names?
  21. SQLite not saving data between uses
  22. Problem with regexp python and sqlite
  23. Python SQLite3 SQL Injection Vulnerable Code
  24. How to open and convert sqlite database to pandas dataframe
  25. SQLite, python, unicode, and non-utf data
  26. Pycharm environment different than command line
  27. Can’t change system default SQLite binary
  28. Passing SQLite variables in Python
  29. Python sqlite3 parameterized drop table
  30. Python sqlite3.OperationalError: no such table:

Leave a Comment