fetchall() should return empty sequence instead of None when no rows returned

[Ryan Williams]

>>> from mysql import connector
>>> conn = connector.Connect('localhost', user='root', db='test')
>>> curs = conn.cursor()
>>> curs.execute('SELECT * FROM test WHERE 1 = 0')
>>> print curs.fetchall()
None

This means that every piece of code that calls fetchall has to have a special case branch for None; if it returned an empty sequence, no special cases would be needed. This is probably also a minor violation of PEP-249. Easy to fix, though!

[Geert JM Vanderkelen (geertjmvdk)]

This went from empty list to None a few times. PEP-249 says actually nothing what it should return in case of "empty" result set:

     .fetchall()

            Fetch all (remaining) rows of a query result, returning
            them as a sequence of sequences (e.g. a list of tuples).
            Note that the cursor's arraysize attribute can affect the
            performance of this operation.

            An Error (or subclass) exception is raised if the previous
            call to .execute*() did not produce any result set or no
            call was issued yet.

It does say explicitly that .fetchone() should return None and .fetchmany() an empty list.
It's easy to change indeed, but maybe I'm just missing the point in PEP-249?
Personally, I favor [] as well, it does indeed make sense.

"SELECT * FROM test WHERE 1 = 0" does produce an empty result set, so the exception is not raised either.

[Sergey Shepelev (temoto)]

Since we all like empty list, you may pull from my branch. :)

[Geert JM Vanderkelen (geertjmvdk)]

Fix committed (rev 140): .fetchall() now returns [] when result set was empty.

Thanks for the bug report!

[Ryan Williams (rdw)]

Cool, thanks for the fix! I guess I thought that it was a violation of PEP-249 because the spec dictates a return value of a sequence unilaterally, which implies to me that it must return a sequence even in the empty case. Anyhow, water under the bridge since happiness has ensued!

[Sergey Shepelev (temoto)]

BTW, i wrote to <email address hidden> (PEP 249 support) and got official answer: .fetchall() should return an empty list.

> The spec is not as clear in this respect as for .fetchmany(), but since you can use .fetchmany() and .fetchall() interchangeably on the cursor, the same logic applies to both of them:

[Geert JM Vanderkelen (geertjmvdk)]

@Ryan:
Most welcome! Thanks again for bringing this up!

@Sergey:
That's a good idea! I was going to blog about this, but I guess waiting for official answer might be good.

I now realize I missed the 'remaining' in PEP's .fetchall(): "Fetch all (remaining) rows ..". Have to check that later, but should work according to code :)