python 多字段外键关联

XinShun

已于 2024-01-04 17:44:35 修改

阅读量539

点赞数 9

CC 4.0 BY-SA版权

文章标签： python 数据库开发语言

于 2024-01-04 17:39:23 首次发布

本文链接：https://blog.csdn.net/XinShun/article/details/135392471

本文介绍了如何在Flask应用中使用SQLAlchemyORM进行多对多关系的设置，包括backref和外键约束的使用，并提供了查询特定路线关联行的示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、参考https://docs.sqlalchemy.org/en/20/orm/backref.html

https://blog.csdn.net/u012206617/article/details/91436606

https://www.codenong.com/43024464/

2、示例代码如下：

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import and_, ForeignKeyConstraint
from sqlalchemy.orm import relationship


con_sql = 'mssql+pymssql://test:test@test/test?charset=utf8'
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = con_sql
app.config["SQLALCHEMY_ECHO"] = True
db = SQLAlchemy(app)


class ALines(db.Model):
    __tablename__ = "TRUCK_LINES"
    ASTN = db.Column(db.String(3), primary_key=True, nullable=False, info='')
    BSTN = db.Column(db.String(3), primary_key=True, nullable=False, info='')

    ROUTES = relationship(
        "ALineRoute",
        uselist=True,
        primaryjoin=(
            "and_(ALines.ASTN==ALineRoute.ASTN, ALines.BSTN==ALineRoute.BSTN)"
        ),
        backref="LINE",
    )


class ALineRoute(db.Model):
    __tablename__ = "TRUCK_LINES_ROUTES"
    ROUTE_ID = db.Column(db.BigInteger, primary_key=True, nullable=False, info='')
    ASTN = db.Column(db.String(3), db.ForeignKey('TRUCK_LINES.ASTN'), info='')
    BSTN = db.Column(db.String(3), db.ForeignKey('TRUCK_LINES.BSTN'), info='')

    ForeignKeyConstraint(('ALineRoute.ASTN', 'ALineRoute.BSTN'), ['ALineRoute.ASTN', 'ALineRoute.BSTN'])

    # LINE = relationship("ALines")


class TestRelationShip(object):

    def test_trucklines(self):
        route_entities = db.session.query(ALineRoute)\
            .filter(and_(ALineRoute.ASTN == 'TEST1',
                         ALineRoute.BSTN == 'TEST2')).all()
        entity, *_ = route_entities
        line = entity.LINE

        line_entities = db.session.query(ALines)\
            .filter(and_(ALines.ASTN == 'TEST1',
                         ALines.BSTN == 'TEST2')).all()
        entity, *_ = line_entities
        routes = entity.ROUTES

        return route_entities


if __name__ == "__main__":
    db_api = TestRelationShip()
    db_api.test_trucklines()