The Basic SQL Queries of SQLAlchemy

Explore how to execute fundamental SQL queries using SQLAlchemy in Python. Understand simple SELECT statements, JOIN operations, and pattern matching with LIKE. This lesson prepares you to confidently manipulate and query databases with Python's popular ORM and guides you to further resources for advanced queries.

We'll cover the following...

Wrapping Up

Python

# queries.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# from table_def import Album, Artist
engine = create_engine('sqlite:///mymusic.db', echo=True)
# create a Session
Session = sessionmaker(bind=engine)
session = Session()
# how to do a SELECT * (i.e. all)
res = session.query(Artist).all()
for artist in res:
    print(artist.name)
# how to SELECT the first result
res = session.query(Artist).filter(Artist.name=="Newsboys").first()
# how to sort the results (ORDER_BY)
res = session.query(Album).order_by(Album.title).all()
for album in res:
    print(album.title)
# how to do a JOINed query
qry = session.query(Artist, Album)
qry = qry.filter(Artist.id==Album.artist_id)
artist = qry.filter(Album.title=="Step Up to the Microphone").first()
album = qry.filter(Album.title=="Step Up to the Microphone").first()
# how to use LIKE in a query
res = session.query(Album).filter(Album.publisher.like("S%a%")).all()
for item in res:
    print(item.publisher)