from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import relationship

BaseModel = declarative_base()
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s21?charset=utf8")


class Girl(BaseModel):
    __tablename__ = "girl"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)

    gyb = relationship("Boy", backref="byg", secondary="hotel")  # secondary="hotel" 数据表中的数据才能证明两者关系


class Boy(BaseModel):
    __tablename__ = "boy"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)


class Hotel(BaseModel):
    __tablename__ = "hotel"
    id = Column(Integer, primary_key=True)
    bid = Column(Integer, ForeignKey("boy.id"))
    gid = Column(Integer, ForeignKey("girl.id"))


BaseModel.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine import create_engine
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s21?charset=utf8")

select_db = sessionmaker(engine)
db_session = select_db()

# 增加数据 relationship 正向添加
g = Girl(name="赵丽颖", gyb=[Boy(name="DragonFire"), Boy(name="冯绍峰")])
db_session.add(g)

# 增加数据 relationship 反向添加
b = Boy(name="李杰")
b.byg = [
    Girl(name="罗玉凤"),
    Girl(name="朱利安"),
    Girl(name="乔碧萝")
]
#
db_session.add(b)
# db_session.commit()
# db_session.close()


# 查询 relationship 正向
res = db_session.query(Girl).all()
for g in res:
    print(g.name, len(g.gyb))

# 查询 relationship 反向
res = db_session.query(Boy).all()
for b in res:
    print(b.name, len(b.byg))

db_session.commit()
db_session.close()