SQLAlchemy Query Examples
Some of the most commonly used operators in SQLAlchemy are given below with examples:
AND
from sqlalchemy import and_
session.query(User).filter(and_(User.name == "Danny", User.age == 20))
EQUALS ==
session.query(User).filter(User.id == 1)
NOT EQUALS !=
session.query(User).filter(User.id != 1)
IN
session.query(User).filter(User.id.in_([1, 2, 3, 4]))
NOT IN
session.query(User).filter(~User.id.in_([1, 2, 3, 4]))
IS NULL
session.query(User).filter(User.name == None)
IS NOT NULL
session.query(User).filter(User.name != None)
LIKE
session.query(User).filter(User.name.like("%Danny%"))
MATCH
session.query(User).filter(User.name.match("Danny"))
OR
from sqlalchemy import or_
session.query(User).filter(or_(User.name == "Danny", User.name == "Jenifer",
User.name == "Peter"))
INSERT Single Object
session.add(user)
session.commit()
session.close()
INSERT List of Objects
session.add_all(user_list)
session.commit()
session.close()
UPDATE
session.query(User).filter(User.id == 1).update({User.name: "Lee", User.active: True},
synchronize_session=False)
session.flush()
session.commit()
JOIN Two Tables
session.query(User).join(Address, User.id == Address.user_id).filter(User.id == 1)