flask-sqlalchemy中的基本sql操作

---

更新

# 批量更新某表多条数据的某些字段
HeTongTaiZhangLaoWu.query.filter_by(xiang_mu_id=xiang_mu_id).update({"is_delete": True})

# 批量更新某表不同数据不同字段，更新数据中需要有主键
update_data = [
    {'id': 1, 'is_delete': True, 'name': '张三'},
    {'id': 2, 'is_delete': False, 'age': 20}
]
# 注意这种方式并不能触发orm层级的更新，即涉及到关联关系的属性并不能更新，只能对本表内的属性进行更新
db.session.bulk_update_mappings(RenYuanXinXi, update_data)

# 更新单条数据
ren_yuan_xin_xi.name = '张三'  # ren_yuan_xin_xi是数据库查询的实例化对象

跟新 Json 格式数据需要注意：
当更新json字段时，不能直接在该字段的基础上进行添加。例如：

def query():
    obj = DangAnShiWenJian.query.filter(DangAnShiWenJian.id == 783).first()
    obj.meta_info["new_key"] = "new_value"
    db.session.commit()

这是因为数据库对象的json属性值本质上是一个指针，当修改json类型时（例如字段或者列表），因为是可变数据类型，导致指针所指向的数据结构发生变化，但是指针本身没有变化，这种情况数据库认为该值没有发生变化，于是没有将修改操作commit进数据库。
有两种解决方式：

1. 重新定义一个新的dict或者list对象，可以使用copy或者new一个都可行。将新的对象赋值给数据库对象的json字段数据即可。
2. 显式的声明该字段被修改了：使用sqlalchem orm中的flag_modified将某个字段显式的标记为已修改（该操作下，即使未对该字段进行修改，也会触发表的on_update更新功能）

from sqlalchemy.orm.attributes import flag_modified

def query():
    obj = DangAnShiWenJian.query.filter(DangAnShiWenJian.id == 783).first()
    obj.meta_info["new_key"] = "new_value"
    flag_modified(obj, "meta_info")
    db.session.commit()

新增

# 创建一条
db.session.add(RenYuanXinXi(name='张三'))

# 批量创建数据，同表数据
create_data = [
    {'name': '张三', 'age': 20},
    {'name': '李四', 'age': 22},
]
# 注意这种方式并不能触发orm层级的更新，即涉及到关联关系的属性并不能新增，只能对本表内的属性进行新增
db.session.bulk_insert_mappings(RenYuanXinXi, create_data)

# 批量创建数据，不同表数据
create_data = []
create_data.append(RenYuanXinXi(name='张三'))
create_data.append(XiangMuYunXingZhiBiaoCanShuShiJianSheZhi(suo_shu_zu_zhi=1))
db.session.add_all(create_data)

查询

查询的顺序

在Flask-SQLAlchemy中，查询语句的执行顺序并不是按照你在代码中写的顺序来执行的。实际上，查询语句的执行顺序是由SQLAlchemy的ORM（对象关系映射）层在后台处理的。
以下是一般的执行顺序：

1. db.session.query()：这是开始一个查询的起点，你在这里指定你想要查询的模型或者字段。
2. filter()：这个方法在查询中添加WHERE子句，用于过滤结果。
3. group_by()：这个方法在查询中添加GROUP BY子句，用于将结果集按照某个字段进行分组。
4. 聚合函数（如db.func.max）：这些函数通常在GROUP BY子句之后使用，用于对每个分组进行某种计算，如求最大值、最小值、平均值等。
5. order_by()：这个方法在查询中添加ORDER BY子句，用于对结果集进行排序。
6. 最后，当你调用all()、first()等方法时，SQLAlchemy会将整个查询转换为SQL语句并在数据库中执行。

这个执行顺序是由SQL标准和SQLAlchemy的ORM层决定的，和你在代码中调用这些方法的顺序无关。你可以在代码中以任何顺序调用这些方法，SQLAlchemy会在后台正确地组织它们。

基本查询

# 查询符合条件的第一条, 没有返回None
ManYiDuDiaoChaShiJianSheZhi.query.filter(ManYiDuDiaoChaShiJianSheZhi.suo_shu_zu_zhi == zu_zhi_id, ManYiDuDiaoChaShiJianSheZhi.is_delete == false()).first()

