使用SQLAlchemy ORM设置比较

Question

查看一本OReilly的旧书Transact SQL Cookbook，其中一节描述了原始SQL中如何执行集合比较。你知道吗

CREATE TABLE branch_book_list (
   branch_name CHAR(10),
   book_ISBN CHAR (13),
   book_name CHAR(40),
   PRIMARY KEY (branch_name, book_ISBN)

表格：

branch_name book_ISBN     book_name                                
----------- ------------- ---------------------------------------- 
Branch A    1-56592-401-0 Transact-SQL Programming                
Branch A    1-56592-578-5 Oracle SQL*Plus: The Definitive Guide   
Branch A    1-56592-756-7 Transact-SQL Cookbook   

Branch B    1-56592-401-0 Transact-SQL Programming                
Branch B    1-56592-756-7 Transact-SQL Cookbook                   
Branch B    1-56592-948-9 Oracle SQL*Loader: The Definitive Guide

查找不在另一集中的行 首先，我们编写一个查询来查找由分支a持有但不由分支B持有的所有书籍。使用SQL Server，我们可以通过使用子查询来实现此目标，如下所示：

SELECT bbl1.*
FROM branch_book_list bbl1
WHERE branch_name = 'Branch A'
  AND NOT EXISTS (
      SELECT bbl2.*
      FROM branch_book_list bbl2
      WHERE branch_name = 'Branch B' 
        AND bbl1.book_ISBN = bbl2.book_ISBN 
        AND bbl1.book_name = bbl2.book_name)

输出：

branch_name book_ISBN     book_name                                
----------- ------------- ---------------------------------------- 
Branch A    1-56592-578-5 Oracle SQL*Plus: The Definitive Guide

现在的问题是

使用ORM框架的SQLAlchemy中的相应查询应该是什么？

Answer 1

你的模型是这样的：

class BranchBookList(Base):

    __tablename__ = 'branch_book_list'

    branch_name = Column(Unicode)
    book_ISBN = Column(Unicode)
    book_name = Column(Unicode)

    __table_args__ = (PrimaryKeyConstraint(branch_name, book_ISBN),)

在带有模型的模块中，您应该首先定义^{}和^{}。还有一个查询的结果与建议的相同：

>>> from sqlalchemy.orm.util import aliased, and_
>>> from module_with_bbl import BranchBookList, Session
>>> bbl1 = Session.query(BranchBookList).filter(BranchBookList.branch_name == 'Branch A').subquery()
>>> bbl2 = Session.query(BranchBookList).filter(BranchBookList.branch_name == 'Branch B').subquery()
>>> query = Session.query(bbl1).outerjoin(bbl2, and_(bbl1.c.book_name == bbl2.c.book_name, bbl1.c.book_ISBN == >>> bbl2.c.book_ISBN)).filter(bbl2.c.book_ISBN == None)
>>> print(query)
SELECT anon_1.branch_name AS anon_1_branch_name, anon_1."book_ISBN" AS "anon_1_book_ISBN", anon_1.book_name AS anon_1_book_name 
FROM (SELECT branch_book_list.branch_name AS branch_name, branch_book_list."book_ISBN" AS "book_ISBN", branch_book_list.book_name AS book_name 
FROM branch_book_list 
WHERE branch_book_list.branch_name = :branch_name_1) AS anon_1 LEFT OUTER JOIN (SELECT branch_book_list.branch_name AS branch_name, branch_book_list."book_ISBN" AS "book_ISBN", branch_book_list.book_name AS book_name 
FROM branch_book_list 
WHERE branch_book_list.branch_name = :branch_name_2) AS anon_2 ON anon_1.book_name = anon_2.book_name AND anon_1."book_ISBN" = anon_2."book_ISBN" 
WHERE anon_2."book_ISBN" IS NULL
>>> query.all()
[(u'Branch A', u'1-56592-578-5', u'Oracle SQL*Loader: The Definitive Guide')]

我几乎100%确信我的版本是更好的，因为建议查询中的子查询将对branch_book_table中的每一行执行。因此，如果表中有上千行，则会有上千个额外的子查询，而我的查询只有两个查询。你知道吗

对不起我的英语：）