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.
0
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.
1