sqlalchemy.orm.Query.filter_by support for querying tables

[Andrey Popp]

I use intermix of orm and non-orm functionality of SQLAlchemy in my application, but often I use sqlalchemy.orm.Query to query both for entities and for rows from tables. Unfortunately Query.filter_by() method doesn't support tables as "main entities" of Query.

from sqlalchemy import MetaData, create_engine, Table, Column, Integer
from sqlalchemy.orm import sessionmaker

e = create_engine("sqlite://")
m = MetaData(bind=e)
t = Table("t", m, Column("a", Integer))
S = sessionmaker(bind=e)
s = S()
q = s.query(t).filter_by(a=12)

which gives me:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/Cellar/python/2.7.2/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1132, in filter_by
    for key, value in kwargs.iteritems()]
  File "/usr/local/Cellar/python/2.7.2/lib/python2.7/site-packages/sqlalchemy/orm/util.py", line 561, in _entity_descriptor
    entity = entity.class_
AttributeError: 'NoneType' object has no attribute 'class_'

[Mike Bayer]

potential patch:

diff -r 2dbeeff50b7ccc6f47b2816a59f99f051fdabc8c lib/sqlalchemy/orm/query.py
--- a/lib/sqlalchemy/orm/query.py   Sun Feb 05 16:58:32 2012 -0500
+++ b/lib/sqlalchemy/orm/query.py   Tue Feb 07 09:23:26 2012 -0500
@@ -276,6 +276,7 @@
         return self._select_from_entity or \
             self._entity_zero().entity_zero

+
     @property
     def _mapper_entities(self):
         # TODO: this is wrong, its hardcoded to "primary entity" when
@@ -3086,8 +3087,9 @@
 class _ColumnEntity(_QueryEntity):
     """Column/expression based entity."""

-    def __init__(self, query, column):
+    def __init__(self, query, column, namespace=None):
         self.expr = column
+        self.namespace = namespace

         if isinstance(column, basestring):
             column = sql.literal_column(column)
@@ -3106,7 +3108,7 @@
             for c in column._select_iterable:
                 if c is column:
                     break
-                _ColumnEntity(query, c)
+                _ColumnEntity(query, c, namespace=column)

             if c is not column:
                 return
@@ -3147,12 +3149,14 @@

         if self.entities:
             self.entity_zero = list(self.entities)[0](0)
+        elif self.namespace is not None:
+            self.entity_zero = self.namespace
         else:
             self.entity_zero = None

     @property
     def entity_zero_or_selectable(self):
-        if self.entity_zero:
+        if self.entity_zero is not None:
             return self.entity_zero
         elif self.actual_froms:
             return list(self.actual_froms)[0](0)
diff -r 2dbeeff50b7ccc6f47b2816a59f99f051fdabc8c lib/sqlalchemy/orm/util.py
--- a/lib/sqlalchemy/orm/util.py    Sun Feb 05 16:58:32 2012 -0500
+++ b/lib/sqlalchemy/orm/util.py    Tue Feb 07 09:23:26 2012 -0500
@@ -557,7 +557,9 @@
     attribute.

     """
-    if not isinstance(entity, (AliasedClass, type)):
+    if isinstance(entity, expression.FromClause):
+        entity = entity.c
+    elif not isinstance(entity, (AliasedClass, type)):
         entity = entity.class_

     try:

more comprehensive test:

from sqlalchemy import MetaData, create_engine, Table, Column, Integer
from sqlalchemy.orm import sessionmaker

e = create_engine("sqlite://")
m = MetaData(bind=e)
t = Table("t", m, Column("a", Integer))
r = Table("r", m, Column("a", Integer), Column("b", Integer))
S = sessionmaker(bind=e)
s = S()

q = s.query(t).filter_by(a=5).join(r, t.c.a==r.c.b).filter_by(a=15)
print q

[Mike Bayer]

38b7cef9828acc360c6a356a8a6bbf4f85a04348

[Mike Bayer]

Removing milestone: 0.7.6 (automated comment)