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 ".../python2.7/site-packages/sqlalchemy/sql/expression.py", line
2558, in columns
self._populate_column_collection()
File ".../python2.7/site-packages/sqlalchemy/sql/expression.py", line
3704, in _populate_column_collection
col._make_proxy(self)
File ".../python2.7/site-packages/sqlalchemy/schema.py", line 1103, in
_make_proxy
fk = [ForeignKey(f.column) for f in self.foreign_keys]
File ".../python2.7/site-packages/sqlalchemy/util/langhelpers.py", line
485, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File ".../python2.7/site-packages/sqlalchemy/schema.py", line 1392, in
column
tname)
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column
'foo.bar_id' could not find table 'bar' with which to generate a foreign
key to target column 'id'
It looks like the problem is that mapper state is not being initialized
properly prior to setting up the column_property in the first two options.
Is there another type of property similar to declared_attr that will allow
me to add the column_property some place in the initialization sequence
where mappers have been sufficiently initialized to allow constructing a
select statement?
~ 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.
