How to use relationships and associations in SQLAlchemy

When working with databases in Python, SQLAlchemy is a powerful tool kit that simplifies the process. One of its key features is managing relationships and associations between different tables. Let’s delve into relationships and associations and how they are implemented in SQLAlchemy.

Relationships in SQLAlchemy

In SQLAlchemy, relationships denote links between tables within a database, facilitating easy navigation between associated tables. There are different types of relationships:

One-to-many relationship

In a one-to-many relationship, each entry in one table can correspond to multiple entries in another.

One-to-many relationship in SQLAlchemy
One-to-many relationship in SQLAlchemy

For instance, consider a database containing User and Post tables, where each user can possess multiple posts. The method of defining such a relationship in SQLAlchemy is as follows:

# class User(Base):
#     __tablename__ = 'users'
#     id = Column(Integer, primary_key=True)
#     name = Column(String)
#     posts = relationship("Post", back_populates="author")
# class Post(Base):
#     __tablename__ = 'posts'
#     id = Column(Integer, primary_key=True)
#     title = Column(String)
#     content = Column(String)
#     author_id = Column(Integer, ForeignKey('users.id'))
#     author = relationship("User", back_populates="posts")
# Create a new user
entry = User(name='Naruto Uzumaki')
# Add some posts for the user
entry.posts = [
    Post(title='Post 1', content='9 Tails Fox Sealer'),
    Post(title='Post 2', content='Hokage of Hidden Leaf Village')
]
# Add the user and posts to the session and commit changes to the database
session.add(entry)
session.commit()
# Query the database to retrieve data
user = session.query(User).filter_by(name='Naruto Uzumaki').first()
print(f'User: {user.name}')
for post in user.posts:
    print(f'Post: {post.title} - {post.content}')

Explanation

  • Lines 1–13: These lines define two SQLAlchemy models, User and Post, representing database tables for users and posts, respectively. The User class has a one-to-many relationship with the Post class through the “posts” attribute.

    • author = relationship("User", back_populates="posts") establishes a bidirectional relationship between the User and Post classes.

    • Similarly, posts = relationship("Post", back_populates="author") sets up a reverse relationship.

    • This enables posts to be linked to their authors through the “author” attribute.

  • Line 16: A new User instance is created.

  • Lines 19–22: Two Post instances are created and associated with the User. These Posts are appended to the user’s “posts” attribute.

  • Lines 25–26: The User and associated Posts are added to the session, and changes are committed to the database.

  • Lines 29–32: The database is queried to retrieve the user object with the name. The User’s name is printed, followed by a loop that prints each post’s title and content associated with the user.

In this instance, the User class establishes a one-to-many association with the Post class via the posts attribute.

Many-to-one relationship

A many-to-one relationship can be viewed as the reverse of a one-to-many relationship.

Many-to-one relationship in SQLAlchemy
Many-to-one relationship in SQLAlchemy

It implies that multiple records in one table can be linked to a solitary record in another. For instance, in our scenario, each Post is linked to only one User.

# class User(Base):
#     __tablename__ = 'users'
#     id = Column(Integer, primary_key=True)
#     name = Column(String)
#     posts = relationship("Post", back_populates="author")
# class Post(Base):
#     __tablename__ = 'posts'
#     id = Column(Integer, primary_key=True)
#     title = Column(String)
#     content = Column(String)
#     author_id = Column(Integer, ForeignKey('users.id'))
#     author = relationship("User", back_populates="posts")
# Create a new user
new_user = User(name='Kakashi Hatake')
session.add(new_user)
session.commit()
# Create a new post associated with the user
new_post = Post(title='Shinobi', content='Hatake clan', author=new_user)
new_post2 = Post(title='Jonin', content='Hidden Leaf Village', author=new_user)
session.add(new_post)
session.add(new_post2)
session.commit()
# Query the user object first
user = session.query(User).filter_by(name='Kakashi Hatake').first()
# Check if the user exists
if user:
    # Query all posts associated with the user
    user_posts = session.query(Post).filter_by(author=user).all()
    # Print all posts and their respective authors
    for post in user_posts:
        print(f'Post: {post.title} - {post.content}')
        print(f'Author: {post.author.name}')
