4

More specifically, I see that instead of using the natural SQL-like join syntax in SQLAlchemy, a lot of people tend to use filters for purposes of joins. To elaborate here's how I would do a join:

(session.Query(Book)
        .join(Author, Publisher, Retailer)
        .filter(
            Author.name == "Crenshaw Adams",
            Publisher.country == "United States",
            Retailer.is_online == True))

The join columns are implicit in the relationships as defined in the model declaration file.

But everywhere else (especially on StackOverflow) I've seen people do it as:

(session.Query(Book)
        .filter(
            Book.author_id == Author.id,
            Author.publisher_id == Publisher.id,
            Publisher.retailer_id == Retailer.id,
            Author.name == "Crenshaw Adams",
            Publisher.country == "United States",
            Retailer.is_online == True
            ))

Which of these methods is the right way to do it? Which is more Pythonic? Or, at the very least, more idiomatic in the way SQLAlchemy is supposed to be used? And is there a difference in terms of DB resource usage, or local machine resource usage (that is, one is more stressful on CPU and RAM of DB and less on local machine, and vice versa)?

In addition, the former way doesn't allow for the update() method on the Query API - it complains about multi-table updates not being allowed - even if I'm updating only a single table. The latter allows for update() to work just fine.

alichaudry
  • 2,573
  • 2
  • 18
  • 27

1 Answers1

6

The main difference is that the former results in a query that uses the SQL-92 JOIN syntax, while the latter uses the older syntax – there are people that prefer it out of habit, for example. Both are the right way and neither have much to do with the code being Pythonic or not. Also in my opinion neither is more idiomatic in SQLAlchemy, though Query.join() works nicely with defined foreign key relationships and ORM relationships, as you've noted yourself. They should also result in the same execution plan in modern SQL DBMS, so there's no meaningful difference in resource usage etc.

As to Query.update() not supporting explicit joins, different SQL DBMS have varying support for multiple table updates, with differing syntax and methods. Some allow explicit joins, some don't, and some allow updating through subqueries. The current implementation seems like it's a compromise and will render to suitable UPDATE statement for the DBMS in use.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127