SQLAlchemy之外键、多对多
目录
ForeignKey (一对多)
创建数据表
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship # 关系映射
Base = declarative_base()
class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(8), nullable=False)
# 关联字段, 让school_id 与 School的id进行关联,(ForeignKey一定要是表名 .id不是对象名)
school_id = Column(Integer, ForeignKey('school.id', ondelete='CASCADE'), nullable=False)
# 将Student与School 创建关系, 不是字段, backref是反向关联的关键字
stu_school = relationship('School', backref='stu_school')
class School(Base):
__tablename__ = 'school'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(16), nullable=False)
my_engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/alchemy?charset=utf8')
Base.metadata.create_all(my_engine)
基于relationship增加数据
from foreignKey import Student, School, my_engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(my_engine)
db_session = Session()
1. 简单的增加数据
db_session.add(School(name="beijing"))
db_session.commit()
# 添加一个学生 jason 学校是 beijing
school_obj = db_session.query(School).filter(School.name == "beijing").first()
stu = Student(name="jason",school_id = school_obj.id)
db_session.add(stu)
db_session.commit()
2. 增加数据 relationship 正向版
stu = Student(name='jason', stu_school=School(name='beijing'))
db_session.add(stu)
db_session.commit()
重点: 通过关系列 stu_school 可以做到两件事
- 在School表中添加一条数据
- 在Student表中添加一条数据并将刚刚添加的School的数据id填写在Student的school_id中
3. 增加数据 relationship 反向版
sch = School(name='hangzhou') # 建立School数据
# 通过 backref=stu_school 反向关联字段, 向 Student 数据表中添加 2条数据
sch.stu_school = [Student(name='ran'), Student(name='mei')] # 添加多条外键关系数据
db_session.add(sch)
db_session.commit()
db_session.close()
基于relationship查询数据
1. 查询数据 relationship 正向版
res = db_session.query(Student).filter(Student.id==1).first()
# 通过 relationship 中 stu_school 获取关联的数据
print(res.id, res.name, res.stu_school.name)
# 多条数据
res = db_session.query(Student).all()
for item in res:
print(item.id, item.name, item.stu_school.name)
2. 查询数据 relationship 反向版
res = db_session.query(School).all()
# 通过 backref 中的 stu_school 反向关联到数据表获取数据
for item in res:
print(item.id, item.name, len(item.stu_school))
for stu in item.stu_school:
print(item.id, item.name, stu.name)
db_session.close()
更改数据
school_info = db_session.query(School).filter(School.name=="hangkong").first()
db_session.query(Student).filter(Student.school_id == school_info.id).update({"name":"jason"})
db_session.commit()
db_session.close()
删除数据
res = db_session.query(Student).filter(Student.school_id==1).delete()
db_session.commit()
db_session.close()
ManyToMany (多对多)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine
from sqlalchemy import String, Integer, Column, ForeignKey
from sqlalchemy.orm import relationship
Base = declarative_base()
class Girl(Base):
__tablename__ = 'girl'
id = Column(Integer, primary_key=True)
name = Column(String(15), nullable=False)
g2b = relationship('Boy', backref='b2g', secondary="home") # 创建关系
class Boy(Base):
__tablename__ = 'boy'
id = Column(Integer, primary_key=True)
name = Column(String(15), nullable=False)
class Home(Base):
__tablename__ = 'home'
id = Column(Integer, primary_key=True)
boy_id = Column(Integer, ForeignKey("boy.id"))
girl_id = Column(Integer, ForeignKey("girl.id"))
my_engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/alchemy?charset=utf8")
Base.metadata.create_all(my_engine)
基于relationship增加数据
1. 添加数据relationship 正向版
g = Girl(name='mei')
g.g2b = [Boy(name='jason'), Boy(name='jack')]
db_session.add(g)
db_session.commit()
2. 添加数据relationship 反向版
b = Boy(name='jie')
# 通过 backref 添加数据
b.b2g = [Girl(name='ran'), Girl(name='meng')]
db_session.add(b)
db_session.commit()
db_session.close()
基于relationship查询数据
1. 查询数据relationship 反向版
res = db_session.query(Boy).all()
for item in res:
for ret in item.b2g:
print(item.id, item.name, ret.name)
2. 查询数据relationship 正向版
res = db_session.query(Girl).all()
for item in res:
for ret in item.g2b:
print(item.id, item.name, ret.name)
更新数据
h = Home(boy_id=3, girl_id=1)
db_session.add(h)
db_session.commit()
