Postgres custom composite types

[Jason]

Hello, 

I'm looking or some direction on how to implement the SQL syntax when querying Postgres' composite types. In case you're unfamiliar: the composite types in Postgres are a type that contains attributes which are regular SQL types. The attribute to type relationship is similar to the column to table relationship (except there are no constraints on attributes). A table column can be this composite type. Psycopg2 implements this by instantiating a namedtuple on retrieval or accepting a namedtuple on update/insert (you can also use your own custom class). The saving and retrieval isn't a problem with SQLAlchemy, I just pass through the namedtuple to/from Psycopg2. 

What I need to implement is the SQL syntax for querying composite type attributes. Postgres allows this by using the syntax "(table_name.column_name).attribute_name = 'bleh'" or just "(column_name).attribute_name = 'bleh'" when a table identifier is not required. I'm not sure how to go about this because the sql generation needs to change the way the column name is output, would this require subclassing the ColumnClause? I think I just need to know where to override the behaviour of generating the qualified column name in statements.

Thanks,

Jason

[Michael Bayer]

you'd be doing ColumnElement here which is the more fundamental object. It's discussed here in "subclassing guidelines": http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#subclassing-guidelines

if you're on 0.8 the integration here can be very smooth, using custom operators (http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators) you could have an approach that looks like: mytable.c.column.attrs.attribute_name == "value". The "attrs" namespace would call into your custom ColumnElement.

Since the custom operator API is a total blast to use here's a proof of concept:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import UserDefinedType, to_instance
from sqlalchemy.sql.expression import ColumnElement

class PGCompositeElement(ColumnElement):
def __init__(self, base, attrname, type_):
self.base = base
self.attrname = attrname
self.type = to_instance(type_)

@compiles(PGCompositeElement)
def _compile_pgelem(element, compiler, **kw):
return "%s.%s" % (
compiler.process(element.base, **kw),
element.attrname
)

class _Namespace(object):
def __init__(self, comparator):
self.comparator = comparator

def __getattr__(self, key):
try:
type_ = self.comparator.type.typemap[key]
except KeyError:
raise KeyError(
"Type '%s' doesn't have an attribute: '%s'" %
(self.comparator.type, key))
return PGCompositeElement(
self.comparator.expr,
key,
type_)

class PGCompositeType(UserDefinedType):
def __init__(self, name, typemap):
self.name = name
self.typemap = typemap

class comparator_factory(UserDefinedType.Comparator):
@property
def attrs(self):
return _Namespace(self)

def get_col_spec(self):
return self.name

if __name__ == '__main__':
from sqlalchemy import String, Integer
from sqlalchemy.sql import table, column

mytype = PGCompositeType("mytype", {"attr1": String(50), "attr2": Integer})

t1 = table('mytable', column('mycolumn', mytype))

print t1.c.mycolumn.attrs.attr2 == 5

print t1.c.mycolumn.attrs.attr1 + "some string"

[Jason]

On Tuesday, March 5, 2013 11:09:00 AM UTC-5, Michael Bayer wrote:

On Mar 5, 2013, at 9:45 AM, Jason <ja...@deadtreepages.com> wrote:

> Hello,
>
> I'm looking or some direction on how to implement the SQL syntax when querying Postgres' composite types. In case you're unfamiliar: the composite types in Postgres are a type that contains attributes which are regular SQL types. The attribute to type relationship is similar to the column to table relationship (except there are no constraints on attributes). A table column can be this composite type. Psycopg2 implements this by instantiating a namedtuple on retrieval or accepting a namedtuple on update/insert (you can also use your own custom class). The saving and retrieval isn't a problem with SQLAlchemy, I just pass through the namedtuple to/from Psycopg2.
>
> What I need to implement is the SQL syntax for querying composite type attributes. Postgres allows this by using the syntax "(table_name.column_name).attribute_name = 'bleh'" or just "(column_name).attribute_name = 'bleh'" when a table identifier is not required. I'm not sure how to go about this because the sql generation needs to change the way the column name is output, would this require subclassing the ColumnClause? I think I just need to know where to override the behaviour of generating the qualified column name in statements.

you'd be doing ColumnElement here which is the more fundamental object.    It's discussed here in "subclassing guidelines": http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#subclassing-guidelines

if you're on 0.8 the integration here can be very smooth, using custom operators (http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators) you could have an approach that looks like:  mytable.c.column.attrs.attribute_name == "value".   The "attrs" namespace would call into your custom ColumnElement.

Since the custom operator API is a total blast to use here's a proof of concept:

Wow thats great, thanks!

There is a problem with this when using declarative and a reflected base. My Column's type get's overrwritten with NullType even though I've set autoreload_replace=False when constructing the table. I am still using the example ReflectedBase that was in the 0.7 documentation (because the new DeferredReflection class in 0.8 raises a NoReferencedTableError when configure_mappers() is called). Any idea how this reflected base is overwriting the type?:

