Flask探究-无外键情况下Sqlachemy join的用法

大家好，我是金鱼座，一个走在测试领域这片蓝海中， 蹉跎前行的技术渣渣，唯有一直走下去，也许能改变点什么，加油！

不知道是因为什么。公司大佬，都不喜欢用外键，虽然我也不知道为什么，但是也默认建表不用外键的原则

言归正传

何为无外键（外行人可能不太知道）

无外键建表指的是当我们建立数据库表时，通过sqlachemy建立的表模型中，不使用ForeignKey，以及配套的relationship关联，
对比下图有外键和无外键

# 不使用外键（role_id）
class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # 编号
    name = db.Column(db.String(100), unique=True)  # 管理员账号
    pwd = db.Column(db.String(100))  # 管理员密码
    role_id = db.Column(db.Integer)  # 所属角色
    addtime = db.Column(db.DateTime, index=True, default=datetime.now)  # 添加时间

    def __repr__(self):
        return "<User %r>" % self.name

# 使用外键（s_g）
class Student(db.Model):
    s_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    s_name = db.Column(db.String(20), unique=True)
    s_age = db.Column(db.Integer, default=8)
    s_g = db.Column(db.Integer, db.ForeignKey('grade.g_id'),  nullable=True)  # 
    # 'grade.g_id'必须要小写
    __table__name = 'student'

如果不使用外键，两表如何关联呢？

先看两个表,分别为用例表和产品表

class _Case(db.Model):
    __tablename__ = "case"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    proid = db.Column(db.Integer, nullable=False)
    menuid = db.Column(db.Integer, nullable=False)
    casecode = db.Column(db.String(10), nullable=False)
    casetype = db.Column(db.String(2), nullable=False)
    casepre = db.Column(db.String(100), nullable=False)
    casedesc = db.Column(db.Text(100), nullable=False)
    casestep= db.Column(db.Text(500), nullable=False)
    caseresult = db.Column(db.String(100), nullable=False)
    status = db.Column(db.String(10), nullable=True)
    createtime = db.Column(db.DateTime, index=True, default=datetime.now)
    updatetime = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)

    def __repr__(self):
        return "_Case is %r" % self.casedesc

class Product(db.Model):
    __tablename__ = "product"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50), nullable=False)
    code = db.Column(db.String(5), nullable=False)
    # url = db.Column(db.String(1000), nullable=False)
    createtime = db.Column(db.DateTime, index=True, default=datetime.now)
    updatetime = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)

    def __repr__(self):
        return "Product is %r" % self.name

关联查询

第一种：只需要获取左表Model中的数据

pagedata1 = _Case.query.join(Product, Product.id == _Case.proid).
order_by(_Case.createtime.desc()).paginate(page=page, per_page=10)
eq:
SELECT `case`.id AS case_id, `case`.proid AS case_proid, `case`.menuid AS case_menuid, `case`.casecode AS case_casecode, `case`.casetype AS case_casetype, `case`.casepre AS case_casepre, `case`.casedesc AS case_casedesc, `case`.casestep AS case_casestep, `case`.caseresult AS case_caseresult, `case`.status AS case_status, `case`.createtime AS case_createtime, `case`.updatetime AS case_updatetime 
FROM `case` INNER JOIN product ON product.id = `case`.proid ORDER BY `case`.createtime DESC

结果返回

image.png

前端配套：

<tbody>
{% for data in pagedata.items %}

    <tr role="row" class="odd">
        <td class="sorting_1">{{ data.id }}</td>
        <td>{{ data.name }}</td>
        <td>{{ data.code }}</td>
{#                                                <td>{{ data.name[1]}}</td>#}
        <td>{{ data.createtime }}</td>
        <td>
            <a href="{{ url_for("admin.algo_edit", id=data.id) }}" class="label label-success">编辑</a>
            <a href="{{ url_for("admin.algo_del", id=data.id) }}" class="label label-danger">删除</a>
        </td>
    </tr>
{% endfor %}
</tbody>

第二种： 想获取左连接所有的字段值

pagedata = db.session.query(_Case, Product).join(Product, _Case.proid == Product.id).
order_by(_Case.createtime.desc()).paginate(page=page, per_page=10)
eq：
SELECT `case`.id AS case_id, `case`.proid AS case_proid, `case`.menuid AS case_menuid, `case`.casecode AS case_casecode, `case`.casetype AS case_casetype, `case`.casepre AS case_casepre, `case`.casedesc AS case_casedesc, `case`.casestep AS case_casestep, `case`.caseresult AS case_caseresult, `case`.status AS case_status, `case`.createtime AS case_createtime, `case`.updatetime AS case_updatetime, product.id AS product_id, product.name AS product_name, product.code AS product_code, product.createtime AS product_createtime, product.updatetime AS product_updatetime 
FROM `case` INNER JOIN product ON `case`.proid = product.id ORDER BY `case`.createtime DESC 

返回结果

image.png

前端配套：

 {% for data in pagedata.items %}
{#                                        {% for data in pagedata %}#}
        <tr role="row" class="odd">
            <td class="sorting_1">{{ data["_Case"].casecode }}</td>
            <td>{{ data["_Case"].casedesc }}</td>
            <td>{{ data["_Case"].caseresult }}</td>
            {#                                                <td>{{ data.name[1]}}</td>#}
            <td>{{ data["Product"].name }}</td>
            <td>{{ data["_Case"].createtime }}</td>
            <td>
                <a  href=""
                   class="label label-success">编辑</a>
                <a href=""
                   class="label label-danger">删除</a>
            </td>
        </tr>
    {% endfor %}
    </tbody>

第三种：只想获取指定字段的值

db.session.query(_Case.proid, _Case.casedesc, Product.name).
join(Product, _Case.proid == Product.id).all()
eq：
SELECT `case`.proid AS case_proid, `case`.casedesc AS case_casedesc, product.name AS product_name 
FROM `case` INNER JOIN product ON `case`.proid = product.id
>
[(1, '12', '动态人脸'), (1, '用例描述', '动态人脸'), (2, '静态描述', '静态人脸')]

返回的结果

指定字段返回.png

转载大佬：
https://blog.zengrong.net/post/2656.html#
https://www.cnblogs.com/clement-jiao/p/10010497.html