Is sqlite3 fetchall necessary?

Question

I just started using sqlite3 with python . I would like to know the difference between :

cursor = db.execute("SELECT customer FROM table")
    for row in cursor:
              print row[0]

and

cursor = db.execute("SELECT customer FROM table")
    for row in cursor.fetchall():
              print row[0]

Except that cursor is <type 'sqlite3.Cursor'> and cursor.fetchall() is <type 'list'>, both of them have the same result .

Is there a difference, a preference or specific cases where one is more preferred than the other ?

Solution 1

fetchall() reads all records into memory, and then returns that list.

When iterating over the cursor itself, rows are read only when needed. This is more efficient when you have much data and can handle the rows one by one.

Solution 2

The main difference is precisely the call to fetchall(). By issuing fetchall(), it will return a list object filled with all the elements remaining in your initial query (all elements if you haven't get anything yet). This has several drawbacks:

1. Increments memory usage: by storing all the query's elements in a list, which could be huge
2. Bad performance: filling the list can be quite slow if there are many elements
3. Process termination: If it is a huge query then your program might crash by running out of memory.

When you instead use cursor iterator (for e in cursor:) you get the query's rows lazily. This means, returning one by one only when the program requires it.

Surely that the output of your two code snippets are the same, but internally there's a huge perfomance drawback between using the fetchall() against using only cursor.

Hope this helps!

Comments

[David Oldford]

I think it's important to consider that there could be parallel writes to a database while you run through a select query. Consider what happens if you want to update some of the rows you are iterating over or if there is another process or thread that could be doing such updates. If you iterate over the cursor itself your query can suddenly stop returning rows that it should have but if you fetch all of the rows into memory you get the rows for that window of time without modifications, for better or worse.

[Wirsing]

@DavidOldford SQLite always uses serializable transactions; that cannot happen.

[David Oldford]

it depends on if you are using transactions for everything you do. I haven't really tested multithreading scenarios but in a single threaded app if you iterate over a list of rows you want to update using a select using the cursor and attempt to update them within this loop you won't update all the rows you expect. If you fetchall of the rows this approach will work. I didn't expect it myself it is probably caused by the serialized transaction system, ie a transaction during iteration over a cursor changes the underlying data and the cursor just pukes its guts out.