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)