外键的创建
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外键的使用(二)