【自动化运维新手村】Flask-ORM关联查询

【摘要】

到目前为止，Flask集成ORM扩展到基本操作，已经算是接近尾声了，上一章节已经将单表数据的增删改查，做了十分详细的讲解，并且从Flask应用的日志中可以看出每个ORM操作对应的数据库SQL语句，能够更为清晰的看到程序模型到数据库之间的映射关系，让大家可以对MySQL有一个基本的了解。

但几乎所有的后端应用都不可能只存在单独的一张数据表，大多数情况下都是存在多张数据表，并且这些数据表之间都存在关联，可能是一对一，或者一对多，等等。那么今天这一章节我们就着重讲解一下如何使用Flask-SQLAlchemy进行多表关联查询，并逐步完善后端应用的参数及异常处理。

【一/多 对 一/多】

数据库两张表之间的关系主要存在以下几种关系：

1.一对一

2.一对多

3.多对多

一对一

一对一是关系型数据库的两张表中较为普遍的映射关系，比如，设备信息表 - 设备详情表；

设备信息表中存储的设备基本信息包括ip, hostname, idc, row, column, vendor, model, role，那么一台设备出了具备这些基本信息外，可能还包含其他额外的信息，比如：资产号，最近一次启动时间，运行总时长，操作系统镜像版本，运行状态，过保时间，是否过保，Console口管理地址，IPv6管理地址，等等。

那么常用的做法就是将这些额外的信息单独建立一张设备详情表，一是避免原始表的数据列过多，二是基本信息和详细信息的查询频率也略有差异，并不是任何时候都需要将这些信息都查出来，所以建两张表是较为合适的做法。

如上图所示，略微调整了一下设备信息表，将部分字段放在了设备详情表中，并且在两张表中都增加sn（资产号）字段作为主键，来唯一标识一台设备，这是因为在堆叠交换机中，主备两台设备的IP是相同的，但资产号一直是可以保持唯一的。

所以将设备信息表和设备详情表通过资产号进行关联，形成一对一对关系。

一对多

一对多在关系型数据库中是最为普遍的映射关系，因为一对一在不考虑过滤数据库范式的情况下，可以将其合并成一张表。

一对多比较好理解的例子就是设备信息表与设备端口表之间的关系，设备信息表中的一行数据可以表示一台设备，而一台设备可以具有多个端口，这多个端口在端口表中存储为多行，所以两张表之间就形成了一对多关系，如下：

如上图所示，设备端口表中需要有一列资产号字段，最终数据的内容中，多行端口信息的sn可能相同，这个sn就可以与设备进行关联。

多对多

当两张表存在多对多关系时，通常的做法是额外新增一张中间表来进行关联，将一个多对多转换为两个多对一。

由于我们对后端应用中暂时没有多对多的场景，大家暂时只做初步的了解即可，如果十分感兴趣的朋友可以自行多做研究。

【Flask关联查询】

定义模型

class Devices(db.Model):
    __tablename__ = "devices"
    sn = db.Column(db.String(128), primary_key=True, comment="资产号")
    ip = db.Column(db.String(16), nullable=False, comment="IP地址")
    hostname = db.Column(db.String(128), nullable=False, comment="主机名")
    idc = db.Column(db.String(32), comment="机房")
    vendor = db.Column(db.String(16), comment="厂商")
    model = db.Column(db.String(16), comment="型号")
    role = db.Column(db.String(8), comment="角色")
    created_at = db.Column(db.DateTime(), nullable=False, server_default=text('NOW()'), comment="创建时间")
    updated_at = db.Column(db.DateTime(), nullable=False, server_default=text('NOW()'), server_onupdate=text('NOW()'), comment="修改时间")

class DeviceDetail(db.Model):
    __tablename = "device_detail"
    sn = db.Column(db.String(128), db.ForeignKey(Devices.sn), primary_key=True, comment="资产号")
    ipv6 = db.Column(db.String(16), nullable=False, comment="IPv6地址")
    console_ip = db.Column(db.String(16), nullable=False, comment="console地址")
    row = db.Column(db.String(8), comment="机柜行")
    column = db.Column(db.String(8), comment="机柜列")
    last_start = db.Column(db.DateTime(), comment="最近启动时间")
    runtime = db.Column(db.Integer, comment="运行时长")
    image_version = db.Column(db.String(128), comment="镜像版本")
    over_warrant = db.Column(db.BOOLEAN, comment="是否过保")
    warrant_time = db.Column(db.DateTime(), comment="过保时间")
    created_at = db.Column(db.DateTime(), nullable=False, server_default=text('NOW()'), comment="创建时间")
    updated_at = db.Column(db.DateTime(), nullable=False, server_default=text('NOW()'), server_onupdate=text('NOW()'), comment="修改时间")

