SQLAlchemy 数据表之多对多 (ManyToMany)

一、创建表及关系

from sqlalchemy.ext.declarative import declarative_base

# 创建基类
Model = declarative_base()

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship


# 创建女生表
class Girls(Model):
    __tablename__ = "girl"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=True)
　　 # 创建关系
    g2b = relationship("Boys", secondary="hotel", backref="b2g")


# 创建男生表
class Boys(Model):
    __tablename__ = "boy"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=True)


# 创建第三张表
class Hotels(Model):
    __tablename__ = "hotel"
    id = Column(Integer, primary_key=True)
    girl_id = Column(Integer, ForeignKey("girl.id"))
    boy_id = Column(Integer, ForeignKey("boy.id"))


from sqlalchemy.engine import create_engine

# 创建数据库引擎
engine = create_engine("mysql+pymysql://root:1234@127.0.0.1:3306/db4?charset=utf8")

# 检索所有继承 Model 的Object 并在engine指向的数据库中创建 所有的表
Model.metadata.create_all(engine)

# 删除所有的表
# Model.metadata.drop_all(engine)

二、基于relationship增加数据

from my_M2M import Girl,Boy,Hotel,engine

# 创建连接
from sqlalchemy.orm import sessionmaker
# 创建数据表操作对象 sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()

# 1.通过Boy添加Girl和Hotel数据 反向添加数据
boy = Boy(name="avicii")
boy.b2g = [Girl(name="taylor"),Girl(name="sia")]
db_session.add(boy)
db_session.commit()


# 2.通过Girl添加Boy和Hotel数据 正向添加数据
girl = Girl(name="珊珊")
girl.g2b = [Boy(name="alan")]
db_session.add(girl)
db_session.commit()

三、基于relationship查询数据

from my_M2M import Girl,Boy,Hotel,engine

# 创建连接
from sqlalchemy.orm import sessionmaker
# 创建数据表操作对象 sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()

# 1.通过Boy查询约会过的所有Girl  反向查询
hotel = db_session.query(Boy).all()
for row in hotel:
    for row2 in row.girl2boy:
        print(row.name,row2.name)

# 2.通过Girl查询约会过的所有Boy  正向查询
hotel = db_session.query(Girl).all()
for row in hotel:
    for row2 in row.boys:
        print(row.name,row2.name)