python orm多外键关联

创建表结构orm_multiForeignKey_fk.py文件

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer,primary_key=True)
    name = Column(String)
    billing_address_id = Column(Integer,ForeignKey('address.id'))
    shipping_address_id = Column(Integer, ForeignKey('address.id'))

    billing_address = relationship('Address',foreign_keys = [billing_address_id])
    shipping_address = relationship('Address',foreign_keys = [shipping_address_id])

    def __repr__(self):
        return self.name

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer,primary_key=True)
    street = Column(String(64))
    city = Column(String(64))
    state = Column(String(64))
    def __repr__(self):
        return self.street
engine = create_engine('sqlite:///F:\软件下载\自动化测试软件\pycharmProfessional\workspace\c14\sudentsInfo.db3')
Base.metadata.create_all(engine)

创建数据及查询orm_multiForeignKey_fk_api.py文件：

from day11 import orm_multiForeignKey_fk
from sqlalchemy.orm import sessionmaker

session_class = sessionmaker(bind=orm_multiForeignKey_fk.engine)
session = session_class()

#添加数据
# addr1 = orm_multiForeignKey_fk.Address(street = 'street1',city = 'city1',state = 'state1')
# addr2 = orm_multiForeignKey_fk.Address(street = 'street2',city = 'city2',state = 'state2')
# addr3 = orm_multiForeignKey_fk.Address(street = 'street3',city = 'city3',state = 'state3')
# session.add_all([addr1,addr2,addr3])
# c1 = orm_multiForeignKey_fk.Customer(name = 'Tim',billing_address = addr1,shipping_address = addr2)
# c2 = orm_multiForeignKey_fk.Customer(name = 'kite',billing_address = addr3,shipping_address = addr3)
# session.add_all([c1,c2])
# session.commit()

obj = session.query(orm_multiForeignKey_fk.Customer).filter(orm_multiForeignKey_fk.Customer.name == 'Tim').first()
print(obj.name,obj.billing_address,obj.shipping_address)

输出结果：

Tim street1 street2

customer表可共用地址表address数据，通过address中的id关联起来