一、前言
之前我们只是创建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