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)