class DeclarativeReflectedBase(object):

    _mapper_args = []

    @classmethod

    def __mapper_cls__(cls, *args, **kw):

        """

            Declarative will use this function in lieu of

            calling mapper() directly.

            Collect each series of arguments and invoke

            them when prepare() is called.

        """

        cls._mapper_args.append((args, kw))

    @classmethod

    def prepare(cls, engine):

        """Reflect all the tables and map !"""

        for args, kw in cls._mapper_args:

            class_ = args[0]

            print class_.__tablename__

            class_.__table__ = table = Table(

                    class_.__tablename__,

                    cls.metadata,

                    extend_existing=True,

                    autoload_replace=False,

                    autoload=True,

                    autoload_with=engine)

            class_.__mapper__ = mapper(class_, table, **kw)

Thanks,

Jason

[Jason]

On Tuesday, March 5, 2013 2:52:59 PM UTC-5, Jason wrote:

On Tuesday, March 5, 2013 11:09:00 AM UTC-5, Michael Bayer wrote:

On Mar 5, 2013, at 9:45 AM, Jason <ja...@deadtreepages.com> wrote:

> Hello,
>
> I'm looking or some direction on how to implement the SQL syntax when querying Postgres' composite types. In case you're unfamiliar: the composite types in Postgres are a type that contains attributes which are regular SQL types. The attribute to type relationship is similar to the column to table relationship (except there are no constraints on attributes). A table column can be this composite type. Psycopg2 implements this by instantiating a namedtuple on retrieval or accepting a namedtuple on update/insert (you can also use your own custom class). The saving and retrieval isn't a problem with SQLAlchemy, I just pass through the namedtuple to/from Psycopg2.
>
> What I need to implement is the SQL syntax for querying composite type attributes. Postgres allows this by using the syntax "(table_name.column_name).attribute_name = 'bleh'" or just "(column_name).attribute_name = 'bleh'" when a table identifier is not required. I'm not sure how to go about this because the sql generation needs to change the way the column name is output, would this require subclassing the ColumnClause? I think I just need to know where to override the behaviour of generating the qualified column name in statements.

you'd be doing ColumnElement here which is the more fundamental object.    It's discussed here in "subclassing guidelines": http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#subclassing-guidelines

if you're on 0.8 the integration here can be very smooth, using custom operators (http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators) you could have an approach that looks like:  mytable.c.column.attrs.attribute_name == "value".   The "attrs" namespace would call into your custom ColumnElement.

Since the custom operator API is a total blast to use here's a proof of concept:

Wow thats great, thanks!

There is a problem with this when using declarative and a reflected base. My Column's type get's overrwritten with NullType even though I've set autoreload_replace=False when constructing the table. I am still using the example ReflectedBase that was in the 0.7 documentation (because the new DeferredReflection class in 0.8 raises a NoReferencedTableError when configure_mappers() is called). Any idea how this reflected base is overwriting the type?:

I figured out the problem. It's not finding the table in the metadata (because it's schema qualified in metadata.tables) and so it creates it anew. 

[Tobias Bieniek]

I've tried to extract the basic structure from this sample code and merge it into the GeoAlchemy2 project, but somehow it fails to work for my specific use-case: https://github.com/geoalchemy/geoalchemy2/pull/18

I'm trying something like: select([func.SomeFunction(table.c.some_column, type_=mytype).attr1])

Unfortunately the resulting query string is missing the FROM clause and won't execute. I'd be glad if anyone has an idea how to fix this.

[Michael Bayer]

if you can package up a script with full context that would help, that pullreq appears to add a new type to a file and I don't see how that results in anything happening.

In general if you are using my recipe earlier that features "ColumnElement", that object won't report on its parent "table" for usage in the FROM clause, if you are producing a custom column class you need to set it up correctly for the way it will be used.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Tobias Bieniek]

thanks for the quick reply!

> if you can package up a script with full context that would help, that pullreq appears to add a new type to a file and I don't see how that results in anything happening.

you are right, https://github.com/Turbo87/geoalchemy2/commit/9d09a94c97faab902ca29000c6e985a7b51f7eb1#L1R154 is adding the PGCompositeType class, which is similar to the one in your recipe but with a few simplifications. 

https://github.com/Turbo87/geoalchemy2/commit/9d09a94c97faab902ca29000c6e985a7b51f7eb1#L0R220 is adding the corresponding unit test for that class, which is unfortunately failing at the moment, due to the missing FROM clause.

you should be able to run the test case through "python setup.py nosetests". there will be a few failing functional tests due to the missing postgres DB, but the relevant test in "test_types.py" should be visible too.

> In general if you are using my recipe earlier that features "ColumnElement", that object won't report on its parent "table" for usage in the FROM clause, if you are producing a custom column class you need to set it up correctly for the way it will be used.

