Relationships between tables are of little use if we cannot join them in our queries. Let’s look at how SQLAlchemy Core creates JOIN statements.
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.
To print the results of the queries I slightly modified the print_result() method so that it uses the global connection_string variable:
|
1 2 3 4 5 6 |
def print_result(stmt): engine = create_engine(connection_string, echo=False) with engine.begin() as con: rs = con.execute(stmt) for row in rs: print(row) |
You find the full code for this post in my PythonFriday repository on GitHub.
Join two tables
SQLAlchemy Core offers us a join() method that creates the JOIN SQL statement:
|
1 2 3 4 |
join_publisher_books = publishers.\ join(books, publishers.c.Id == books.c.PublishedBy) print(join_publisher_books) |
“Publisher” JOIN “Book” ON “Publisher”.”Id” = “Book”.”PublishedBy”
If we have a foreign key between the two tables, SQLAlchemy creates the ON clause for us and we do not need to type it:
|
1 2 3 |
join_publisher_books = publishers.join(books) print(join_publisher_books) |
“Publisher” JOIN “Book” ON “Publisher”.”Id” = “Book”.”PublishedBy”
We can take this Join object and use it in our select() statement as part of the select_from() method:
|
1 2 3 |
stmt = select(publishers, books).\ select_from(join_publisher_books).\ where(publishers.c.Id == 1) |
This brings us all fields from the Publisher and the Book table:
(1, ‘Manning Publications’, 1, ‘The Mikado Method’, ‘9781617291210’, 245, 1)
(1, ‘Manning Publications’, 2, ‘Specification by Example’, ‘9781617290084’, 249, 1)
Show only the columns you need
In most cases we do not need all columns. We can tell the select() method which columns we want and only get them back:
|
1 2 3 4 5 |
stmt = select(books.c.Title, books.c.ISBN, books.c.Pages, publishers.c.Name).\ select_from(join_publisher_books).\ where(publishers.c.Id == 1) print_result(stmt) |
(‘The Mikado Method’, ‘9781617291210’, 245, ‘Manning Publications’)
(‘Specification by Example’, ‘9781617290084’, 249, ‘Manning Publications’)
Join multiple tables
For each table we want to join we add another join() method as in this example:
|
1 2 3 4 5 |
join_book_authors = book_authors.\ join(books).\ join(authors) print(join_book_authors) |
“BookAuthor” JOIN “Book” ON “Book”.”Id” = “BookAuthor”.”BookId” JOIN “Author” ON “Author”.”Id” = “BookAuthor”.”AuthorId”
We can use this combined JOIN to see the authors of a book through our many to many relation:
|
1 2 3 4 5 |
stmt = select(books.c.Title, authors).\ select_from(join_book_authors).\ where(book_authors.c.BookId == 1) print_result(stmt) |
(‘The Mikado Method’, 1, ‘Ellnestam’, ‘Ola’)
(‘The Mikado Method’, 2, ‘Brolund’, ‘Daniel’)
Left outer join
In a left outer join for publishers and books we get all publishers even when there are no books published by them in our database. SQLAlchemy offers the parameter isouter= in the join() method that we can set to True when we want a left outer join:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
def show_left_outer_join(): manning = create_publisher("Manning Publications") harper = create_publisher("Harper Collins") bookA = create_book("The Mikado Method", "9781617291210", 245, manning) join_publisher_books = publishers.join(books) print(join_publisher_books) stmt = select(publishers, books).\ select_from(join_publisher_books) print_result(stmt) join_publisher_books_full = publishers.join(books, isouter=True) print(join_publisher_books_full) stmt = select(publishers, books).\ select_from(join_publisher_books_full) print_result(stmt) |
In the output you can see the difference between the default isouter=False and isouter=True:
“Publisher” JOIN “Book” ON “Publisher”.”Id” = “Book”.”PublishedBy”
(1, ‘Manning Publications’, 1, ‘The Mikado Method’, ‘9781617291210’, 245, 1)“Publisher” LEFT OUTER JOIN “Book” ON “Publisher”.”Id” = “Book”.”PublishedBy”
(1, ‘Manning Publications’, 1, ‘The Mikado Method’, ‘9781617291210’, 245, 1)
(2, ‘Harper Collins’, None, None, None, None, None)
Full outer join
A full outer join combines the left outer join with a right outer join. This would give us all books and all publishers (if books could be saved in the database without a publisher).
|
1 |
publishers.join(books, full=True) |
Unfortunately, SQLite does not support right and full outer joins and throws an exception:
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) RIGHT and FULL OUTER JOINs are not currently supported
Other database systems implement these kinds of JOINS and this option may help you with your query.
Next
JOINs complete the most important parts of SQLAlchemy Core. What we covered so far should be enough to give you a good starting point for your own journey. Next week we start with SQLAlchemy ORM and how we define our objects there.
1 thought on “Python Friday #79: JOINs in SQLAlchemy Core”