[DB-SIG] Result Set Inconsistencies

[Orr, Steve]

> However, there is a fundamental difference between tuples and lists
Exactly!!!

> ...the standard needs to be specific about what kind of sequence is
> used to bundle up result sets.  It's terrible to think that something 
> innocent like:
> ###
> cursor.execute("select name, email from people")
> if ('dyoo', 'dyoo at hkn.eecs.berkeley.edu') in cursor.fetchall():
>     print "I'm in there!"
> ###
> won't work in Oracle simply because each result row is a list.

Here's a quote from the spec:
"This API has been defined to encourage similarity between the
Python modules that are used to access databases.  By doing this,
we hope to achieve a consistency leading to more easily understood
modules, code that is generally more portable across databases..."

Because results sets can either be lists or tuples (or strings?) the API
fails its stated purpose. 

Retrofitting clarity into the spec and imposing lists or tuples as the
only way to handle result sets would be disruptive BUT... Enhancing the
spec to require user configurable result set types could be quite
beneficial.


-----Original Message-----
From: Danny Yoo [mailto:dyoo at hkn.eecs.berkeley.edu] 
Sent: Wednesday, July 16, 2003 3:03 PM
To: Chris Cogdon
Cc: Orr, Steve; db-sig at python.org
Subject: Re: [DB-SIG] Result Set Inconsistencies


On Wed, 16 Jul 2003, Chris Cogdon wrote:

> > I know the spec calls for sequences but why? Why be vague on an API 
> > "spec?" Intentional inconsistency? So if I'm developing against 
> > multiple databases and I want consistent result sets I have to know 
> > the behavior of each module and convert types? Seems weird to me.
> >
> > I'm thinking results sets should always be a lists or I should be 
> > able to specify how I want the result sets. I know there are lots of

> > differences in database engines and an API can't make all database 
> > engines behave the same but it OUGHT to impose SOME consistency.
>
> Both lists and tuples follow the sequence specification. Ie, you find 
> out their length, get elements and slices all using the same syntax. 
> For example, where 's' is some sequence.
>
> length = len(s)
> element_4 = s[4]
> a_slice = s[2:4]
> for i in s:
> 	print "element", i
>
> All of the preceding work the same regardless of whether 's' is a 
> list, tuple, or a PgResultSet


However, there is a fundamental difference between tuples and lists:
they don't compare!

###
>>> (1, 2) == [1, 2]
0
###


In this case, I agree that the standard needs to be specific about what
kind of sequence is used to bundle up result sets.  It's terrible to
think that something innocent like:

###
cursor.execute("select name, email from people")
if ('dyoo', 'dyoo at hkn.eecs.berkeley.edu') in cursor.fetchall():
    print "I'm in there!"
###

won't work in Oracle simply because each result row is a list.