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) #创建所有的表
注:这两行,如果再后面不加foreign_keys=[外键字段],就会报错误
billing_address = relationship("Address",foreign_keys=[billing_address_id]) shipping_address = relationship("Address",foreign_keys=[shipping_address_id])
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