# 查询符合条件的所有， 没有返回空的查询集
ManYiDuDiaoChaShiJianSheZhi.query.filter(ManYiDuDiaoChaShiJianSheZhi.suo_shu_zu_zhi == zu_zhi_id, ManYiDuDiaoChaShiJianSheZhi.is_delete == false()).all()

# 预加载数据
ManYiDuDiaoChaShiJianSheZhi.query.options(selectinload(ManYiDuDiaoChaShiJianSheZhi.can_shus)).filter(ManYiDuDiaoChaShiJianSheZhi.id == subquery).first()

查询id最大的一条数据

shi_jian = ManYiDuDiaoChaShiJianSheZhi.query.filter(ManYiDuDiaoChaShiJianSheZhi.suo_shu_zu_zhi == zu_zhi_id, ManYiDuDiaoChaShiJianSheZhi.is_delete == false()).order_by(ManYiDuDiaoChaShiJianSheZhi.id.desc()).first()

查询关联表id最大的所有数据

                    subquery = db.session.query(db.func.max(XiangMuYunXingZhiBiaoCanShuShiJianSheZhi.id)).filter(
                        XiangMuYunXingZhiBiaoCanShuShiJianSheZhi.suo_shu_xiang_mu == zu_zhi_id).scalar_subquery()
                    db_data = db_class.query.options(selectinload(db_class.xiang_mu_shi_jian_she_zhi)).filter(
                        db_class.suo_shu_xiang_mu_ == zu_zhi_id, db_class.shi_jian_she_zhi_id == subquery).all()

连接两表查询

                    subquery = db.session.query(db.func.max(XiangMuYunXingZhiBiaoCanShuShiJianSheZhi.id)).filter(
                        XiangMuYunXingZhiBiaoCanShuShiJianSheZhi.suo_shu_xiang_mu == zu_zhi_id).scalar_subquery()
                    db_data = db_class.query.join(XiangMuYunXingZhiBiaoCanShu).options(
                        selectinload(db_class.xiang_mu_shi_jian_she_zhi),
                        selectinload(db_class.xiang_mu_yun_xing_zhi_biao_can_shu)).filter(
                            db_class.suo_shu_xiang_mu_ == zu_zhi_id, db_class.shi_jian_she_zhi_id == subquery,
                            XiangMuYunXingZhiBiaoCanShu.zhi_biao_type == zhi_biao).all()

查询年龄在30以下、30-40、40-50、50以上的人数，人员按照身份证号码去重

        nian_ling_ren_shu = (
            db.session.query(
                db.func.count().label("count"),
                db.case(
                    (FenBaoShangRenYuanZhuCeBiao.nian_ling < 30, "30岁以下"),
                    (FenBaoShangRenYuanZhuCeBiao.nian_ling.between(30, 50), "30-50岁"),
                    (FenBaoShangRenYuanZhuCeBiao.nian_ling > 50, "50岁以上"),
                    else_="其他",
                ).label("nian_ling"),
            )
            .distinct(FenBaoShangRenYuanZhuCeBiao.shen_fen_zheng_hao_ma)
            .group_by("nian_ling")
            .all()
        )

子查询构建指定数组内的数据

        tong_ji_yue_fen_subquery = (
            db.session.query(db.func.distinct(model.tong_ji_yue_fen))
            .filter(
                model.zu_zhi_id == 5,
                model.tong_ji_yue_fen >= start_time,
                model.tong_ji_yue_fen < end_time,
            )
            .scalar_subquery()
        )
        if data_type == "last":
            filters.append(model.tong_ji_yue_fen.in_(tong_ji_yue_fen_subquery))

按照datetime类型日期，忽略时分秒分组

    result = (
        db.session.query(
            YueDuWuZiXuQiuJiHua,
            db.func.date_format(YueDuWuZiXuQiuJiHua.tong_ji_yue_fen, "%Y-%m-%d").label("day"),
            db.func.sum(YueDuWuZiXuQiuJiHua.shu_liang).label("shu_liang_zong_ji"),
        )
        .filter(YueDuWuZiXuQiuJiHua.xiang_mu_id == xiang_mu_id, YueDuWuZiXuQiuJiHua.is_delete == false())
        .group_by(YueDuWuZiXuQiuJiHua.md5, "day")
        .all()
    )

