Using OR in SQLAlchemy

Question

I ve looked through the docs and I cant seem to find out how to do an OR query in SQLAlchemy  I just want to do this query   SELECT address FROM addressbook WHERE city  boston  AND  lastname  bulger  OR firstname  whitey     Should be something like  addr   session query AddressBook  filter City     boston   filter

User · Answer

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'))

Note that the parentheses are not optional due to the precedence of the bitwise operators.

So your whole query could look like this:

addr = session.query(AddressBook) \
    .filter(AddressBook.city == "boston") \
    .filter((AddressBook.lastname == 'bulger') | (AddressBook.firstname == 'whitey'))

User · Answer

From the tutorial   from sqlalchemy import or  filter or  User name     ed   User name     wendy

User · Answer

or    function can be useful in case of unknown number of OR query components    For example  let s assume that we are creating a REST service with few optional filters  that should return record if any of filters return true  On the other side  if parameter was not defined in a request  our query shouldn t change  Without or    function we must do something like this   query   Book query if filter title and filter author      query   query filter  Book title ilike filter title    Book author ilike filter author    else if filter title      query   query filter Book title ilike filter title   else if filter author      query   query filter Book author ilike filter author     With or    function it can be rewritten to   query   Book query not null filters      if filter title      not null filters append Book title ilike filter title   if filter author      not null filters append Book author ilike filter author    if len not null filters   gt  0      query   query filter or   not null filters

User · Answer

This has been really helpful  Here is my implementation for any given table   def sql replace self  tableobject  dictargs         missing check of table object is valid     primarykeys    key name for key in inspect tableobject  primary key       filterargs          for primkeys in primarykeys          if dictargs primkeys  is not None              filterargs append getattr db RT eqmtvsdata  primkeys     dictargs primkeys           else              return      query   select  db RT eqmtvsdata   where and   filterargs        if self r ExecuteAndErrorChk2 query  primarykeys 0   is not None            update         filter   and   filterargs          query   tableobject   table   update   values dictargs  where filter          return self w ExecuteAndErrorChk2 query       else          query   tableobject   table   insert   values dictargs          return self w ExecuteAndErrorChk2 query     example usage inrow     eqmtvs id   eqmtvsid   datetime   dtime   param id   paramid   self sql replace tableobject db RT eqmtvsdata  dictargs inrow

[python] Using OR in SQLAlchemy

Examples related to python

Examples related to sqlalchemy