sqlalchemy orm复杂查询备忘录

收录一些自己用过的sqlalchemy复杂查询以备忘

env: python flask sqlalchemy

case 1：搜索今年及去年的数据记录

编号格式为：PR-19-00001
编号中间的19为年份，需要根据年份来找出所有今年以及去年（18）的记录并排序
key point：or_、contains()

from sqlalchemy import or_
import datetime, time

this_year_code = str(datetime.datetime.now().year)[2:4]
last_year_code = str(int(this_year_code) - 1)
projects = Projects.query.filter(or_(Projects.project_num.contains('PR-{}'.format(last_year_code)),
                                         Projects.project_num.contains('PR-{}'.format(this_year_code)))).order_by(
        Projects.project_num.desc()).all()

result查询对象转化为dict

db.session.query出的结果是result查询对象，如果是.first()就直接是result对象，如果是.all()，则是result的list
（1）db.session.query().filter().first()

requirements = db.session.query(RequirementList.id, DeviceCategory.type, DeviceCategory.name, DeviceCategory.specs,
                                    DeviceCategory.supplier, DeviceCategory.price, DeviceCategory.usage_scenarios,
                                    RequirementList.numbers, RequirementList.device_category_id,
                                    DeviceCategory.brand).join(DeviceCategory,
                                                               DeviceCategory.id == RequirementList.device_category_id).filter(
                                    RequirementList.order_id == order_id).all()
# 将result结果转化为dict
requirement_list = [dict(zip(r.keys(), r)) for r in requirements]   

（2）db.session.query().filter().all()

device = db.session.query(PurchaseList.cicode, PurchaseList.sn, DeviceCategory.name,
                                  DeviceCategory.specs,
                                  DeviceCategory.price, PurchaseList.kvm_ip, DeviceCategory.supplier,
                                  DeviceCategory.brand, PurchaseList.status).select_from(PurchaseList). \
            join(DeviceCategory, DeviceCategory.id == PurchaseList.device_category_id). \
            filter(PurchaseList.cicode == every_cicode).first()
# 将result list结果转化为dict的list
device_dict = dict(zip(device.keys(), device))

查找字段为空或者非空

# 查找字段为空的
db.session.query(employee).filter(employ.brand_id==None))

# 查找字段非空的
db.session.query(employee).filter(employ.brand_id.isnot(None))

# 查找字段非空2
from sqlalchemy import not_
db.session.query(user).filter(not_(employ.user==None))

或和并同时的查询条件

from sqlalchemy import or_, and_

db.session.query(devices).filter(or_(
	devices.name == None,
	and_(devices.status == 'delivered', devices.op == 'david')
))

多条件过滤查询

param = []
param.append(User.name == 'xiaoming')
param.append(User.city == 'shenzhen')
param.append([User.num.isnot(None), User.car == 'Focus')

db.session.query(User).filter(*param).all()