python - SQL 到 SQLAlchemy 的翻译

Question

我有一个有点奇怪的查询，它获取父表中在相应子表中没有匹配项的所有项目。

如果可能的话，我想把它变成一个 SQLAlchemy 查询。但我不知道怎么办。我可以进行基本的获取和过滤，但是到目前为止，这超出了我的经验。如果您能提供任何帮助，我们将不胜感激。

class customerTranslations(Base):
    """parent table. holds customer names"""
    __tablename__ = 'customer_translation'

    id = Column(Integer, primary_key=True)

class customerEmails(Base):
    """child table. hold emails for customers in translation table"""
    __tablename__ = 'customer_emails'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('customer_translation.id'))

我想构建:

SELECT * FROM customer_translation 
WHERE id NOT IN (SELECT parent_id FROM customer_emails)

Answer 1

您有一个子查询，因此首先创建一个:

all_emails_stmnt = session.query(customerEmails.parent_id).subquery()

然后您可以使用它来过滤其他表格:

translations_with_no_email = session.query(customerTranslations).filter(
    ~customerTranslations.id.in_(all_emails_stmnt))

这会产生相同的 SQL(但扩展了所有列名称，而不是使用 *，ORM 然后可以创建您的对象):

>>> all_emails_stmnt = session.query(customerEmails.parent_id).subquery()
>>> print(all_emails_stmnt)
SELECT customer_emails.parent_id
FROM customer_emails
>>> translations_with_no_email = session.query(customerTranslations).filter(
...     ~customerTranslations.id.in_(all_emails_stmnt))
>>> print(translations_with_no_email)
SELECT customer_translation.id AS customer_translation_id
FROM customer_translation
WHERE customer_translation.id NOT IN (SELECT customer_emails.parent_id
FROM customer_emails)

您还可以使用NOT EXISTS:

from sqlalchemy.sql import exists

has_no_email_stmnt = ~exists().where(customerTranslations.id == customerEmails.parent_id)
translations_with_no_email = session.query(customerTranslations).filter(has_no_email_stmnt)

或者，如果您在 customerTranslations 类上有一个指向电子邮件的反向引用，名为 emails，请在关系上使用 .any()并反转:

 session.query(customerTranslations).filter(
    ~customerTranslations.emails.any())

回到 2010 年 NOT EXISTS was a little slower on MySQL但您可能需要重新评估情况是否仍然如此。