[PyGreSQL] DB 2.0 list of tuples

[Erin Sheldon]

On 11/17/06, Christoph Zwerschke <cito at online.de> wrote:
> > I'm trying out the pgdb interface for
> > the first time.  One surprise was that the
> > results in fetchall() are returned as a list
> > of lists.  The DB 2.0 specification says the
> > results are a list of tuples.  Is there a way
> > to get a list of tuples returned?  I have some
> > applications that require this format, and
> > copying the result is not going to be
> > efficient.
>
> No, the DB API 2 specification does not say it is a list of tuples. It
> says it is a "sequence of sequences (e.g. a list of tuples)". So it
> could as well be a list of lists (as in pgdb) or a tuple of tuples or a
> list of some special row objects. All these different implementations
> exist. So if your application wants to be DB API 2 conform, it should
> not assume anything here. If you need something specific (normally it
> should not matter), use tuple(...) or list(...) to convert it
> explicitly. Don't worry, this will not copy the whole result:
>
>  >>> a = ['test']
>  >>> b = tuple(a)
>  >>> b[0] is a[0]
> True
>
> The reason why pgdb (contrary to pg) returns a list of lists is that the
> rows are typecasted in pgdb and this process converts them to lists. It
> would require an additional step to convert them back to tuples. I see
> not real reason why PyGreSQL should do that other that it would be more
> consistent with the implementation in the classic pg interface and also
> in psycopg2 (but not in some other DB API 2 interfaces).

Thanks Chris -

To give some context to this: I am working with numerical
python, reading from postgres (and other) databases into
NumPy arrays.  Since I sent this message, a very useful
thread on this subject has developed on the numpy list.

First of all, it is nice to know that no copy is made, I didn't
realize that.  Also, we have found the cursor-as-iterator to
be very useful in creating these arrays using the "fromiter"
array constructor. This is  efficient both in terms of memory
and speed.

Thanks again,
Erin