On Mar 5, 2013, at 9:45 AM, Jason <[email protected]> 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: 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" -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