巧用多次子查询

    # 子查询，log数据中按照bill_id分组，去除每组操作时间最晚数据，得到log_id列表
    sub_query = (
        db.session.query(AppDataWorkFlowLog.bill_id, db.func.max(AppDataWorkFlowLog.id).label("max_id"))
        .filter(
            AppDataWorkFlowLog.optid == current_user.id,
            AppDataWorkFlowLog.status.in_(["PROCESSING", "FINISHED", "SENDBACK"]),
            AppDataWorkFlowLog.biao_dan_type == biao_dan_type,
        )
        .group_by(AppDataWorkFlowLog.bill_id)
        .subquery()
    )

    bill_ids = db.session.query(sub_query.c.bill_id).scalar_subquery()

    workflow_bills = (
        db.session.query(AppDataWorkFlowBill).filter(AppDataWorkFlowBill.id.in_(bill_ids), *bill_filters).all()
    )

如果要根据某几个字段进行group_by,并且对某个字段求最大值，并找到最大值对应的数据

# 需要先建立子查询，然后根据子查询进行逐一字段匹配进行查询
sub_query = (
        db.session.query(
            GongChengZaoJiaTongJiDanXiangGongCheng.zu_zhi_id,
            GongChengZaoJiaTongJiDanXiangGongCheng.dan_xiang_gong_cheng_id,
            GongChengZaoJiaTongJiDanXiangGongCheng.zhuan_ye_lei_bie,
            db.func.max(GongChengZaoJiaTongJiDanXiangGongCheng.tong_ji_yue_fen).label("max_date"),
        )
        .filter(
            GongChengZaoJiaTongJiDanXiangGongCheng.zu_zhi_id.in_(xiang_mu_ids),
            GongChengZaoJiaTongJiDanXiangGongCheng.is_delete == false(),
        )
        .group_by(
            GongChengZaoJiaTongJiDanXiangGongCheng.zu_zhi_id,
            GongChengZaoJiaTongJiDanXiangGongCheng.dan_xiang_gong_cheng_id,
            GongChengZaoJiaTongJiDanXiangGongCheng.zhuan_ye_lei_bie,
        )
        .subquery()
    )
    current_app.logger.info(sub_query)

    zu_zhi_id_and_max_date = (
        db.session.query(
            GongChengZaoJiaTongJiDanXiangGongCheng,
        )
        .filter(
            GongChengZaoJiaTongJiDanXiangGongCheng.is_delete == false(),
            GongChengZaoJiaTongJiDanXiangGongCheng.zu_zhi_id == sub_query.c.zu_zhi_id,
            GongChengZaoJiaTongJiDanXiangGongCheng.dan_xiang_gong_cheng_id == sub_query.c.dan_xiang_gong_cheng_id,
            GongChengZaoJiaTongJiDanXiangGongCheng.zhuan_ye_lei_bie == sub_query.c.zhuan_ye_lei_bie,
            GongChengZaoJiaTongJiDanXiangGongCheng.tong_ji_yue_fen == sub_query.c.max_date,
        )
        .all()
    )
    current_app.logger.info(zu_zhi_id_and_max_date)

过滤某个字符串类型的字段不包含“-”的数据

        data = GongChengChengBenZhengTiHeSuanGcxm.query.filter(
            GongChengChengBenZhengTiHeSuanGcxm.is_delete == false(),
            GongChengChengBenZhengTiHeSuanGcxm.tong_ji_yue_fen >= start_time,
            GongChengChengBenZhengTiHeSuanGcxm.tong_ji_yue_fen <= end_time,
            GongChengChengBenZhengTiHeSuanGcxm.xiang_mu_id == zu_zhi_id,
            GongChengChengBenZhengTiHeSuanGcxm.cheng_ben_bian_ma.notlike("%-%"),
        ).all()

多对多关联对象过滤查询

zhu_ce_biao_filters.append(
	FenBaoShangRenYuanZhuCeBiao.shi_yong_bu_wei.any(
		DanXiangGongCheng.id== shi_yong_bu_wei_id_
		)
    )

枚举

根据枚举值获取枚举常量