else:
    print("User 'Kakashi Hatake' not found.")

Explanation

  • Lines 1–13: These lines define two SQLAlchemy models, User and Post, representing database tables for users and posts, respectively. The User class has a many-to-one relationship with the Post class through the “posts” attribute.

  • Lines 16–18: Add a new User instance to the session. The changes are then committed to the database.

  • Lines 21–25: Two new Post instances are created and associated with the previously created User. These posts are added to the session and committed to the database.

  • Lines 27–28: The User object is queried from the database using the name.

  • Lines 31–40: If the User is found, all posts associated with that User are queried from the database. Each Post and its respective author’s name are then printed. If the User is not found, a message indicating so is printed.

Association in SQLAlchemy

In SQLAlchemy, an association is a way to establish a many-to-many relationship between two tables without the need for an explicit intermediate table. Instead of creating a separate table to represent the relationship between the two entities, SQLAlchemy allows you to define a relationship directly between the two classes involved.

Associations are useful when you have a relationship between two entities that don’t warrant its independent table with additional attributes. They simplify the modeling process and make the code more straightforward.

Many-to-many relationship

In a many-to-many relationship, entries in one table may be linked to multiple entries in another, and vice versa.

Many-to-many relationships in SQLAlchemy
Many-to-many relationships in SQLAlchemy

For instance, let’s take a database with tables for Books and Authors. Multiple authors may author a book; likewise, an author may have contributed to multiple books. Here’s how you establish such a connection:

# class Author(Base):
#     __tablename__ = 'authors'
#     id = Column(Integer, primary_key=True)
#     name = Column(String)
#     books = relationship("Book", secondary=authors_books, back_populates="authors")
# class Book(Base):
#     __tablename__ = 'books'
#     id = Column(Integer, primary_key=True)
#     title = Column(String)
#     authors = relationship("Author", secondary=authors_books, back_populates="books")
# Create new authors
author1 = Author(name='Naruto Uzumaki')
author2 = Author(name='Sasuke Uchiha')
session.add_all([author1, author2])
session.commit()
# Create new books associated with authors
book1 = Book(title='Secret Scroll of Hidden leaf')
book2 = Book(title='Sharingan: The power within eyes')
book1.authors = [author1, author2]
book2.authors = [author2]
session.add_all([book1, book2])
session.commit()
# Query the database to retrieve authors and their books
authors = session.query(Author).all()
for author in authors:
    print(f'Author: {author.name}')
    for book in author.books:
        print(f' - Book: {book.title}')

In this example, the Author and Book classes have a many-to-many relationship through the authors_books association table.

Explanation

  • Lines 1–11: These lines define two SQLAlchemy models, Author and Book, representing database tables for authors and books, respectively. They establish a many-to-many relationship between authors and books through the intermediary table authors_books, with each Author having multiple Books and vice versa.

    • authors = relationship(“Author”, secondary=authors_books, back_populates=“books”) sets up a many-to-many relationship between the Author and Book classes.

    • It utilizes the intermediary table authors_books to manage this relationship.

    • Through this relationship, authors can be associated with multiple books via the “books” attribute.

    • The back_populates parameter ensures that changes made to one side of the relationship are reflected on the other, maintaining consistency.

  • Lines 14–17: Two new author instances are added to the session.

  • Lines 20–23: Two new book instances are created and associated with the respective authors. These associations are made by assigning the authors to each book’s “authors” attribute.

  • Lines 24–25: The newly created books are added to the session.

  • Lines 28–33: The database is queried to retrieve all authors, and each author’s name is printed, followed by a nested loop to print all associated books’ titles.

Conclusion

Understanding relationships and associations in SQLAlchemy is essential for effective database management in Python. Whether it’s a one-to-many, many-to-one, or many-to-many relationship, SQLAlchemy provides the tools to define and navigate these connections effortlessly. You can easily build robust and scalable database applications by leveraging these features.

Unlock your potential: SQLAlchemy fundamentals series, all in one place!

If you’ve missed any part of the series, you can always go back and check out the previous Answers:

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved