Обсуждение: Generic casters for composite types

Hello.

I think it may be interesting to add a generic mechanism for registering
composite type casters.

One of the features I like about postgresql is the ability to perform
queries like this one:

select user_group, array_agg(user) from user_group natural join user
group by user_group

Where user_group and user are two tables related by a foreign key.

Such a query is pleasant to write, but unusable for now because psycopg
returns those values as strings.

Since those types are defined by postgresql at table creation time, it
should be easy to parse those results to return namedtuples.

Would you find a such a feature interesting ? Or is it something that
should be best kept in a distinct library ?

I've attached a small 'proof of concept' file demonstrating how this
could be implemented. The parser implementation is simplistic (and does
not work with complex types), but if this is of interest to you I would
happily work on it.

--
Ronan Dunklau

On Thu, Dec 15, 2011 at 3:17 PM, Ronan Dunklau <rdunklau@gmail.com> wrote:

> I think it may be interesting to add a generic mechanism for registering
> composite type casters.

Such as this one? :)
http://initd.org/psycopg/docs/extras.html#composite-types-casting


> One of the features I like about postgresql is the ability to perform
> queries like this one:
>
> select user_group, array_agg(user) from user_group natural join user
> group by user_group
>
> Where user_group and user are two tables related by a foreign key.
>
> Such a query is pleasant to write, but unusable for now because psycopg
> returns those values as strings.
>
> Since those types are defined by postgresql at table creation time, it
> should be easy to parse those results to return namedtuples.

The register_composite() does *almost* what you want. Without the
"almost", the result, after committing the tables created in your
example, is:

    >>> import psycopg2
    >>> import psycopg2.extras
    >>> cnn = psycopg2.connect('dbname=test')
    >>> psycopg2.extras.register_composite('test2', cnn)
    >>> psycopg2.extras.register_composite('test1', cnn)
    >>> cur = cnn.cursor()
    >>> cur.execute("""select test1, array_agg(test2) as test2s from
test1 inner join test2 on test1.id = test2.test_id group by test1;""")
    >>> cur.fetchone()
    (test1(id=1, label='test1'),
     [test2(id=1, label='testa', test_id=1),
      test2(id=2, label='testb', test_id=1)])
    >>> cur.fetchone()
    (test1(id=2, label='test2'),
     [test2(id=3, label='testc', test_id=2),
      test2(id=4, label='testd', test_id=2)])

