Thanks Michael, Writing a big list of conditions and combining them with and_(*conditions) worked well. I was indeed querying like this before:
for condition in conditions: q = q.filter(condition) print q On Friday, January 18, 2013 6:00:04 PM UTC-5, Michael Bayer wrote: > > > On Jan 18, 2013, at 4:15 PM, [email protected] <javascript:> wrote: > > I haven't boiled this down to a short test case yet, but when my WHERE > clause gets especially long I start getting the "recursion depth exceeded" > exception. Is this a well-known limitation of sqlalchemy? We're running > this query in production currently without SQLAlchemy, and it performs > fine, but perhaps I need to look for another approach... > > If I keep the filter condition relatively short, my query looks like this > and runs fine (with fake columns start_date, X, Y, and Z on table T): > > SELECT X, sum(Z) AS Z > FROM T > WHERE T.start_date >= :start_date_1 > AND T.start_date <= :start_date_2 > AND NOT (T.X = :X_1 AND T.Y = :Y_1) > AND NOT (T.X = :X_2 AND T.Y = :Y_2) > AND NOT (T.X = :X_3 AND T.Y = :Y_3) > GROUP BY T.X > > However, if I make the filter() clause very long (over 150 AND NOT... > clauses), I start getting exceptions with this stack trace: > > > Always amazing how many wacky new problems come around. Well, the > compilation of these clauses is pretty straightforward, using a recursive > traversal scheme. So if you give Python a tree structure of more than 1000 > nodes deep and do such a traversal, this is the error you'd get, and I > suppose it's sort of "well known", depends on what perspective you're > coming from. > > So this indicates you're creating a structure that is nested this deeply. > Which is to say, really deep ! > > This could happen if you're doing the AND's using a nesting pattern of one > at a time like this: > > from sqlalchemy.sql import column > > root = column('x') == 5 > current = root > > for i in xrange(200): > current = current & (column('x') == 5) > > print current > > > because that's really and_(expr, and_(expr, and_(expr, and_( for 200 > times... ))). > > But if you flatten out the and_() you can get this: > > from sqlalchemy.sql import column, and_ > > expr = [column('x') == 5] > for i in xrange(200): > expr.append(column('x') == 5) > > expr = and_(*expr) > > print expr > > then you have a flat structure, and you're fine. > > So we could modify our and_()/or_ construct to open itself up this way, > that is, as it's built, it flattens out the nesting, though maybe for now > there's a way you can build up using one big and_() block. > > In fact to flatten out the nesting is something you could enable across > the board here, and you can see why I'm hesitant to build this in by > default as it adds lots of isinstance() and other expensive checks, but you > can add this to your app as a quick fix (just run this anywhere at import > time to redefine how and_() and or_() are rendered): > > from sqlalchemy.ext.compiler import compiles > from sqlalchemy.sql.expression import BooleanClauseList > > @compiles(BooleanClauseList) > def flatten_boolean_clause_list(clauselist, compiler, **kw): > op = clauselist.operator > flattened = [] > rewrite = False > stack = list(clauselist.clauses) > while stack: > elem = stack.pop(0) > if isinstance(elem, BooleanClauseList) and elem.operator is op: > stack[:0] = elem.clauses > rewrite = True > else: > flattened.append(elem) > if rewrite: > clauselist = BooleanClauseList(operator=op, *flattened) > return compiler.visit_clauselist(clauselist, **kw) > > then the original test passes because we've rewritten the nested list as a > flat list. Basically the "recursion" is replaced by the stack based > traversal we do here. > > or even quicker, you could just increase your recursion depth. It > defaults to 1000, so here's 10000, do this before you try to run the SQL: > > import sys > sys.setrecursionlimit(10000) > > > > > > > > > > > > File "test.py", line 350, in do_test > print q > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", > line 3031, in __str__ > return str(self._compile_context().statement) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py", line > 1790, in __str__ > return unicode(self.compile()).encode('ascii', 'backslashreplace') > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py", line > 1778, in compile > return self._compiler(dialect, bind=bind, **kw) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py", line > 1784, in _compiler > return dialect.statement_compiler(dialect, self, **kw) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 277, in __init__ > engine.Compiled.__init__(self, dialect, statement, **kwargs) > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", > line 705, in __init__ > self.string = self.process(self.statement) > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", > line 724, in process > return obj._compiler_dispatch(self, **kwargs) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line > 72, in _compiler_dispatch > return getter(visitor)(self, **kw) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 941, in visit_select > t = select._whereclause._compiler_dispatch(self, **kwargs) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line > 72, in _compiler_dispatch > return getter(visitor)(self, **kw) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 477, in visit_clauselist > for c in clauselist.clauses) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 475, in <genexpr> > s for s in > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 477, in <genexpr> > for c in clauselist.clauses) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line > 72, in _compiler_dispatch > return getter(visitor)(self, **kw) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 477, in visit_clauselist > for c in clauselist.clauses) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 475, in <genexpr> > s for s in > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 477, in <genexpr> > for c in clauselist.clauses) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line > 72, in _compiler_dispatch > return getter(visitor)(self, **kw) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 477, in visit_clauselist > for c in clauselist.clauses) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 475, in <genexpr> > s for s in > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 477, in <genexpr> > for c in clauselist.clauses) > ... > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 475, in <genexpr> > s for s in > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 477, in <genexpr> > for c in clauselist.clauses) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line > 72, in _compiler_dispatch > return getter(visitor)(self, **kw) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 477, in visit_clauselist > for c in clauselist.clauses) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 475, in <genexpr> > s for s in > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line > 477, in <genexpr> > for c in clauselist.clauses) > File > "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line > 72, in _compiler_dispatch > return getter(visitor)(self, **kw) > RuntimeError: maximum recursion depth exceeded > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/FeiLu3iVqisJ. > To post to this group, send email to [email protected]<javascript:> > . > To unsubscribe from this group, send email to > [email protected] <javascript:>. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZEg--8APCO4J. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
