The higher abstraction of SQLAlchemy ORM allows us to work with our objects as we usually do in Python. We do not need to think much about tables and relationships – SQLAlchemy ORM does this work for us.
This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub.
Pre-requisites
The examples in this post build on top of the bookstore example from the last post for SQLAlchemy ORM.
We need once more the setup_db() and some helper methods to create our objects:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
import os from sqlalchemy.orm import joinedload import data.db_session as db_session from data.__all_models import Book, Publisher, Author, BookDetails def setup_db(): db_file = os.path.join( os.path.dirname(__file__), 'db', 'Bookstore.sqlite') db_session.global_init(db_file) def create_publisher(name): publisher = Publisher() publisher.name = name return publisher def create_book(title, isbn, pages, publisher): book = Book() book.title = title book.isbn = isbn book.pages = pages book.publisher = publisher return book def create_author(first, last): author = Author() author.first_name = first author.last_name = last return author def create_book_details(cover, book): details = BookDetails() details.book = book details.cover = cover return details |
Important: It is easy to overlook, but when we create a relationship between two objects we use the whole object and not its id property. This allows SQLAlchemy ORM to do its magic in the background and we can add objects as we like.
Creating the example data
For the examples on how to retrieve data we need a publisher, two books, three authors and a bookdetail:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
def create_example_data(): session = db_session.factory() manning = create_publisher("Manning Publications") session.add(manning) # one-to-many bookA = create_book("The Mikado Method", "9781617291210", 245, manning) session.add(bookA) bookB = create_book("Specification by Example", "9781617290084", 249, manning) session.add(bookB) # many-to-many authorA = create_author("Ola", "Ellnestam") session.add(authorA) authorB = create_author("Daniel", "Brolund") session.add(authorB) authorC = create_author("Gojko", "Adzic") session.add(authorC) bookA.authors.append(authorA) bookA.authors.append(authorB) bookB.authors.append(authorC) # one-to-one detail = create_book_details("477597.jpg", bookA) session.add(detail) session.commit() return manning.id, bookA.id, bookB.id |
A few things to point out:
- We need to call session.commit() at some time, otherwise SQLAlchemy will not persist our objects.
- Whenever this method runs, it creates a new set of objects and with different primary key values.
- For many-to-many relations we can use the collection property at either end (bookA.authors works as well as authorA.books)
- The method returns multiple values with a tuple
A publisher and his books (one-to-many)
We can load the publisher and its books with this code:
|
1 2 3 4 5 6 7 8 9 10 |
def show_book_and_publisher(publisher_id): session = db_session.factory() publisher = session.query(Publisher).\ options(joinedload("books")).\ filter(Publisher.id == publisher_id).first() for b in publisher.books: print(f"{b.title} by {b.publisher}") session.close() |
The Mikado Method by <Publisher 43 (Manning Publications)>
Specification by Example by <Publisher 43 (Manning Publications)>
The method options(joinedload(“books”)) tells SQLAlchemy to eager load the Book table. This saves us a round trip to the database when we access the publisher.books property the first time. The next post will look at eager loading in more detail.
Authors of a book (many-to-many)
To show all authors of a book we can start at the book and load the authors with this command:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
def show_authors_of_book(bookA_id, bookB_id): session = db_session.factory() books = session.query(Book).\ options(joinedload("authors")).\ filter(Book.id.in_([bookA_id, bookB_id])).all() for b in books: print(b) for a in b.authors: print(f"\t{a}") session.close() |
<Book 87 (The Mikado Method 9781617291210) 245>
<Author 89 (Ola Ellnestam)>
<Author 90 (Daniel Brolund)>
<Book 88 (Specification by Example 9781617290084) 249>
<Author 91 (Gojko Adzic)>
Bookdetail for a book (one-to-one)
We can use this code to go from a book to its bookdetail:
|
1 2 3 4 5 6 7 8 9 10 11 |
def show_book_details(bookA_id): session = db_session.factory() books = session.query(Book).\ options(joinedload("details")).\ filter(Book.id == bookA_id).all() for b in books: print(b) print(b.details) session.close() |
<Book 87 (The Mikado Method 9781617291210) 245>
<BookDetails 87 (477597.jpg)>
Next
With only a few rows in our database we may not notice what SQLAlchemy ORM does in the background. This changes as soon as we have some real data and our application is unbearably slow. Next week we look at the difference between eager loading and lazy loading.
1 thought on “Python Friday #84: Working With Relational Data in SQLAlchemy ORM”