Why the "almost"? The query to introspect the database was tested on
composite types created by CREATE TYPE, not on the ones created after
the tables, and it doesn't account for dropped fields and hidden
columns (cmin, xmin ecc.). Plus, there was an error raising the error
message after the column count mismatch. With the following patch, the
above works as expected. It takes psycopg 2.4.3 as previous version
didn't support arrays of composites:

    diff --git a/lib/extras.py b/lib/extras.py
    index 491a390..d9c2ab9 100644
    --- a/lib/extras.py
    +++ b/lib/extras.py
    @@ -841,8 +841,8 @@ class CompositeCaster(object):
             tokens = self.tokenize(s)
             if len(tokens) != len(self.atttypes):
                 raise psycopg2.DataError(
    -                "expecting %d components for the type %s, %d
found instead",
    -                (len(self.atttypes), self.name, len(self.tokens)))
    +                "expecting %d components for the type %s, %d found instead"
    +                % (len(self.atttypes), self.name, len(tokens)))

             attrs = [ curs.cast(oid, token)
                 for oid, token in zip(self.atttypes, tokens) ]
    @@ -914,6 +914,8 @@ FROM pg_type t
     JOIN pg_namespace ns ON typnamespace = ns.oid
     JOIN pg_attribute a ON attrelid = typrelid
     WHERE typname = %%s and nspname = %%s
    +AND NOT attisdropped
    +AND attnum > 0
     ORDER BY attnum;
     """ % typarray, (tname, schema))

So, thank you very much for your test case: I will include it in the
test suite together with the above patch to make sure the composite
adapter works with tables too.


-- Daniele

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 15/12/2011 17:21, Daniele Varrazzo wrote:
> On Thu, Dec 15, 2011 at 3:17 PM, Ronan Dunklau <rdunklau@gmail.com>
> wrote:
>
>> I think it may be interesting to add a generic mechanism for
>> registering composite type casters.
>
> Such as this one? :)
> http://initd.org/psycopg/docs/extras.html#composite-types-casting

How can I have missed that ?! ;)
Sorry for bothering you with something I could have found in the docs.

> The register_composite() does *almost* what you want. Without the
> "almost", the result, after committing the tables created in your
> example, is:
>
>>>> import psycopg2 import psycopg2.extras cnn =
>>>> psycopg2.connect('dbname=test')
>>>> psycopg2.extras.register_composite('test2', cnn)
>>>> psycopg2.extras.register_composite('test1', cnn) cur =
>>>> cnn.cursor() cur.execute("""select test1, array_agg(test2) as
>>>> test2s from
> test1 inner join test2 on test1.id = test2.test_id group by
> test1;""")
>>>> cur.fetchone()
> (test1(id=1, label='test1'), [test2(id=1, label='testa',
> test_id=1), test2(id=2, label='testb', test_id=1)])
>>>> cur.fetchone()
> (test1(id=2, label='test2'), [test2(id=3, label='testc',
> test_id=2), test2(id=4, label='testd', test_id=2)])
>
> Why the "almost"? The query to introspect the database was tested
> on composite types created by CREATE TYPE, not on the ones created
> after the tables, and it doesn't account for dropped fields and
> hidden columns (cmin, xmin ecc.). Plus, there was an error raising
> the error message after the column count mismatch. With the
> following patch, the above works as expected. It takes psycopg
> 2.4.3 as previous version didn't support arrays of composites:
>
> diff --git a/lib/extras.py b/lib/extras.py index 491a390..d9c2ab9
> 100644 --- a/lib/extras.py +++ b/lib/extras.py @@ -841,8 +841,8 @@
> class CompositeCaster(object): tokens = self.tokenize(s) if
> len(tokens) != len(self.atttypes): raise psycopg2.DataError( -
> "expecting %d components for the type %s, %d found instead", -
> (len(self.atttypes), self.name, len(self.tokens))) +
> "expecting %d components for the type %s, %d found instead" +
> % (len(self.atttypes), self.name, len(tokens)))
>
> attrs = [ curs.cast(oid, token) for oid, token in
> zip(self.atttypes, tokens) ] @@ -914,6 +914,8 @@ FROM pg_type t
> JOIN pg_namespace ns ON typnamespace = ns.oid JOIN pg_attribute a
> ON attrelid = typrelid WHERE typname = %%s and nspname = %%s +AND
> NOT attisdropped +AND attnum > 0 ORDER BY attnum; """ % typarray,
> (tname, schema))

Thank you for such a detailed answer.

> So, thank you very much for your test case: I will include it in
> the test suite together with the above patch to make sure the
> composite adapter works with tables too.

You're welcome, I'm glad this mail turned out to be useful.


- --
Ronan Dunklau
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.18 (GNU/Linux)

iQEcBAEBAgAGBQJO6iR8AAoJECTYLCgFy323QOAH/i9X+K9VF2IDTgtx1+qTsqvJ
xPdUgJYNuKZeO7EquNFJb3mBHLW+V9N4ov6FGW/Y+jzSvFLfm1WBjAThISBnsdda
TWiJU2p9YOcQXC1sPtTTwgjuAAmze6KIG3QnGqtF1Kjk4Lwx/7wuPAO0wpgPCIdW
kRl0lOJEpx9/UpBkCzq8UirtluQwVi2gdUJrf+pGng17mG1rHEJrlmk62Tkr36CR
Any6TG6oNHYR/rPqpEBTVj4qL0V9PUPoldhn8Uc0sYsuNGxoqE+QPQu1nYvBz3Q9
XhGExwqp4tyWEmUIc4JCDzUhv6PR+99H8XpKw5QZAHti7SDPl9fG5cmpEBrBxB8=
=OcDB
-----END PGP SIGNATURE-----