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

Question

I am completely new to the sqlite3 Python module (and generally SQL in this case), and it's just a complete stump. The abundant lack of descriptions of cursor objects (rather, their necessity) also seems strange.

This piece of code is the preferred way:

 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 is not, although it works just as well without the (seemingly meaningless) cursor :

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

Can someone tell me why i cursor ?
It just seems pointless overhead. For each method in my script that accesses the database, do I have to create and destroy the cursor ?
Why not just use the connection object?

Answer 1

It seems to me that this is a wrong abstraction. The db cursor is an abstraction designed to traverse a dataset.

Wikipedia related topics :

In computer science and technology, the database cursor is a management structure that allows you to bypass records in the database. Cursors facilitate post-processing in conjunction with crawls such as retrieving, adding, and deleting database records. A database cursor specific to crawl makes cursors look like a programming language iterator concept.

As well as:

Cursors can be used not only to retrieve data from the DBMS into the application, but also to determine the row in the table that needs to be updated or deleted. The SQL: 2003 standard defines positional updates and positions the removal of SQL statements for this purpose. Such statements do not use the regular WHERE clause with predicates. Instead, the cursor identifies the row. The cursor should be open and already placed on the line using the FETCH statement.

If you check the documents on the Python sqlite module , you will see that the python module cursor is needed even for the CREATE TABLE statement, so it is used for cases when a simple connection object is enough - as OP correctly pointed out. This abstraction is different from what people mean by the db cursor and therefore confusion / frustration on the part of users. Regardless of efficiency, this is just conceptual overhead. It would be nice if the docs indicated that the cursor the python module is different from the cursor in SQL and databases.

Answer 2

You need a cursor object to get the results. Your example works because it is INSERT and therefore you are not trying to get any rows from it, but if you look at sqlite3 docs , you'll notice that there are no .fetchXXXX methods on the connection objects, so if you tried make SELECT without a cursor, you would not be able to get the resulting data.

Cursor objects allow you to keep track of which result set is one that, since it can run multiple queries, before you fetch the results of the first.

Answer 3

According to official docs, connection.execute() is a non-standard shortcut that creates an intermediate cursor object:

Connection.execute
This is a non-standard shortcut that creates a cursor object by calling the cursor () method, calls the execute () cursor method with the specified parameters, and returns the cursor.

Answer 4

12.6.8. Effective use of sqlite3

12.6.8.1. Using shortcuts

Using the non-standard execute() , executemany() and executescript() methods of the Connection object, your code can be written in more concise Ly, because you do not have to create (often superfluous) Cursor objects in explicit form. Instead, Cursor objects are created implicitly, and these shortcut methods return cursor objects. Thus, you can execute the SELECT statement and iterate over it directly using only one call to the Connection object.

( sqlite3 documentation ; highlighting mine.)

Why not just use a connection object?

Because these connection object methods are non-standard , that is, they are not part of the Python v2.0 Database API Specification (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 meets the above specification, your code will be fully portable. You may only need to change the import line.

But if you use connection.execute there is a possibility that switching will not be so simple. This is the main reason you can use cursor.execute instead.

However, if you are sure that you are not going to switch, I would say that everything is in order to use the connection.execute shortcut and be "effective".

Answer 5

This enables us to have several separate workspaces through the same database connection.