class Ports(db.Model):
    __tablename = "ports"
    sn = db.Column(db.String(128), db.ForeignKey(Devices.sn), primary_key=True, comment="资产号")
    port_id = db.Column(db.String(16), nullable=False, comment="端口ID")
    port_name = db.Column(db.String(64), nullable=False, comment="端口名称")
    port_type = db.Column(db.String(16), comment="端口类型")
    bandwidth = db.Column(db.Integer, comment="端口速率")
    link_status = db.Column(db.String(8), comment="链路状态")
    admin_status = db.Column(db.String(8), comment="管理状态")
    interface_ip = db.Column(db.String(16), comment="端口IP")
    vlan_id = db.Column(db.String(8), comment="端口所属VLAN")
    created_at = db.Column(db.DateTime(), nullable=False, server_default=text('NOW()'), comment="创建时间")
    updated_at = db.Column(db.DateTime(), nullable=False, server_default=text('NOW()'), server_onupdate=text('NOW()'), comment="修改时间")

创建数据表

三张表的SQL语句如下：

CREATE TABLE devices (
        sn VARCHAR(128) NOT NULL COMMENT '资产号', 
        ip VARCHAR(16) NOT NULL COMMENT 'IP地址', 
        hostname VARCHAR(128) NOT NULL COMMENT '主机名', 
        idc VARCHAR(32) COMMENT '机房', 
        vendor VARCHAR(16) COMMENT '厂商', 
        model VARCHAR(16) COMMENT '型号', 
        `role` VARCHAR(8) COMMENT '角色', 
        created_at DATETIME NOT NULL COMMENT '创建时间' DEFAULT NOW(), 
        updated_at DATETIME NOT NULL COMMENT '修改时间' DEFAULT NOW(), 
        PRIMARY KEY (sn)
)
CREATE TABLE device_detail (
        sn VARCHAR(128) NOT NULL COMMENT '资产号', 
        ipv6 VARCHAR(16) NOT NULL COMMENT 'IPv6地址', 
        console_ip VARCHAR(16) NOT NULL COMMENT 'console地址', 
        `row` VARCHAR(8) COMMENT '机柜行', 
        `column` VARCHAR(8) COMMENT '机柜列', 
        last_start DATETIME COMMENT '最近启动时间', 
        runtime INTEGER COMMENT '运行时长', 
        image_version VARCHAR(128) COMMENT '镜像版本', 
        over_warrant BOOL COMMENT '是否过保', 
        warrant_time DATETIME COMMENT '过保时间', 
        created_at DATETIME NOT NULL COMMENT '创建时间' DEFAULT NOW(), 
        updated_at DATETIME NOT NULL COMMENT '修改时间' DEFAULT NOW(), 
        PRIMARY KEY (sn), 
        FOREIGN KEY(sn) REFERENCES devices (sn)
)
CREATE TABLE ports (
        sn VARCHAR(128) NOT NULL COMMENT '资产号', 
        port_id VARCHAR(16) NOT NULL COMMENT '端口ID', 
        port_name VARCHAR(64) NOT NULL COMMENT '端口名称', 
        port_type VARCHAR(16) COMMENT '端口类型', 
        bandwidth INTEGER COMMENT '端口速率', 
        link_status VARCHAR(8) COMMENT '链路状态', 
        admin_status VARCHAR(8) COMMENT '管理状态', 
        interface_ip VARCHAR(16) COMMENT '端口IP', 
        vlan_id VARCHAR(8) COMMENT '端口所属VLAN', 
        created_at DATETIME NOT NULL COMMENT '创建时间' DEFAULT NOW(), 
        updated_at DATETIME NOT NULL COMMENT '修改时间' DEFAULT NOW(), 
        PRIMARY KEY (sn, port_id), 
        FOREIGN KEY(sn) REFERENCES devices (sn)
)

一对一

通常在主表中定义relationship，在附表中定义外键，如下：

class Devices(db.Model):
    __tablename__ = "devices"
    ...
    detail = db.relationship("DeviceDetail", uselist=False, backref="device")

