【Python/数据库】SQLAlchemy一对多,多对多操作

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

  1. RESTRICT：若子表中有父表对应的关联数据，删除父表对应数据，会阻止删除。默认项
  2. NO ACTION：在MySQL中，同RESTRICT。
  3. CASCADE：级联删除。
  4. 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:DragonFire@127.0.0.1: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)
posted @   浅吟清风  阅读(359)  评论(0)  编辑  收藏  举报
相关博文：
· 【Python/数据库】SQLAlchemy基础操作
· awk——文本和数据进行处理的编程语言
· python—sqlalchemy简单使用方法，一对多，多对多，双向关系教程
· python—sqlalchemy简单使用方法，一对多，多对多，双向关系教程
· sqlalchemy关系操作
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