class ShiYongDanWeiType(enum.Enum):
    lao_wu_fen_bao = '劳务分包'
    zhuan_ye_fen_bao = '专业分包'
    zong_bao_dan_wei = '总包单位'
    qi_ta_dan_wei = '其他单位'
    
# 得到枚举常量
ShiYongDanWeiType('总包单位') # 通过value获得
ShiYongDanWeiType.zong_bao_dan_wei
ShiYongDanWeiType["zong_bao_dan_wei"] # 通过name获得

# 获取枚举key
ShiYongDanWeiType.lao_wu_fen_bao.name  # 返回 lao_wu_fen_bao 字符串

# 获取枚举value
ShiYongDanWeiType.lao_wu_fen_bao.value  # 返回 劳务分包 字符串

# 枚举类查询
class ZhaoTouBiaoWenJianType(enum.Enum):
    zhao_biao_wen_jian = '招标文件'
    tou_biao_wen_jian = '投标文件'
ZhaoTouBiaoWenJian.query.filter(ZhaoTouBiaoWenJian.wen_jian_lei_xing == 'zhao_biao_wen_jian').all()

打印

.__dict__

# db.session.query方式查询出来的数据打印
._asdict()

# 打印 SQL 语句
print(db.session.query(*querys).filter(*filters).group_by(*group_by).statement)

# 打印查询参数
print(db.session.query(*querys).filter(*filters).group_by(*group_by).params)

创建或更新后立即获取新数据的相关属性

    # 创建
    record=[]
    dan_xiang_gong_chengs=[
        {
            "suo_shu_xiang_mu": 1,
        },
        {
            "suo_shu_xiang_mu": 2,
        }
    ]
    for obj in dan_xiang_gong_chengs:
        record.append(DanXiangGongCheng(**obj))
    db.session.add_all(record)
    db.session.commit()
    for obj in record:
        print('+++++++++++++++',obj.id)
    # 更新
    db_objs = DanXiangGongCheng.query.filter(DanXiangGongCheng.id.in_([4,5])).all()
    for obj in db_objs:
        # ㎡
        obj.gui_mo_dan_wei='m'
    db.session.commit()
    for obj in db_objs:
        print(obj.gui_mo_dan_wei)

当需要获取日期类型的属性时，上述方式有一个需要注意的点：

    # 创建
    record = []
    dan_xiang_gong_chengs = [
        {
            "suo_shu_xiang_mu": 1,
            "created_at": "2021-01-01",
        },
        {
            "suo_shu_xiang_mu": 2,
        },
    ]
    for obj in dan_xiang_gong_chengs:
        record.append(DanXiangGongCheng(**obj))
    db.session.add_all(record)
    db.session.flush()
    for obj in record:
        print("+++++++++++++++", type(obj.created_at))

这种情况下，第一次会输出<class 'str'>，第二次会输出<class 'datetime.datetime'>。也就是说，手动创建时间类型的数据时，flush之后，自己创建时是什么类型，获取到的还是什么类型；数据库默认创建的时间类型是datetime类型的。

多对多关系的增加与修改

# 增加
    dan_xiang_gong_chengs = DanXiangGongCheng.query.filter(DanXiangGongCheng.id.in_([1, 2])).all()
    data = {"min_zu": "汉族", "shi_yong_bu_wei": dan_xiang_gong_chengs}
    db_data = FenBaoShangRenYuanZhuCeBiao(**data)
    db.session.add(db_data)
    db.session.commit()

# 修改
    dan_xiang_gong_chengs = DanXiangGongCheng.query.filter(DanXiangGongCheng.id.in_([4, 5])).all()
    data = {"min_zu": "傣族", "shi_yong_bu_wei": dan_xiang_gong_chengs}
    db_data = FenBaoShangRenYuanZhuCeBiao.query.filter_by(id=128).first()
    for key, value in data.items():
        setattr(db_data, key, value)
    db.session.commit()

数据监听

使用sqlalchemy 中的event 接口进行表操作监听，可以使用event.listen() 或者event.listens_for() 装饰器

1. 使用限制：只有单个插入数据，或者obj.field_name = new_value 的更新方式能够触发listen，其他的更新或插入方式无法触发
2. 如果回调函数中是对数据库对象本身进行操作，那么操作完后不需要单独提交事务

