[DB-SIG] "lists of tuples" vs. "tuples of tuples"

[Michael Bayer]

M.-A. Lemburg <mal at egenix.com> wrote:

> On 01.02.2015 22:47, Michael Bayer wrote:
>> Hi -
>> 
>> May I get clarification from pep-249 regarding the contract for
>> fetchall() and fetchmany(), as to if it is acceptable and/or recommended
>> that these methods return tuples of tuples, as opposed to lists of
>> tuples?
>> 
>> In my experience, while tuples and lists are just “sequences” that are
>> immutable / mutable, there is a more subtle semantic distinction between
>> them, in that lists are for variable-length collections of homogenous
>> elements (e.g. rows), whereas tuples are appropriate for fixed-length
>> collections of heterogenous elements (e.g. column values).
>> 
>> If this is the case, pep-249’s use of the language "e.g. a list of
>> tuples” leaves this open to interpretation and inconsistency, like so
>> many other things. I’d like to get some language, at least in email here,
>> that lists are recommended, if this is the case. Otherwise, I guess it’ll
>> just be another thing for me to suggest for DBAPI-3 :).
>> 
>> thanks for your attention!
> 
> We have made this more general in the DB-API to allow authors to
> create e.g. result set objects which implement the sequence API
> and row objects which also implement sequence API.
> 
> I know several database modules which provide ways to have the
> fetch methods return row objects, but I'm not aware of ones which
> implement a result set object.
> 
> You get the best performance using lists of tuples. Tuple creation
> is fast and lists are ideal for variable length storage of objects.
> 
> Using tuples for result sets is not a very useful thing to do,
> since you typically want to manipulate the result set in the
> application.

Both MySQL and PyMySQL return the result as a tuple of tuples, specifically
because tuples are “immutable” and a result set from the DB should also be
“immutable”, because you can’t change what the DB just returned to you. 
It’s wrong both from a practical standpoint as well as a
semantic one.  Though in practice, people haven’t seemed to call for this result
specifically to be mutable. It’s usually being shuttled off to some other
collection somewhere.

 I’d love if DBAPI could grant some more clarity on this.