sqlalchemy 多对多创建表,以及添加查询操作

一,多对多表的创建:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column,INT,VARCHAR,ForeignKey
from sqlalchemy.orm import relationship

class Girls(Base):
    __tablename__ = "girl"  # 字段
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    g2b = relationship("Boys",backref="b2g",secondary="hotel")  #建立多对多关系

class Boys(Base):
    __tablename__ = "boy"  # 字段
    id = Column(Integer,primary_key=True)
    name = Column(String(32))

class Hotel(Base):
    __tablename__ = "hotel"  # 字段
    id = Column(Integer, primary_key=True)
    boy_id = Column(Integer,ForeignKey("boy.id"))
    girl_id = Column(Integer,ForeignKey("girl.id"))


from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:dd112211@127.0.0.1:3306/sqlalchemy?charset=utf8")

Base.metadata.create_all(engine)

1,新增数据

# 1.增加数据 - relationship 正向
girl_obj = Girls(name="妹子1")
girl_obj.g2b = [Boys(name="哥哥1"),Boys(name="哥哥2")]  #查Girl类中backref="b2g"
db_session.add(girl_obj)
db_session.commit()

# 2.增加数据 - relationship 反向
boy_obj = Boys(name="关关")
boy_obj.b2g = [Girls(name="美女1"),Girls(name="美女2")]
db_session.add(boy_obj)
db_session.commit()

图说:

2,查询

正向-- g2b

girl_obj_list = db_session.query(Girls).all()
for girl_obj in girl_obj_list:
    for boy in girl_obj.g2b:
        print(girl_obj.name,boy.name)

反向-- b2g

boy_obj_list = db_session.query(Boys).all()
for boy in boy_obj_list:
    for girl in boy.b2g:
        print(girl.name,boy.name)