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(????)
SQLAlchemy is the ORM of choice for working with relational databases in python. The reason why SQLAlchemy is so popular is because it is very simple to implement, helps you develop your code quicker and doesn't require knowledge of SQL to get started.
SQLAlchemy is great because it provides a good connection / pooling infrastructure; a good Pythonic query building infrastructure; and then a good ORM infrastructure that is capable of complex queries and mappings (as well as some pretty stone-simple ones).
SQLAlchemy is a library that facilitates the communication between Python programs and databases. Most of the times, this library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements.
One of the key aspects of any data science workflow is the sourcing, cleaning, and storing of raw data in a form that can be used upstream. This process is commonly referred to as “Extract-Transform-Load,” or ETL for short.
From the tutorial:
from sqlalchemy import or_ filter(or_(User.name == 'ed', User.name == 'wendy'))
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'))
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