CODEWITHSUNDEEP
pythonsqlsqlite
4m1nh4j1
4m1nh4j1

Reputation: 4356

Is sqlite3 fetchall necessary?

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 ?

Upvotes: 10

Views: 33121

Answers (2)

Paulo Bu
Paulo Bu

Reputation: 29794

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!

Upvotes: 9

CL.
CL.

Reputation: 180040

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.

Upvotes: 19

Related Questions