# 定义 事件监听器函数，在 插入/更新 数据之前计算
def calculate_xiang_mu_gai_kuang(mapper, connection, target):
    if target.mate_info is None:
        target.mate_info = {"illegal_fields": []}

    if "illegal_fields" not in target.mate_info:
        target.mate_info["illegal_fields"] = []

    fields_to_check = ["ben_qi_ji_hua_wan_cheng_lv", "lei_ji_ji_hua_wan_cheng_lv"]

    for field in fields_to_check:
        if getattr(target, field) is None:
            if field not in target.mate_info["illegal_fields"]:
                target.mate_info["illegal_fields"].append(field)
        else:
            if field in target.mate_info["illegal_fields"]:
                target.mate_info["illegal_fields"].remove(field)

# 将事件监听器与数据模型关联
event.listen(XiangMuGaiKuang, "before_insert", calculate_xiang_mu_gai_kuang, propagate=True)

使用listens_for 装饰器举例
listens_for 中需要指定需要监听的字段，如果有多个字段，可以使用多个装饰器

@event.listens_for(DanXiangGongCheng.ji_chu_lei_xing, "set")
def on_ren_yuan_xin_xi_changed(target, value, old_value, initiator):
    print(11111)
    target.is_tong_bu_super = false()

通过query语句获取当前查询model

query.column_descriptions[0]["type"]
# 或者
query.column_descriptions[0]["expr"]

Float 和 Decimal 类型数据查询结果的类型受查询方式影响

案例：

    res1 = LaoWuGongRenYongGongTongJi.query.get(1)
    res2 = db.session.query(
        db.func.sum(LaoWuGongRenYongGongTongJi.ben_qi_qi_chu_ren_shu).label("ben_qi_qi_chu_ren_shu")
    ).first()
    
    print("------打印调试信息------", type(res1.ben_qi_qi_chu_ren_shu))
    print("------打印调试信息------", type(res2.ben_qi_qi_chu_ren_shu))

结果：

------打印调试信息------ <class 'int'>
------打印调试信息------ <class 'decimal.Decimal'>

原因：
res1 和 res2 获取的数据类型不同是因为它们查询数据的方式不同，导致了 SQLAlchemy 在处理结果时使用了不同的数据类型。
对于 res1，直接使用.query.get(1)从 LaoWuGongRenYongGongTongJi 模型获取一个实例。在这种情况下，ben_qi_qi_chu_ren_shu 字段的类型由该模型定义。如果模型中 ben_qi_qi_chu_ren_shu 被定义为整数类型（如 Integer ），那么查询结果中res1.ben_qi_qi_chu_ren_shu的类型就是 int 。
对于 res2，你使用了db.session.query()配合db.func.sum()进行了聚合查询。在 SQLAlchemy 中，当你对某列进行聚合操作（如求和）时，默认情况下，结果可能会被处理为 Decimal 类型，这是为了保证数值运算的精度。因此，即使原始数据是整数类型，聚合后的结果 res2.ben_qi_qi_chu_ren_shu 也可能是 decimal.Decimal 类型。
这种类型差异是由于 SQLAlchemy 和底层数据库如何处理聚合函数的结果决定的。Decimal 类型提供了精确的小数运算，这在处理财务数据时特别有用，但它可能与 Python 的内置 int 类型在行为上有所不同。

通过relationship加载关联对象时，获取这个关联对象的模型

    @classmethod
    def get_last_data(cls, zu_zhi_id, deadline=None, relationship_keys=None):
        if relationship_keys is None:
            relationship_keys = cls.relationship_keys
        filters = [getattr(cls, cls.zu_zhi_key) == zu_zhi_id, cls.is_avilable == true(), cls.is_delete == false()]
        if deadline:
            filters.append(cls.shu_wen_jian_created_at < deadline)

        options = []
        for relationship_key in relationship_keys:
            relationship_attr = getattr(cls, relationship_key)
            # 获取关联对象的模型
            related_model = relationship_attr.property.mapper.class_
            options.append(selectinload(relationship_attr.and_(related_model.is_delete == false())))

        shi_jian_she_zhi = (
            cls.query.options(*options).filter(*filters).order_by(cls.shu_wen_jian_created_at.desc()).first()
        )
        return shi_jian_she_zhi