Some examples of database queries with SQLAlchemy 2.0

partial_db_commands.py

# lots of code missing here! Just showing db commands 

# my model - table named socks - the model matches the real table in your db 
class Sock(db.Model):
    __tablename__ = 'socks'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    style = db.Column(db.String)
    color = db.Column(db.String)
    quantity = db.Column(db.Integer)
    price = db.Column(db.Float)
    updated = db.Column(db.String)

# get all rows for socks with the style 'knee-high' 
socks = db.session.execute(db.select(Sock)
    .filter_by(style='knee-high')
    .order_by(Sock.name)).scalars()

# get all rows for socks with the color 'blue' AND a price greater than or equal to 4.0
socks = db.session.execute(db.select(Sock)
    .where(Sock.color == 'blue')
    .where(Sock.price >= 4.0)
    .order_by(Sock.name)).scalars()

# get all the rows in the table, order by name 
socks = db.session.execute(db.select(Sock)
    .order_by(Sock.name)).scalars()

# get only one sock, using its ID 
# note that sock_id would have to be assigned a value BEFORE THIS, in this Python script
the_sock = db.get_or_404(Sock, sock_id)

# regular SQL has this: WHERE color LIKE '%blue%' --
# it will get all records that include 'blue' in the color column
# this is the equivalent 
search_string = "%{}%".format('blue')
socks = db.session.execute(db.select(Sock)
    .where(Sock.color.like(search_string))
    .order_by(Sock.price)).scalars()

# .filter() also works for that
socks = db.session.execute(db.select(Sock)
    .filter(Sock.color.like('%blue%'))
    .order_by(Sock.price)).scalars()

# get each _unique_ value in the style column - 
# even if 'knee-high' appears 20 times in the table,
# it appears only ONCE in this result - no rows, just a list of values
styles = db.session.execute(db.select(Sock)
    .with_only_columns(Sock.style).distinct())

# suggest others