[DB-SIG] return value of .fetchall() with an empty result set

Andy Todd andy47@halfcooked.com
Tue, 23 Apr 2002 09:21:24 +1000

brian zimmer wrote:
> I need a clarification on the use of .fetchall().  Which of the below
> scenarios is correct?
> The call
>  c.execute("select * from some_table where 1=0")
>  data = c.fetchall()
>  (1) raises an Error
>  (2) data == []
>  (3) data is None
> Under some cases I can not know the number of rows returned in the query
> so the user must try one of the fetchXXX methods.  I currently implement
> zxJDBC as (3) which is probably the most incorrect.  I *think* it should
> probably return (2) to be more consistent with .fetchmany().  (1) seems
> rather harsh to me as this is not really an exceptional case, there's
> just no data.  Either way it appears I'm not consistent with the API.
> How have others implemented this?  Is everyone else raising an Error?
> Is the preferred method to check the .rowcount attribute and only call
> .fetchXXX if it's greater than zero?
> thanks,
> brian


All of the db modules I use return data==() ( or data==[] ) when 
fetchall returns no rows.

This is consistent both with Python and SQL because your query is valid 
but returns an empty set. I wouldn't expect a cursor method to raise an 
exception unless some part of the database operation is incorrect or 
fails and I wouldn't expect a valid call to return None, but that would 
be better than raising an exception.

The DB-API (http://www.python.org/topics/database/DatabaseAPI-2.0.html) 
is not entirely clear on this subject, so maybe some clarification is 
required. I would suggest that when writing your own database handler 
that the most important thing is to be consistent in how you return the 
empty set from fetchone, fetchmany and fetchall.

 From the desk of Andrew J Todd esq - http://www.halfcooked.com