在SQLAlchemy中，“过滤器”和“过滤器”之间的区别是什么？与“加入并过滤”句法？ [英] In SQLAlchemy what is the difference between the "filter" vs "join and filter" syntax?

问题描述

更具体地说，我看到很多人不使用SQLAlchemy中类似SQL的自然连接语法，而是倾向于使用过滤器来实现连接。详细说明以下是我如何进行联接的方法：

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.

但是其他地方（尤其是在StackOverflow上）我都看到人们这样做：

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
            ))

以下哪种方法最合适？ y要这样做吗？哪个更适合Pythonic？或者，至少，应该使用SQLAlchemy的方式更惯用了？在数据库资源使用或本地计算机资源使用方面是否存在差异（也就是说，对数据库的CPU和RAM的压力更大，而对本地计算机的压力较小，反之亦然）？

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)?

此外，前一种方法不允许在Query API上使用 update（）方法-它抱怨无法进行多表更新允许-即使我只更新一个表。后者允许 update（）正常工作。

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.

推荐答案

主要区别在于前者会导致使用 SQL-92 JOIN 语法，而后者则使用较旧的语法–例如，有些人出于习惯而不喜欢它。两者都是正确的方法，两者都与是否使用Python语言无关。我还认为，尽管 Query.join（） 与定义的外键关系和ORM关系很好地配合使用。在现代SQL DBMS中，它们还应导致相同的执行计划，因此在资源使用等方面没有有意义的区别。

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.

对于 Query.update（） 不支持显式连接，不同的SQL DBMS对具有不同语法和方法的多表更新有不同的支持。有些允许显式联接，有些不允许，有些允许通过子查询进行更新 。当前的实现似乎是一个折衷方案，它将为正在使用的DBMS提供适当的 UPDATE 语句。

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.

这篇关于在SQLAlchemy中，“过滤器”和“过滤器”之间的区别是什么？与“加入并过滤”句法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！