sqlalchemy外键的使用(二)

外键的创建

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

# ORM精髓 relationship
from sqlalchemy.orm import relationship

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


# 一对多
class School(BaseModel):
    __tablename__ = "school"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)


class Student(BaseModel):
    __tablename__ = "student"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    sch_id = Column(Integer, ForeignKey("school.id"))

    # 关系映射
    stu2sch = relationship("School", backref="sch2stu")

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

# 增加数据
# 先建立一个学校 再查询这个学校的id 利用这个ID 再去创建学生添加sch_id
# relationship 正向添加 relationship字段出现在哪个类
stu = Student(name="DragonFire",stu2sch=School(name="OldBoyBeijing"))

# relationship 反向添加
sch = School(name="OldBoyShanghai")
sch.sch2stu = [
    Student(name="赵丽颖"),
    Student(name="冯绍峰")
]
db_session.add(sch)


# 查询 relationship 正向
res = db_session.query(Student).all()
for stu in res:
    print(stu.name,stu.stu2sch.name)

# 查询 relationship 反向
res = db_session.query(School).all()
for sch in res:
    print(sch.name,len(sch.sch2stu)) #学校里面有多少学生
    for stu in sch.sch2stu:
        print(sch.name,stu.name)

db_session.commit()
db_session.close()

版权声明:除特别注明外,本站所有文章均为王晨曦个人站点原创

转载请注明:出处来自王晨曦个人站点 » sqlalchemy外键的使用(二)

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注