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

转载于:https://www.cnblogs.com/xiangjun555/articles/8033083.html