flask sqlalchemy 数据动态更新

coding时候用flask sqlalchemy操作数据库，但是由于了解的不是很多，因此一直卡在数据动态更新上。后来总算是解决了这个问题，记录下大致过程。

本文假设有两张表格需要操作，一个是group，一个是student。

group
字段	名称	类型	注	示例值
id	编号	int	自增主键	12
name	班级名称	varchar(255)	非空	计科23(1)
parent_id	父级组织编号	int	指向group.id的外键	5
leaf	叶节点	tinyint	非空，默认值为0	0

student
字段	名称	类型	注	示例值
id	编号	int	自增主键	6
stu_id	学生学号	varchar(255)	非空	2023xxxxxxxxx
name	学生名称	varchar(255)	非空	张XX
group_id	所属组织id	int	指向group.id的外键	5
telephone	学生电话	varchar(255)	非空	5

用datagrip展示ER图如下：

两张表格对应的flask sqlalchemy代码如下：

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import remote
import sqlalchemy

database = SQLAlchemy()

class Group(database.Model):
    __tablename__ = 'group'

    id = database.Column(database.Integer, primary_key=True, comment='自增id')
    group_id = database.Column(database.Integer, nullable=False, unique=True, comment='组织id')
    name = database.Column(database.String(255), nullable=False, comment='组织名称')
    parent_id = database.Column(database.ForeignKey('group.group_id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False, index=True, comment='上级组织id')
    leaf = database.Column(database.TINYINT, nullable=False, comment='是否为叶节点')

    parent = database.relationship('Group', remote(sqlalchemy.column('group.id')))

class Student(database.Model):
    __tablename__ = 'student'

    id = database.Column(database.Integer, primary_key=True, comment='自增id')
    stu_id = database.Column(database.String(255), nullable=False, unique=True, comment='学生学号')
    name = database.Column(database.String(255), nullable=False, comment='学生姓名')
    group_id = database.Column(database.ForeignKey('group.group_id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False, index=True, comment='学生所在组织id')
    telephone = database.Column(database.String(255), nullable=False, comment='学生电话')

    group = database.relationship('Group')

普通情况下，用以下方法添加一条数据：

obj = Group(name=val_1,parent_id=val_2,leaf=val_3)
database.session.add(obj)
database.session.commit()

但是直接使用类进行添加有缺陷。特别对于表格中字段数量较多的时候，这一条语句会非常长，显得不灵活，且泛用性不是特别强。

因此思考是否有一种办法，能够根据传入的字典，动态增加数据。搜索似乎也没能找到想要的答案，于是乎尝试自己改，碰壁+踩坑无数，终于改出了一个满意的答案。

在flask-sqlalchemy中，ORM是个非常重要的概念。

对象关系映射（Object Relational Mapping，简称ORM）模式是一种为了解决面向对象与关系数据库存在的互不匹配的现象的技术。ORM框架是连接数据库的桥梁，只要提供了持久化类与表的映射关系，ORM框架在运行时就能参照映射文件的信息，把对象持久化到数据库中。

基于此，给出如下代码：（把用户名和密码改成本机数据库的用户名和密码、表格内容替换成自定义的表格）

class DBCtrl:
    def __init__(self,host,username,password,db,table):
        """
        初始化
        :param host: 连接ip
        :param username: 用户名
        :param password: 密码
        :param db: 数据库名称
        :param table: 表的名称
        """
        self.host = host
        self.username = username
        self.password = password
        self.db = db
        self.table = table
        self.model = Group if self.table=='group' else Student

        self.engine = create_engine('mysql+pymysql://' + self.username + ':' + self.password + '@' + self.host + ':3306/' + self.db)
        self.metadata = MetaData()
        self.metadata.bind = self.engine
        self.Base = sqlalchemy.orm.declarative_base()
        self.create_not_exist()

    def create_not_exist(self):
        """
        如果表不存在则创建
        """
        exist = self.engine.dialect.has_table(db.engine.connect(),schema=self.db,table_name=self.table)
        if not exist:
            db.create_all()
        class CtrlTable(self.Base,db.Model):
            __table__ = Table(self.table,self.metadata,autoload_with=self.engine)

            def __init__(self,data_dict):
                for key, value in data_dict.items():
                    setattr(self, key, value)
        self.CtrlTable = CtrlTable


    def update_dynamic(self,session,value_dic_list,logger):
        """
        动态更新,如果存在则更新,不存在则插入,可执行批量数据
        :param session: 数据库会话
        :param value_dic_list: 需要更新或增加数据的列表
        :param logger: 日志，类型为logging
        """
        for vd in value_dic_list:
            try:
                query = self.CtrlTable(vd)
                session.add(query)
                session.commit()
            except sqlalchemy.exc.OperationalError as seo:
                logger.debug("故障："+str(seo))
            except sqlalchemy.exc.IntegrityError as sei:
                logger.debug("故障："+str(sei))
            except sqlalchemy.exc.PendingRollbackError as sep:
                logger.debug("故障："+str(sep))        

代码设定所有的flask-sqlalchemy代码都存放在一个类中方便操作数据库，通过实例化这个操作的类，自动导入或创建表格，并在类中定义了CRUD操作。上述代码中给出了增加和修改操作。我在代码中合并了增加和更改操作为update_dynamic，如果记录不存在则增加，存在则进行修改。

代码中的self.CtrlTable是一个Table类，根据传入的表名从数据库导入对应表。重写构造函数，以便传入字典增加或修改记录。python中通过setattr函数可以设置类的属性，如果属性存在，则修改该值，如果属性不存在，则设定该值。

因此，如果希望灵活批量添加或者修改记录，以'group'表为例，用一个字典存放一条记录vd：

{
    'group_id'='SC_01',
    'name'='xxxx大学',
    'parent_id'=None,
    'leaf'=False
}

列表value_dic_list存放多个记录：前两条为增加的数据，最后一条为修改的数据

[
    {
    'group_id'='SC_02',
    'name'='xxxx大学',
    'parent_id'=None,
    'leaf'=False
    },
    {
    'group_id'='CO_03',
    'name'='xxxx学院',
    'parent_id'='SC_02',
    'leaf'=False
    },
    {
    'group_id'='CO_02',
    'name'='xxx学院'
    }
]

数据库原始数据如下：

将上述作为输入传入update_dynamic函数，运行得到：

其中红色部分为修改内容，橙色部分为新增内容。

需要注意的是，用上述类实例化并进行操作时，确保在flask路由函数的环境下，否则报错。