Re: [sqlalchemy] column_property for correlated subquery
I think we need to just get column_property to work as a string with declarative. Sent from my iPhone On Mar 19, 2013, at 6:10 AM, millerdev miller...@gmail.com wrote: __declare_last__ works. Thanks Mike. Would it make sense to add a property decorator similar to @declared_attr (maybe @declared_last)? Aesthetically, it seems nicer to define each property separately than to pile them all inside a __declared_last__ function. Of course that could introduce a new ordering problem, so __declared_last__ would still be necessary in cases where the properties must to be defined in a particular order (I don't have that situation now, but can imagine it). ~ Daniel -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@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. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@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.
[sqlalchemy] column_property for correlated subquery
Hi, Using declarative here, and I'm trying to create a column_property with a correlated subquery that returns a count of records with a matching value in some other column. Here's what I've tried. Option 1 is the best, option 2 is ugly but second best, option 3 is not a good option since there are many other classes involved and the place where I'd need to put that code is far away from where it logically belongs. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import * from sqlalchemy.ext.declarative import declared_attr Base = declarative_base() option = 1 class Foo(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) bar_id = Column(Integer, ForeignKey(bar.id)) name = Column(String) if option == 1: # does not work (see first traceback below) @declared_attr def name_count(cls): clx = aliased(cls) return column_property( select(func.count([clx.id])) .where(clx.name == cls.name) .correlate(cls.__table__)) if option == 2: # does not work (see second traceback below) _foo = aliased(Foo) Foo.name_count = column_property( select([func.count(_foo.id)]) .where(_foo.name == Foo.name) .correlate(Foo.__table__)) class Bar(Base): __tablename__ = 'bar' id = Column(Integer, primary_key=True) name = Column(String) if option == 3: # works, but really not where I want to put this code _foo = aliased(Foo) Foo.name_count = column_property( select([func.count(_foo.id)]) .where(_foo.name == Foo.name) .correlate(Foo.__table__)) Option 1 traceback: Traceback (most recent call last): File temp/example.py, line 8, in module class Foo(Base): File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1348, in __init__ _as_declarative(cls, classname, cls.__dict__) File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1181, in _as_declarative value = getattr(cls, k) File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1554, in __get__ return desc.fget(cls) File temp/example.py, line 15, in name_count clx = aliased(cls) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 385, in aliased return AliasedClass(element, alias=alias, name=name, adapt_on_names=adapt_on_names) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 298, in __init__ self.__mapper = _class_to_mapper(cls) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 673, in _class_to_mapper raise exc.UnmappedClassError(class_or_mapper) sqlalchemy.orm.exc.UnmappedClassError: Class '__main__.Foo' is not mapped Option 2 traceback: Traceback (most recent call last): File temp/example.py, line 16, in module select([func.count(_foo.id)]) File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 1229, in __call__ return func(*c, **o) File .../python2.7/site-packages/sqlalchemy/sql/functions.py, line 16, in __call__ args = [_literal_as_binds(c) for c in args] File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 1440, in _literal_as_binds return element.__clause_element__() File .../python2.7/site-packages/sqlalchemy/orm/attributes.py, line 117, in __clause_element__ return self.comparator.__clause_element__() File .../python2.7/site-packages/sqlalchemy/util/langhelpers.py, line 506, in oneshot result = self.fget(obj, *args, **kw) File .../python2.7/site-packages/sqlalchemy/orm/properties.py, line 156, in __clause_element__ return self.adapter(self.prop.columns[0]) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 334, in __adapt_element return self.__adapter.traverse(elem).\ File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 185, in traverse return replacement_traverse(obj, self.__traverse_options__, replace) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 281, in replacement_traverse obj = clone(obj, **opts) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 270, in clone newelem = replace(elem) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 182, in replace e = v.replace(elem) File .../python2.7/site-packages/sqlalchemy/sql/util.py, line 720, in replace return self._corresponding_column(col, True) File .../python2.7/site-packages/sqlalchemy/sql/util.py, line 695, in _corresponding_column require_embedded=require_embedded) File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 2492, in corresponding_column if self.c.contains_column(column): File .../python2.7/site-packages/sqlalchemy/util/langhelpers.py, line 485, in __get__ obj.__dict__[self.__name__] = result = self.fget(obj) File
Re: [sqlalchemy] column_property for correlated subquery
the typical hook for this kind of thing is __declare_last__, works after everything has been compiled. you might have an easier time also (or might not matter) if you build against the Table (i.e. Foo.__table__.alias(), etc.) instead of firing up the class with aliased(Foo), but if you're in __declare_last__ might not matter anyway. On Mar 18, 2013, at 12:20 PM, millerdev miller...@gmail.com wrote: Hi, Using declarative here, and I'm trying to create a column_property with a correlated subquery that returns a count of records with a matching value in some other column. Here's what I've tried. Option 1 is the best, option 2 is ugly but second best, option 3 is not a good option since there are many other classes involved and the place where I'd need to put that code is far away from where it logically belongs. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import * from sqlalchemy.ext.declarative import declared_attr Base = declarative_base() option = 1 class Foo(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) bar_id = Column(Integer, ForeignKey(bar.id)) name = Column(String) if option == 1: # does not work (see first traceback below) @declared_attr def name_count(cls): clx = aliased(cls) return column_property( select(func.count([clx.id])) .where(clx.name == cls.name) .correlate(cls.__table__)) if option == 2: # does not work (see second traceback below) _foo = aliased(Foo) Foo.name_count = column_property( select([func.count(_foo.id)]) .where(_foo.name == Foo.name) .correlate(Foo.__table__)) class Bar(Base): __tablename__ = 'bar' id = Column(Integer, primary_key=True) name = Column(String) if option == 3: # works, but really not where I want to put this code _foo = aliased(Foo) Foo.name_count = column_property( select([func.count(_foo.id)]) .where(_foo.name == Foo.name) .correlate(Foo.__table__)) Option 1 traceback: Traceback (most recent call last): File temp/example.py, line 8, in module class Foo(Base): File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1348, in __init__ _as_declarative(cls, classname, cls.__dict__) File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1181, in _as_declarative value = getattr(cls, k) File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1554, in __get__ return desc.fget(cls) File temp/example.py, line 15, in name_count clx = aliased(cls) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 385, in aliased return AliasedClass(element, alias=alias, name=name, adapt_on_names=adapt_on_names) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 298, in __init__ self.__mapper = _class_to_mapper(cls) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 673, in _class_to_mapper raise exc.UnmappedClassError(class_or_mapper) sqlalchemy.orm.exc.UnmappedClassError: Class '__main__.Foo' is not mapped Option 2 traceback: Traceback (most recent call last): File temp/example.py, line 16, in module select([func.count(_foo.id)]) File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 1229, in __call__ return func(*c, **o) File .../python2.7/site-packages/sqlalchemy/sql/functions.py, line 16, in __call__ args = [_literal_as_binds(c) for c in args] File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 1440, in _literal_as_binds return element.__clause_element__() File .../python2.7/site-packages/sqlalchemy/orm/attributes.py, line 117, in __clause_element__ return self.comparator.__clause_element__() File .../python2.7/site-packages/sqlalchemy/util/langhelpers.py, line 506, in oneshot result = self.fget(obj, *args, **kw) File .../python2.7/site-packages/sqlalchemy/orm/properties.py, line 156, in __clause_element__ return self.adapter(self.prop.columns[0]) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 334, in __adapt_element return self.__adapter.traverse(elem).\ File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 185, in traverse return replacement_traverse(obj, self.__traverse_options__, replace) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 281, in replacement_traverse obj = clone(obj, **opts) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 270, in clone newelem = replace(elem) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 182, in replace e = v.replace(elem) File
