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
欢迎指正
创作不易,抄袭请说明出处
devops 开发小白,不喜勿喷
本文来自博客园,作者:Simba辛巴,转载请注明原文链接:https://www.cnblogs.com/spxinjie6/p/16904220.html
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 单线程的Redis速度为什么快?
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?
· 展开说说关于C#中ORM框架的用法!