Solution doesn't need cursor instance?

On https://app.dataquest.io/m/116/introduction-to-indexing/1/introduction, the solution provided is

import sqlite3
conn = sqlite3.connect("factbook.db")
schema = conn.execute("pragma table_info(facts);").fetchall()
for s in schema:
    print(s)

But in all the PostgreSQL exercises prior to this one, we had to instantiate a cursor instance. How come one isn’t needed here? Is it because it’s not needed for SQLite?

I tried rerunning the solution with a cursor instance, and it passes as well.

import sqlite3
conn = sqlite3.connect("factbook.db")
cursor = conn.cursor()

cursor.execute('pragma table_info(facts);')
schema = cursor.fetchall()
for item in schema:
    print(item)

In the Querying SQLite from Python mission it is explained that the Connection object also have the execute method that creates a cursor under the hood, therefore you do no need to create it yourself. Here’s the mission’s text:

So far, we’ve run queries by creating a Cursor instance, and then calling the execute method on the instance. The SQLite library actually allows us to skip creating a Cursor altogether by using the execute method within the Connection object itself. SQLite will create a Cursor instance for us under the hood and run our query against the database, allowing us to skip a step. Here’s what the code looks like:

conn = sqlite3.connect("jobs.db")
query = "select * from recent_grads;"`
conn.execute(query).fetchall()

Notice that we didn’t explicitly create a separate Cursor instance ourselves in this code example.

Ah, thank you! I have forgotten about that.

What are the pros and cons to explicitly instantiating a Cursor then? Which is the convention?

The documentation defines conn.execute() as 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.

As conn.execute() creates a cursor under the hood I do not think there’s any such thing as pros and cons for this situation since the process is basically the same.

Mentioned in Cat Plus Plus answer Python has a PEP249 standard that does not allow execute on connection. It’s Sqlite3’s own extension to allow that. Also, people were saying python’s cursor is different from general cursors, so when learning you should consider where you are in the chain of library --> language --> abstract concept.

I’m sure you will use requests some day.
There is a similar analogy with whether to use request.get vs session.get

2 Likes