could you point me in the right direction how to "set it up correctly"? I'm currently a bit lost in the documentation and code. 

[Michael Bayer]

On Mar 24, 2013, at 5:54 PM, Tobias Bieniek <tobias....@googlemail.com> wrote:

could you point me in the right direction how to "set it up correctly"? I'm currently a bit lost in the documentation and code. 

just send me a standalone test script so I can see what you're trying to do without diving into all of geoalchemy's source

[Tobias Bieniek]

I've added a small standalone script at https://gist.github.com/Turbo87/5233888

The output is "SELECT (SomeFunction("table".foo)).attr1 AS anon_1", without any FROM clause.

[Michael Bayer]

right, ColumnElement doesn't export the "froms" from it's expression by default.

There's a method you can implement to do this, but here is another approach that doesn't use any private APIs:

from sqlalchemy.sql.expression import FunctionElement

class PGCompositeElement(FunctionElement):

    def __init__(self, base, field, type_):

        self.name = field

        super(PGCompositeElement, self).__init__(base)

        self.type = to_instance(type_)

On Mar 24, 2013, at 6:40 PM, Tobias Bieniek <tobias....@googlemail.com> wrote:

I've added a small standalone script at https://gist.github.com/Turbo87/5233888

The output is "SELECT (SomeFunction("table".foo)).attr1 AS anon_1", without any FROM clause.

[Tobias Bieniek]

Sorry, just noticed that I replied directly, instead of to the list. Here are the contents for everyone:

Thanks for you help. Using the FunctionElement worked quite well, but
I had to tweak the constructor and assign the base parameter to
self.key to make it work properly (failing stack trace at
https://gist.github.com/Turbo87/b5f49ffeedb3fa288b4f).

Now I've tried the same approach using the declarative method
(https://gist.github.com/Turbo87/e7fb19b8e532d3e3b6f3) but I get the
next error: "TypeError: Boolean value of this clause is not defined"
(stack trace for that is at
https://gist.github.com/Turbo87/ab5f0ef5159dc85dd0b3 and what I got
out of the debugger is at
https://gist.github.com/Turbo87/bef75287f67c0f1f948a)

I hope I'm not stealing too much of your time. Thanks again for the
great help already!

[Michael Bayer]

well .key on any ColumnElement is supposed to be a string name that Query and others will call upon to assign it a label in the result set.   removing .key, which is unneeded, allows it to work (note expr.clauses):

class PGCompositeElement(expression.FunctionElement):

    def __init__(self, base, field, type_):

        self.name = field

        self.type = to_instance(type_)

        super(PGCompositeElement, self).__init__(base)

@compiles(PGCompositeElement)

def _compile_pgelem(expr, compiler, **kw):

    return '(%s).%s' % (compiler.process(expr.clauses, **kw), expr.name)

[Tobias Bieniek]

Hi Michael, thanks for the explanation on the key attribute. I ended up misunderstanding it at first, but it seems to work now.

I had a few problems when I tried to use a  UserDefinedType (e.g. geoalchemys Geometry) in the typemap, because it complained about a missing "key" attribute. I've fixed that by adding "key = None" to the comparator class though (https://github.com/Turbo87/geoalchemy2/commit/264c7cd578da78c5c5f964ecd44c35a307923643) and it seems to work as expected now :)

[Michael Bayer]

On Mar 26, 2013, at 7:59 PM, Tobias Bieniek <tobias....@googlemail.com> wrote:

Hi Michael, thanks for the explanation on the key attribute. I ended up misunderstanding it at first, but it seems to work now.

I had a few problems when I tried to use a  UserDefinedType (e.g. geoalchemys Geometry) in the typemap, because it complained about a missing "key" attribute. I've fixed that by adding "key = None" to the comparator class though (https://github.com/Turbo87/geoalchemy2/commit/264c7cd578da78c5c5f964ecd44c35a307923643) and it seems to work as expected now :)

Whats the status of Geoalchemy, is the current released version using the 0.8 stuff yet or when is that happening ?

[Kamil Gałuszka]

As far I'm concerned:

GeoAlchemy is for SQLALchemy  =<0.7.x

GeoAlchemy2 if for SQLALchemy =>0.8

On PyPI is GeoAlchemy 2 version 0.1 working with sqlalchemy 0.8

With regards

Kamil Gałuszka

[Tobias Bieniek]

GeoAlchemy is for SQLALchemy  =<0.7.x

GeoAlchemy2 if for SQLALchemy =>0.8

Kamil is mostly right. GeoAlchemy2 v0.1 is published on PyPI now, and GeoAlchemy(1) is also still available. The latter is still compatible with SQLAlchemy 0.8 though, but GeoAlchemy2 has a much easier code base and API. I'm already running it on a production server and it worked very well so far.