Search⌘ K
Home
Courses
Quick Start Full Stack Web Development

Connecting Multiple Tables

Explore how to connect multiple database tables using SQLAlchemy joins to query paired records efficiently. Understand the difference between querying without joins and using join conditions. Learn to filter results based on specific criteria, such as author birth year and book availability. This lesson equips you with practical skills to manage complex queries in full stack web applications.

We'll cover the following...

Issues of having multiple tables

Suppose we had a second table that paired up the author with their birth year.

Python 3.8
class BirthYear(base):
    __tablename__ = 'birth_years'
    author = Column(types.String(length=50), primary_key=True)
    birth_year = Column(types.Integer, nullable=False)
## Add the new table to the database
base.metadata.create_all(db)
session.add_all([
    BirthYear(author='Lewis Carroll', birth_year=1832),
    BirthYear(author='Kurt Vonnegut', birth_year=1922),
    BirthYear(author='Annie Dillard', birth_year=1945)
])

Now suppose we need to select all available titles from authors born in the 20th century. One way to do this would be to find all such authors and look them up in the other Book table one by one. Or, we could get the authors as a list and use the in_ operator described in the ...