python SQLAchemy多外键关联

关联同一张表的两个字段

Customer表有2个字段都关联了Address表

创建表结构

orm_many_fk.py 只创建表结构

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()


class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))

    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]) #foreign_keys 为了区分哪个外键对应哪个字段
    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:@localhost/test",encoding='utf-8')
Base.metadata.create_all(engine)  # 创建表结构

 

orm_api.py 添加数据。查询

 

from day11 import orm_many_fk
from sqlalchemy.orm import sessionmaker

Session_class = sessionmaker(bind=orm_many_fk.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class()  # 生成session实例 cursor

'''
addr1 = orm_many_fk.Address(street="Tiantongyuan",city="ChangPing",state="BeiJing")
addr2 = orm_many_fk.Address(street="Youryuan",city="ChaoYang",state="BeiJing")
addr3 = orm_many_fk.Address(street="baozipu",city="Haidian",state="BeiJing")
addr4 = orm_many_fk.Address(street="qingfeng",city="Haidian",state="BeiJing")

Session.add_all([addr1,addr2,addr3,addr4])
c1 = orm_many_fk.Customer(name="Alex",billing_address=addr1,shipping_address=addr2)
c2 = orm_many_fk.Customer(name="Jack",billing_address=addr3,shipping_address=addr3)

Session.add_all([c1,c2])
'''

obj = Session.query(orm_many_fk.Customer).filter(orm_many_fk.Customer.name=='alex').first()
print(obj.name,obj.billing_address,obj.shipping_address)

Session.commit()

 查询结果

多对多关系

现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是

1. 一本书可以有好几个作者一起出版
2. 一个作者可以写好几本书

 

 

此时你会发现，用之前学的外键好像没办法实现上面的需求了，因为

当然你更不可以像下面这样干，因为这样就你就相当于有多条书的记录了，太low b了，改书名还得都改。。。

 

那怎么办呢？ 此时，我们可以再搞出一张中间表，就可以了

这样就相当于通过book_m2m_author表完成了book表和author表之前的多对多关联。

创建表结构

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

Base = declarative_base()

book_m2m_author = Table('book_m2m_author', Base.metadata,
                        Column('book_id',Integer,ForeignKey('books.id')),
                        Column('author_id',Integer,ForeignKey('authors.id')),
                        )

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    #正向查询 通过 Book.authors 查book_m2m_author 关联到Author 反向 Author.books 查book_m2m_author 关联到Book
    authors = relationship('Author',secondary=book_m2m_author,backref='books') #secondary 声明第三张表 book_m2m_author

    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))

    def __repr__(self):
        return self.name

engine = create_engine("mysql+pymysql://root:@localhost/test",encoding='utf-8')
Base.metadata.create_all(engine)  # 创建表结构

添加数据

from day11 import orm_m2m
from sqlalchemy.orm import sessionmaker

Session_class = sessionmaker(bind=orm_m2m.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class()  # 生成session实例 cursor

b1 = orm_m2m.Book(name="lenarn python with Alex",pub_date="2014-01-02")
b2 = orm_m2m.Book(name="lenarn ZB with Alex",pub_date="2015-01-02")
b3 = orm_m2m.Book(name="lenarn paolu with Alex",pub_date="2016-01-02")

a1 = orm_m2m.Author(name="Alex")
a2 = orm_m2m.Author(name="Jack")
a3 = orm_m2m.Author(name="Rain")

b1.authors = [a1,a3]
b2.authors = [a2]
b3.authors = [a1,a2,a3]

Session.add_all([b1,b2,b3,a1,a2,a3])

Session.commit()

 

查询某个作者下的书

根据作者查书

author_obj = Session.query(orm_m2m.Author).filter(orm_m2m.Author.name =='alex').first()
print(author_obj.books)
print(author_obj.books[1].pub_date)

Session.commit()

 运行结果

[lenarn python with Alex, lenarn paolu with Alex]
2016-01-02

 根据书查作者

book_obj = Session.query(orm_m2m.Book).filter(orm_m2m.Book.id ==2).first()
print(book_obj.authors)
Session.commit()

  运行结果

[Rain, Jack, Alex]