python 多字段外键关联

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()