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)

本栏目推荐文章
• Python学习笔记
• Jmeter 如何连接mysql数据库？
• SQL Server 检测是不是数字型数据的两种方法
• python字典的使用
• python字符串方法
• python列表和元组使用介绍
• python | 小游戏 开局托儿所 自动化脚本 pyautogui
• python中的Map和Filter
• python-爬元气桌面壁纸
• 如何通过Navicat把MySQL远程数据库导入到本地数据库

SQLAlchemy 数据库 数据 Pythonsqlalchemy数据库 数据python sqlalchemy性能 数据python sqlalchemy数据 bulk_save_objects sqlalchemy objects数据 asyncio sqlalchemy数据库 数据 数据sqlalchemy实战fastapi sqlalchemy数据表flask-script信号 sqlalchemy fastapi数据库 项目 sqlalchemy fastapi schemas数据 sqlalchemy数据库 基础 数据