flask SQLAlchemy 增删改查

  • 前言

  一直在用flask + SQLAlchemy, 每次数据联动,因为踩过坑,就更新一下自己的认识,若有错误,请谅解

  • 准备

    • 模块

1
2
3
4
5
6
7
8
9
10
click==8.1.3
Flask==1.1.2
Flask-SQLAlchemy==2.4.1
greenlet==2.0.1
itsdangerous==1.1.0
Jinja2==2.11.2
MarkupSafe==1.1.1
mysqlclient==2.0.3
SQLAlchemy==1.3.24
Werkzeug==1.0.1
    • db 连接

复制代码
from flask_sqlalchemy import SQLAlchemy


def init(app):
    app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://root:xyyhAv6at_@172.30.0.14:3306/demo"  # 需要改成自己的数据库地址
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
    app.config['SQLALCHEMY_ECHO'] = True  # 输出sql False 未不输出
    db = SQLAlchemy(app)
    db.init_app(app)
复制代码
    • ORM

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
from models.base import (
    db,
    TableOperateMixin,
    GenColumn)
 
 
class DemoModel(db.Model, TableOperateMixin):
    """
CREATE TABLE `tb_demo` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名称',
  `tag` varchar(100) NOT NULL DEFAULT '' COMMENT '标签',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='demo';
    """
    __tablename__ = "tb_demo"
 
    name = GenColumn(db.String(100), name="name", default="", comment="名称")
    tag = GenColumn(db.String(100), name="tag", default="", comment="标签")
 
    def __init__(self, **kwargs):
        self.name = kwargs.get("name")
        self.tag = kwargs.get("tag")
        super().__init__()
    • 代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
from models.tb_demo import DemoModel
from models.base import db
 
 
class Demo:
 
    @classmethod
    def query(cls, **kwargs):
        """
        sql 查询
        先获取table 各个column
        然后进行匹配, 组成sql query
        :param kwargs:
        :return:
        """
        column_names = [c.name for c in DemoModel.__table__.columns]
        body = {key: kwargs[key] for key in kwargs if key in column_names}
        select = DemoModel.query
        for key, value in body.items():
            if isinstance(value, list) or isinstance(value, set):
                select = select.filter(getattr(DemoModel, key).in_(list(value)))
            elif isinstance(value, dict):
                for k, v in value.items():
                    select = select.filter(getattr(DemoModel, key)[k] == v)
            else:
                select = select.filter(getattr(DemoModel, key) == value)
        return select
 
    @classmethod
    def create(cls, **kwargs):
        """
        新增数据
        :param kwargs: dict 字段
        :return:
        """
        with db.session.begin(subtransactions=True) as s:
            s.session.add(DemoModel(**kwargs))
 
    @classmethod
    def create_to_id(cls, **kwargs):
        """
        新增数据, 并 return model 用于获取自增id
        :param kwargs:
        :return:
        """
        with db.session.begin(subtransactions=True) as s:
            demo = DemoModel(**kwargs)
            s.session.add(demo)
            return demo
 
    @classmethod
    def bulk_create(cls, *args):
        """
        批量新增数据
        :param args:
        :return:
        """
        demos = []
        for arg in args:
            demos.append(DemoModel(**arg))
        if demos:
            with db.session.begin(subtransactions=True) as s:
                s.session.bulk_save_objects(demos)
 
    @classmethod
    def bulk_create_to_ids(cls, *args):
        """
        批量新增并返回
        :param args:
        :return:
        """
        demos = []
        for arg in args:
            demos.append(DemoModel(**arg))
        if demos:
            with db.session.begin(subtransactions=True) as s:
                s.session.bulk_save_objects(demos, return_defaults=True)
        return demos
 
    @classmethod
    def update(cls, _id, **kwargs):
        with db.session.begin(subtransactions=True):
            cls.query(id=_id).update(kwargs, synchronize_session='fetch')
 
    @classmethod
    def bulk_update(cls, *args):
        """
        批量新增并返回 ** 必须有唯一键, 未设置默认是id, 其他值就是要修改的值
        数据格式:
        [{
            "id": 1,
            "name": "a"
        }, {
            "id": 2,
            "name": "b",
            "tag": "tag"
        }]
        :param args:
        :return:
        """
        with db.session.begin(subtransactions=True) as s:
            s.session.bulk_update_mappings(DemoModel, args)
 
    @classmethod
    def delete(cls, id_list):
        """
        删除
        * 删除用in 查询时,需要加上 synchronize_session=False 否则抱错
        sqlalchemy.exc.InvalidRequestError: Could not evaluate current criteria in Python: "Cannot evaluate clauselist with operator <function comma_op at 0x10a5db430>". Specify 'fetch' or False for the synchronize_session parameter.
        :param id_list:
        :return:
        """
        with db.session.begin(subtransactions=True):
            cls.query(id=id_list).delete(synchronize_session=False)

  

  代码已上传git: https://github.com/spxinjie6/sql-crud

欢迎指正

创作不易,抄袭请说明出处

posted @   Simba辛巴  阅读(376)  评论(1编辑  收藏  举报
相关博文:
阅读排行:
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 单线程的Redis速度为什么快?
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?
· 展开说说关于C#中ORM框架的用法!
点击右上角即可分享
微信分享提示