如何在Python中使用不同类型的连接用于SQL-Alchemy

Question

i正在试图弄清楚如何定义sql-alchemy orm中的联接类型。如何使用左联接和左外连接？那内连接呢？

这是用于查询，可以选择无相关CRM_TASK的所有CRM_LEAD。我尝试过过滤器，但无法使用此子句过滤现有的CRM_LEAD。

所需的SQL：

select *
from crm_lead l
join crm_task t on l.id = t.lead_id
left outer join crm_pipeline_status cps on l.pipeline_status_id = cps.id
where l.pipeline_status_id not in (142, 143) 
  and (t.id is null or t.is_completed is false);

or ：(如果存在条款对这种情况更好(

select *
from crm_lead l
left outer join crm_pipeline_status cps on l.pipeline_status_id = cps.id
where cps.crm_id not in (142, 143)
  and not exists (select id from crm_task t where l.id = t.lead_id and t.is_completed is false);

我最好的尝试是：

session = sessionmaker(bind=engine, autocommit=True)()
with session.begin():
    leads = session.query(CrmLead).outerjoin(CrmTask).outerjoin(CrmPipelineStatus).filter(
        and_(CrmLead.account_id == 2,
        CrmPipelineStatus.crm_id not in (142, 143),
        or_(CrmTask.is_completed is False, CrmTask.id is None))
    )

但它转换为：

SELECT *
FROM crm_lead 
LEFT OUTER JOIN crm_task ON crm_lead.id = crm_task.lead_id 
LEFT OUTER JOIN crm_pipeline_status ON crm_pipeline_status.id = crm_lead.pipeline_status_id 
WHERE false

替代解决方案：我的情况可以用RAW SQL解决，如下所示[https://stackoverflow.com/a/22084672/2822537]

示例：

    query_text = '''
    select *
    from crm_lead l
    left outer join crm_pipeline_status cps on l.pipeline_status_id = cps.id
    where cps.crm_id not in (:success_final_status, :failed_final_status)
      and l.account_id = :account_id
      and not exists (select id from crm_task t where l.id = t.lead_id and t.is_completed is false);
    '''
    leads = session.execute(query_text, {
        'account_id': crm_configuration["instance_id"],
        'success_final_status': 142,
        'failed_final_status': 143
    })

Answer 1

表达式

CrmPipelineStatus.crm_id not in (142, 143)

在Python中评估False或True，因为__contains__()不能以与其他过载相同的方式使用。如果是False，则将整个封闭的and_()构造汇编为简单的false。在这种情况下，正确的方法是使用方法notin_()：

CrmPipelineStatus.crm_id.notin_([142, 143])

发生了一些类似的问题

or_(CrmTask.is_completed is False, CrmTask.id is None)

由于身份操作员is不能超载，因此您有效地

or_(False, False)

再次编译为false。对于第一个，您应该只使用布尔值作为布尔值(给定是NOT NULL(，并且可以使用is_()方法或操作员中的特殊情况编写NULL检查：

or_(not_(CrmTask.is_completed), CrmTask.id == None)

Answer 2

看一下这篇文章

q = session.query(Table1.field1, Table1.field2)
.outerjoin(Table2) # use in case you have relationship defined
# .outerjoin(Table2, Table1.id == Table2.table_id) # use if you do not have relationship defined
.filter(Table2.tbl2_id == None)

应该这样做，假设field1和field2来自table1，并且您定义了一个关系：

class Table2(Base):
# ...
table1 = relationship(Table1, backref="table2s")

Answer 3

骆驼用于映射的较低表名称：

from sqlalchemy.sql import exists
Session().query(CrmLead).join(CrmTask).outerjoin(CrmPipelineStatus).filter(CrmLead.pipeline_status_id == CrmPipelineStatus.id).filter(CrmPipelineStatus.crm_id.notin_([142, 143])).filter(~exists().where(and_(CrmTask.is_completed==False, CrmLead.id==CrmTask.lead_id)