day12-sqlalchemy 多外键关联

一、前言

　　之前我们只是创建1对1的外键关系，今天我们建立多个外键对应一个主键的外键关系。

二、表的创建

2.1、表创建

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


Base = declarative_base()  #orm基类

class Customer(Base):
    __tablename__ = "customer"
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    #创建两个外键，都指向address.id
    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])

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("mysql+pymysql://root:111111@120.26.225.159:3306/qigaodb",
                       encoding="utf-8")

Base.metadata.create_all(engine)  #创建所有的表

这两行注意了：

#因为有两个外键，所以不知道指向的是哪一个外键
billing_address = relationship("Address",foreign_keys=[billing_address_id])
shipping_address = relationship("Address",foreign_keys=[shipping_address_id])

 这两行，如果再后面不加foreign_keys=[外键字段]，就会报如下错误：

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
condition between parent/child tables on relationship
Customer.billing_address - there are multiple foreign key
paths linking the tables.  Specify the 'foreign_keys' argument,
providing a list of those columns which should be
counted as containing a foreign key reference to the parent table.

所以我们要加上指向的外键名，这样sqlachemy就能分清哪个外键是对应哪个字段了。

2.2、创建数据

from day12.orm_many_fk import many_fk   #many_fk就是上面的表结构，只不过放在另外一个模块中
from  sqlalchemy.orm import sessionmaker


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

addre1 = many_fk.Address(street="beicai",city="pudong",state="shanghai")
addre2 = many_fk.Address(street="anting",city="jiading",state="shanghai")
addre3 = many_fk.Address(street="lujia",city="kushan",state="suzhou")

session.add_all([addre1,addre2,addre3])  #创建address表的数据

c1 = many_fk.Customer(name="sbhong",billing_address=addre1,shipping_address=addre2)
c2 = many_fk.Customer(name="gaogege",billing_address=addre3,shipping_address=addre3)

session.add_all([c1,c2])  #创建customer数据


session.commit()

2.3、查询数据

from day12.orm_many_fk import many_fk
from  sqlalchemy.orm import sessionmaker


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

obj = session.query(many_fk.Customer).filter_by(name="sbhong").first()
print(obj.name,obj.billing_address,obj.shipping_address)

session.commit()

#输出
sbhong beicai anting