python使用sqlalchemy操作-连表和多条件(3)

背景

前面有写过怎么使用sqlcalchemy的基本操作，本篇记录一下连表和多条件的写法。

解决方案

内连接，写过连表的都能看懂

self.orm_session.query(kwfpKwDetail.id).filter(kwfpKwDetail.status == 0).join(kwfpKeyword, kwfpKeyword.id == kwfpKwDetail.kw_id).all()

# 输出如下
2020-07-17 17:34:53,250 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-07-17 17:34:53,251 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT kwfp_kw_detail.id AS kwfp_kw_detail_id 
FROM kwfp_kw_detail INNER JOIN kwfp_keyword ON kwfp_keyword.id = kwfp_kw_detail.kw_id 
WHERE kwfp_kw_detail.status = %(status_1)s) AS anon_1
2020-07-17 17:34:53,251 INFO sqlalchemy.engine.base.Engine {'status_1': 0}

左联接

self.orm_session.query(kwfpKwDetail.id).filter(kwfpKwDetail.status == 0).outerjoin(kwfpKeyword, kwfpKeyword.id == kwfpKwDetail.kw_id).count()

# 输出如下
2020-07-17 17:35:58,944 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-07-17 17:35:58,945 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT kwfp_kw_detail.id AS kwfp_kw_detail_id 
FROM kwfp_kw_detail LEFT OUTER JOIN kwfp_keyword ON kwfp_keyword.id = kwfp_kw_detail.kw_id 
WHERE kwfp_kw_detail.status = %(status_1)s) AS anon_1
2020-07-17 17:35:58,945 INFO sqlalchemy.engine.base.Engine {'status_1': 0}

多条件筛选

self.orm_session.query(kwfpKeyword).filter(kwfpKeyword.pid > 18171).filter(kwfpKeyword.level == curl_level, kwfpKeyword.detail_status == 0).all()

单一字段多筛选

self.orm_session.query(kwfpKeyword).filter(and_(kwfpKeyword.pid > 18171, kwfpKeyword.pid < 18171)).count()


# 输出如下
2020-07-17 17:41:00,139 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-07-17 17:41:00,141 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT kwfp_keyword.id AS kwfp_keyword_id, kwfp_keyword.pid AS kwfp_keyword_pid, kwfp_keyword.kw AS kwfp_keyword_kw, kwfp_keyword.type AS kwfp_keyword_type, kwfp_keyword.level AS kwfp_keyword_level, kwfp_keyword.status AS kwfp_keyword_status, kwfp_keyword.detail_status AS kwfp_keyword_detail_status, kwfp_keyword.show_cnt AS kwfp_keyword_show_cnt, kwfp_keyword.addtime AS kwfp_keyword_addtime, kwfp_keyword.updatetime AS kwfp_keyword_updatetime 
FROM kwfp_keyword 
WHERE kwfp_keyword.pid > %(pid_1)s AND kwfp_keyword.pid < %(pid_2)s) AS anon_1
2020-07-17 17:41:00,141 INFO sqlalchemy.engine.base.Engine {'pid_1': 18171, 'pid_2': 18171}

记得引入and_

好记性不如烂笔头

1.如何指定查询的字段

只要query内写出模型具体属性就行。

self.orm_session.query(kwfpKwDetail.id).filter(kwfpKwDetail.status == 0).all()


2020-07-17 17:22:58,905 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-07-17 17:22:58,905 INFO sqlalchemy.engine.base.Engine SELECT kwfp_kw_detail.id AS kwfp_kw_detail_id 
FROM kwfp_kw_detail 
WHERE kwfp_kw_detail.status = %(status_1)s
2020-07-17 17:22:58,905 INFO sqlalchemy.engine.base.Engine {'status_1': 0}

2.如何为指定字段添加别名

需要使用label方法

self.orm_session.query(kwfpKwDetail.id.label('new_id')).filter(kwfpKwDetail.status == 0).all()


2020-07-17 17:26:18,238 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-07-17 17:26:18,239 INFO sqlalchemy.engine.base.Engine SELECT kwfp_kw_detail.id AS new_id 
FROM kwfp_kw_detail 
WHERE kwfp_kw_detail.status = %(status_1)s
2020-07-17 17:26:18,239 INFO sqlalchemy.engine.base.Engine {'status_1': 0}

3.记录一个查了两个小时的问题，代码之前写过，小改动之后能跑起来，但是总觉得差了点啥。

首先上代码,主要是看过滤条件那里，第一眼肯定没有问题

self.orm_session.query(kwfpKeyword).filter(kwfpKeyword.level == curl_level and kwfpKeyword.detail_status == 0).all()

耽误了我好久，找到了问题。

filter中不能用and

self.orm_session.query(kwfpKeyword).filter(kwfpKeyword.level == curl_level , kwfpKeyword.detail_status == 0).all()

改成逗号就ok了。