class DeviceDetail(db.Model):
    __tablename = "device_detail"
    sn = db.Column(db.String(128), db.ForeignKey(Devices.sn), primary_key=True, comment="资产号")
    ...

1.上述代码中的relationship，是关联属性的意思，是SQLAlchemy提供给开发者快速引用外键模型的一个对象属性，本身并不存在于MySQL中；

2.relationship的参数backref表示反向引用，通过外键模型查询主模型数据时的关联属性，通俗的讲就是在查DeviceDetail数据时，可以通过backref引用到Devices。

3.useList表示关联模型是否为List，如果为False，则不使用列表，而使用标量值。一对一关系中，需要设置relationship中的uselist=Flase。

一对多

通常在“一”表中定义relationship，在“多”表中定义外键

class Devices(db.Model):
    __tablename__ = "devices"
    ...
    ports = db.relationship("Ports", uselist=True, backref="device", lazy='dynamic')

1.由于Deviecs表和Ports表直接为一对多，通过Devices会关联查询到一个或多个端口记录，所以需要将useList设为True

2.参数backref可以在Ports中自动创建一个device属性，作为Devices的反向引用

3.参数lazy决定了ORM框架何时从数据库中加载数据：

​ lazy='subquery'，查询当前数据模型时，采用子查询(subquery)，把外键模型的属性也瞬间查询出来了。

lazy=True或lazy='select'，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性时，才进行连表查询数据

lazy='dynamic'，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性并操作外键模型具体属性时，才进行连表查询数据

【Flask改造】

统一给三个模型都加上to_dict()和to_model()方法。

一对一获取设备详情的代码如下：

device = Devices.query.filter_by(sn=sn).first()
res = {**device, **device.detail.to_dict()}  # 通过device类的detail属性获取DeviceDetail的实例

上述代码中使用到了字典的一个小技巧，将多个字典合并可以使用{**dict1, dict2}

一对一添加设备详情的代码如下：

data = request.get_json()
device = Devices.to_model(**data)  # 生成Device模型实例
device.detail = DeviceDetail.to_model(**data)  # 生成DeviceDetail模型实例，并赋值给device对象
db.session.add(devices)  # 插入数据库
db.session.commit()  # 提交

一对多添加端口的代码如下：

def add_ports():
    data = request.get_json()
    if not isinstance(data, list):
        data = [data]
    sns = list(set([p.get("sn", "") for p in data]))  # 获取传入端口参数中的资产号，并去重
    devices = Devices.query.with_entities(Devices.sn).filter(Devices.sn.in_(sns)).all()  # 查询对应资产号的设备
    exists_sn = [d.sn for d in devices]  # 获取数据库中已存在的资产号
    try:
        ports = []
        for p in data:
            if p.get("sn", "") not in exists_sn:  # 如果端口所属的设备不存在，则返回错误
                return jsonify({"status_code": HTTPStatus.INTERNAL_SERVER_ERROR, "message": p.get("sn", "") + " device is not exists"})
            ports.append(Ports.to_model(**p))
        db.session.add_all(ports)
        db.session.commit()
        return jsonify({"status_code": HTTPStatus.OK})
    except Exception as e:
        return jsonify({"status_code": HTTPStatus.INTERNAL_SERVER_ERROR, "message": str(e)})

如代码中注释，需要在添加端口前判断是否已存在该端口所属的设备，如果设备不存在则应该直接返回错误，而实际上，即使不做这个判断，由于数据库中外键约束的存在，也会导致插入数据出错，但在接口编写时，应该遵循的原则是，将非法检查前置，避免压力集中在数据库上，这样有利于提高应用整体性能。

一对多获取设备端口的代码如下：

device = Devices.query.filter_by(sn=sn).first()
ports = [p.to_dict() for p in device.ports]  # 通过device类的ports属性获取Ports的实例
res = {**device.to_dict(), "ports": ports}

【总结】

这一章节我们对Flask-SQLAlchemy中关联查询的方法做了较为详细的讲解，并且从数据库层面分析了一对一，一对多等关系，除此之外还实现了一对一/多的查询和添加，其中使用到了一些较为Pythonic的语法和逻辑，需要大家慢慢消化。

最终整体的代码由于篇幅原因暂时就不放在文章中，如果有需要的朋友可以通过微信公众号加入读者交流群后获取。

---

欢迎大家添加我的个人公众号【Python玩转自动化运维】加入读者交流群，获取更多干货内容