Menu
I'm trying to do a very simple search from a list of terms
terms = ['term1', 'term2', 'term3'] How do I programmatically go through the list of terms and construct the conditions from the list of terms so that I can make the query using filter and or_ or _and?
query.filter(or_(#something constructed from terms)) 
770
lazy = 'dynamic': When querying with lazy = 'dynamic', however, a separate query gets generated for the related object. If you use the same query as 'select', it will return: You can see that it returns a sqlalchemy object instead of the city objects.
_sa_instance_state is a non-database-persisted value used by SQLAlchemy internally (it refers to the InstanceState for the instance.
Also "IN" is limited to a set of ~1000 entries, while "OR" is not. SQLAlchemy overloads the bitwise operators &, | and ~ so instead of the ugly and hard-to-read prefix syntax with or_ () and and_ () (like in Bastien's answer) you can use these operators: .filter ( (AddressBook.lastname == 'bulger') | (AddressBook.firstname == 'whitey'))
How can I create complex conditions (called ' Query Filters ') by nesting or indenting them together, and using AND and OR? This can be done differently depending on whether your Preferences > Web Intelligence > Modify are Applet or HTML. HTML - Special Nested Filters button available. Open the report in DESIGN mode.
Click the Edit Data Provider icon and the Query Panel opens. Drag the Object that you want to filter on into the Query Filters Pane and drop it anywhere below the pre-existing filters. Drag your second Object into the Query Filters Pane and just slightly overlap the first one and then drop it. Both Objects should indent.
Drag an Object that you want to filter on and drop it when you see the red line next to OR. Drag the second object and drop it when you see the red line appear above or below the previous filter. You now have a nested query filter.
If you have a list of terms and want to find rows where a field matches one of them, then you could use the in_() method:
terms = ['term1', 'term2', 'term3'] query.filter(Cls.field.in_(terms)) If you want to do something more complex, then or_() and and_() take ClauseElement objects as parameters. ClauseElement and its subclasses basically represent the SQL AST of your query. Typically, you create clause elements by invoking a comparison operator on Column or InstrumentedAttribute objects:
# Create the clause element clause = (users_table.columns['name'] == "something") # you can also use the shorthand users_table.c.name # The clause is a binary expression ... print(type(clause)) # <class 'sqlalchemy.sql.expression._BinaryExpression'> # ... that compares a column for equality with a bound value. print(type(clause.left), clause.operator, type(clause.right)) # <class 'sqlalchemy.schema.Column'>, <built-in function eq>, # <class 'sqlalchemy.sql.expression._BindParamClause'> # str() compiles it to SQL print(str(clause)) # users.name = ? # You can also do that with ORM attributes clause = (User.name == "something") print(str(clause)) # users.name = ? You can handle clause elements representing your conditions like any Python objects, put them into lists, compose them into other clause elements, etc. So you can do something like this:
# Collect the separate conditions to a list conditions = [] for term in terms: conditions.append(User.name == term) # Combine them with or to a BooleanClauseList condition = or_(*conditions) # Can now use the clause element as a predicate in queries query = query.filter(condition) # or to view the SQL fragment print(str(condition)) # users.name = ? OR users.name = ? OR users.name = ? If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
