What's the difference between using "c.fetchall()" vs. just assigning "c.execute(SELECT...." to a variable?

Question

I'm pretty certain that for the most they are functionally the same but I'm wondering if there's some cases where they are not and what's going on under the hood that makes it so?

Example:

c.execute("""SELECT * FROM players WHERE team = ?;"""), (team_name,))
results = c.fetchall()

player_list = []
for player in results:
    player_list.append(player)

vs.

results = c.execute("""SELECT * FROM players WHERE team = ?;"""), (team_name,))

player_list = []
for player in results:
    player_list.append(player)

Answer 1

The Python DBAPI doesn't define what execute returns: it could be a generator, it could be a list of results, it could be some custom object representing the results, etc. It only promises that the database query will be made; how the results are presented or made available is not defined.

The fetch_all method, however, is defined to return a "sequence of sequences", which means you have the actual, instantiated result in memory immediately. Iterating over the result, in particular, isn't going to trigger a delayed database connection to execute a query or fetch more results.

One consequence of this is that an implementation could define your two approaches to be equivalent, but it is not required to.