Why chaining matters (Python & SQLite3)?

Question

Why does

cursor = connection.cursor()
cursor.execute("...
return cursor.fetchall()

return the expected non-empty result whereas

connection.cursor().execute("...
return connection.cursor().fetchall()

returns an empty list the SQLite3-command being identical in both cases?

EDIT 2014-02-19: Added the last "...fetchall()"-line to code samples.

Answer 1

For the sqlite3 library, the cursor.execute() call always returns the cursor object again. Both your statements are identical here:

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> conn.cursor().execute('SELECT DATE()')
<sqlite3.Cursor object at 0x1083d89d0>
>>> cursor = conn.cursor()
>>> cursor.execute('SELECT DATE()')
<sqlite3.Cursor object at 0x1083d8960>

However, if you didn't store a reference to the cursor returned from the 'chained' call, you won't have access to the result set anymore, a new cursor object from connection.cursor() won't list those results as it'll be a different cursor object:

>>> conn.cursor().execute('SELECT DATE()')
<sqlite3.Cursor object at 0x1083d8a40>
>>> list(conn.cursor())
[]
>>> cursor = conn.cursor().execute('SELECT DATE()')
>>> list(cursor)
[(u'2014-02-18',)]

That has nothing to do with chaining and everything with the purpose of cursors: to provide distinct views on the database, each with their own independent state.