I've been trying to get SQLAlchemy to work with Postgres composite types
for the last couple days with no luck. I've implemented the PGCompositeType
that Michael created in another post
(https://groups.google.com/forum/#!searchin/sqlalchemy/postgres$20composite/sqlalchemy/f9BPVHfdvbg/HqEbDUO9UOMJ),
however, I cannot seem to get this to work. I'm also a bit unclear on how
to actually query based on the composite type (I'm using declarative table
definitions).
Here is what I have so far - the column definition is taken from Michael
Bayer's post:
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, field, type_):
self.name = field
super(PGCompositeElement, self).__init__(base)
self.type = to_instance(type_)
@compiles(PGCompositeElement)
def _compile_pgelem(expr, compiler, **kw):
return '(%s).%s' % (compiler.process(expr.clauses, **kw), expr.name)
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
# My column and tables definitions:
TopoGeom = PGCompositeType('topo_geom', {'topology_id': Integer,
'layer_id': Integer, 'id': Integer, 'type': Integer})
class Trail(Base):
__tablename__ = 'trail'
id = Column(Integer, primary_key=True)
topo_geom = Column(TopoGeom)
When I query using the following, I get an attribute error since *topo_geom*
is a string.
result = self.session.query(Trail).first()
print result.topo_geom.attrs.topology_id
How do I actually get the column attributes from a result (for example,
*topology_id*)? Additionally, is it possible to query and join other tables
based on the composite fields?
--
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.
For more options, visit https://groups.google.com/d/optout.
