SQLAlchemy一对多操作
1. 创建多表
# create_table_ForeginKey.py
from sqlalchemy.ext.declarative import declarative_base
from salalchemy import Column, INT, VARCHAR, ForeignKey
from sqlalchemy import create_engine
Base = declarative_base()
class Student(Base):
__tablename__ = 'student'
id = Column(INT, primary_key=True)
name = Column(VARCHAR(32))
school_id = Column(INT, ForeignKey('school.id'))
class School(Base):
__tablename__ = 'school'
id = Column(INT, primary_key=True)
name = Column(VARCHAR(32))
engine = create_engine('mysql+pymysql://root:root@localhost:3306/fast_demo?charset=utf8')
Base.metadata.create_all(engine)
2. 多表insert
1.原始增加数据 （笨）
# crud_insert_ForeignKey.py
from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine
Session = sessionmaker(engine)
db_session = Session()
sch_obj = School(name='pekingUniversity')
db_session.add(sch_obj)
db_session.commit()
db_session.close()
sch_obj = db_session.query(School).filter(School.name == 'pekingUniversity').first()
stu_obj = Student(name='zs', school_id=sch_obj.id)
db_session.add(stu_obj)
db_session.commit()
db_session.close()
2. 增加数据（relationship 方法）(推荐)
在create_table_ForeignKey.py
# create_table_ForeginKey.py
from sqlalchemy.ext.declarative import declarative_base
from salalchemy import Column, INT, VARCHAR, ForeignKey
from sqlalchemy import create_engine
+ from sqlalchemy.orm import relationship
Base = declarative_base()
Class Student(Base):
__table__ = 'student'
id = Column(INT, primary_key=True)
name = Column(VARCHAR(32))
school_id = Column(INT, ForeignKey('school.id', ondelete='SET NULL'))
+ stu2sch = relationship('School'，backref='sch2stu')
Class School(Base):
__table__ = 'school'
id = Column(INT, primary_key=True)
name = Column(VARCHAR(32))
engine = create_engine('mysql+pymysql://root:root@localhost:3306/fast_demo?charset=utf8')
Base.metadata.create_all(engine)
1. 正向
# crud_insert_ForeignKey.py
from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine, School, Student
Session = sessionmaker(engine)
db_session = Session()
stu_obj = Student(name='zs', stu2sch=School(name='pekingUnivesity'))
db_session.add(stu_obj)
db_session.commit()
db_session.close()
2. 反向
# crud_insert_ForeignKey.py
from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine, School, Student
Session = sessionmaker(engine)
db_session = Session()
sch_obj = School(name='Tsu')
sch_obj.sch2stu = [Student(name='zhangsan'),Student(name='lisi')]
db_session.add(sch_obj)
db_session.commit()
db_session.close()
3. 多表select
1. 原始查询数据（笨）
# crud_select_ForeignKey.py
from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine，
Session = sessionmaker(engine)
db_session = Session()
# 查询
sch_obj = db_session.query(School).filter(School.name == 'pekingUniversity').first()
beijing_stu_obj = db_session.query(Student).filter(Student.school.id == sch_obj,id).first()
print(beijing_stu_obj.name,sch_obj.name)
2. 查询数据（relationship方法）（推荐）
在create_table_ForeginKey.py
# create_table_ForeginKey.py
from sqlalchemy.ext.declarative import declarative_base
from salalchemy import Column, INT, VARCHAR, ForeignKey
from sqlalchemy import create_engine
+ from sqlalchemy.orm import relationship
Base = declarative_base()
Class Student(Base):
__table__ = 'student'
id = Column(INT, primary_key=True)
name = Column(VARCHAR(32))
school_id = Column(INT, ForeignKey('school.id', ondelete='SET NULL'))
+ stu2sch = relationship('School'，backref='sch2stu')
Class School(Base):
__table__ = 'school'
id = Column(INT, primary_key=True)
name = Column(VARCHAR(32))
engine = create_engine('mysql+pymysql://root:root@localhost:3306/fast_demo?charset=utf8')
Base.metadata.create_all(engine)
1. 正向
# crud_select_ForeignKey.py
from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine，
Session = sessionmaker(engine)
db_session = Session()
# 查询
stu_obj = db_session.query(Student).filter(Student.name=='Tsu').first()
print(stu_obj.name, stu_obj.stu2sch.name)
2. 反向
# crud_select_ForeignKey.py
from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine，
Session = sessionmaker(engine)
db_session = Session()
# 查询
sch_obj_list = db_session.query(School).all()
for sch in sch_obj_list:
for stu in row.sch2stu:
print(sch.name, stu.name)
4.多表update
# crud_update_ForeignKey.py
from my_ForeignKey import Student, ClassTable,engine
from sqlalchemy.orm import sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()
# 更新
class_info = db_session.query(ClassTable).filter(ClassTable.name=="OldBoyS1").first()
db_session.query(Student).filter(Student.class_id == class_info.id).update({"name":"NBDragon"})
db_session.commit()
db_session.close()
5. 多表delete
RESTRICT：若子表中有父表对应的关联数据，删除父表对应数据，会阻止删除。默认项NO ACTION：在MySQL中，同RESTRICT。CASCADE：级联删除。SET NULL：父表对应数据被删除，子表对应数据项会设置为NULL。
# crud_delete_ForeignKey.py
from my_ForeignKey import Student, ClassTable,engine
from sqlalchemy.orm import sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()
# 删除
class_info = db_session.query(ClassTable).filter(ClassTable.name=="OldBoyS1").first()
db_session.query(Student).filter(Student.class_id == class_info.id).delete()
db_session.commit()
db_session.close()
SQLAlchemy多对多操作
1. 创建表及关系
# create_m2m.py
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import relationship
class Hotel(Base):
__tablename__="hotel"
id=Column(Integer,primary_key=True)
girl_id = Column(Integer,ForeignKey("girl.id"))
boy_id = Column(Integer,ForeignKey("boy.id"))
class Girl(Base):
__tablename__="girl"
id=Column(Integer,primary_key=True)
name = Column(String(32),index=True)
#创建关系
boys = relationship("Boy",secondary="hotel",backref="girl2boy")
class Boy(Base):
__tablename__="boy"
id=Column(Integer,primary_key=True)
name = Column(String(32),index=True)
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:[email protected]:3306/dragon?charset=utf8")
Base.metadata.create_all(engine)
2. 基于relationship增加数据
# crud_insert_m2m.py
from my_M2M import Girl,Boy,Hotel,engine
# 创建连接
from sqlalchemy.orm import sessionmaker
# 创建数据表操作对象 sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()
# 1.通过Boy添加Girl和Hotel数据
boy = Boy(name="DragonFire")
boy.girl2boy = [Girl(name="赵丽颖"),Girl(name="Angelababy")]
db_session.add(boy)
db_session.commit()
# 2.通过Girl添加Boy和Hotel数据
girl = Girl(name="珊珊")
girl.boys = [Boy(name="Dragon")]
db_session.add(girl)
db_session.commit()
3. 基于relationship查询数据
# ocrud_select_m2m.py
from my_M2M import Girl,Boy,Hotel,engine
# 创建连接
from sqlalchemy.orm import sessionmaker
# 创建数据表操作对象 sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()
# 1.通过Boy查询约会过的所有Girl
hotel = db_session.query(Boy).all()
for row in hotel:
for row2 in row.girl2boy:
print(row.name,row2.name)
# 2.通过Girl查询约会过的所有Boy
hotel = db_session.query(Girl).all()
for row in hotel:
for row2 in row.boys:
print(row.name,row2.name)