• sqlalchemy 一对多的增删改查操作


    一、创建表以及数据库的连接

     1 # 一对多建表操作, 关系型表
     2 from crud.create_table import engine
     3 from sqlalchemy.ext.declarative import declarative_base
     4 
     5 Base = declarative_base()
     6 from sqlalchemy import String, Column, Integer, ForeignKey
     7 from sqlalchemy.orm import relationship
     8 
     9 """
    10 正向按字段,反向按backref
    11 """
    12 
    13 
    14 class Student(Base):
    15     __tablename__ = "student"
    16     id = Column(Integer, primary_key=True, autoincrement=True)
    17     name = Column(String(32),)
    18     school_id = Column(Integer, ForeignKey("school.id"))
    19 
    20     stu2sch = relationship("School", backref="sch2stu")
    21 
    22 
    23 class School(Base):
    24     __tablename__ = "school"
    25     id = Column(Integer, primary_key=True)
    26     name = Column(String(32))
    27 
    28 
    29 Base.metadata.create_all(engine)
    创建表以及连接数据库

    二、数据的增删改查

    1、数据的添加

    from crud.create_table import engine
    from sqlalchemy.orm import sessionmaker
    from crud_foreignkey.create_table_foreignkey import Student, School
    
    Session = sessionmaker(engine)
    db_session = Session()
    
    # 添加数据1
    # 创建学校
    # sch_obj = School(name="北京校区")
    # db_session.add(sch_obj)
    """
    查询出学校的id,将学校的id赋给学生对象
    """
    sch = db_session.query(School).filter(School.name == '北京校区').first()
    stu_obj = Student(name='张三', school_id=sch.id)
    db_session.add(stu_obj)
    
    # 添加数据2 正向relationship
    """
    1、创建学生对象,
    2、在学生对象里添加学校对象:stu2sch=School(name="上海校区")
    """
    # stu_obj = Student(name="李四", stu2sch=School(name="上海校区"))
    # db_session.add(stu_obj)
    
    # 添加数据3 反向relationship
    """
    1、反向创建学校对象
    2、学校对象.sch2stu = [多个学生对象]
    """
    sch_obj = School(name="深圳校区")
    # 添加多个对象
    sch_obj.sch2stu = [Student(name="赵六"), Student(name='陈琦')]
    db_session.add(sch_obj)
    
    
    db_session.commit()
    db_session.close()
    View Code

    2、数据的查询

     1 from crud_foreignkey.create_table_foreignkey import School, Student
     2 from crud.create_table import engine
     3 from sqlalchemy.orm import sessionmaker
     4 
     5 Session = sessionmaker(engine)
     6 db_session = Session()
     7 
     8 # 正向查询
     9 stu = db_session.query(Student).all()
    10 for row in stu:
    11     print(row.id, row.name, row.school_id, row.stu2sch.name)
    12 """
    13 通过学生表去查询校区表,根据stu2sch.去查询
    14 """
    15 # 反向查询
    16 sch = db_session.query(School).all()
    17 for school in sch:
    18     for student in school.sch2stu:
    19         print(student.name, student.id, student.stu2sch.name)
    20 # print(school.sch2stu) 打印出每个校区的所有学生 如下:
    21 """
    22 [<crud_foreignkey.create_table_foreignkey.Student object at 0x0000028105137898>]
    23 [<crud_foreignkey.create_table_foreignkey.Student object at 0x0000028105137668>]
    24 [<crud_foreignkey.create_table_foreignkey.Student object at 0x0000028105137908>, 
    25 <crud_foreignkey.create_table_foreignkey.Student object at 0x0000028105137978>]
    26 
    27 """
    28 db_session.commit()
    29 db_session.close()
    View Code

    3、数据的修改

     1 from crud.create_table import engine
     2 from crud_foreignkey.create_table_foreignkey import School, Student
     3 from sqlalchemy.orm import sessionmaker
     4 Session = sessionmaker(engine)
     5 db_session = Session()
     6 
     7 # 修改数据,修改学生的school_id
     8 sch = db_session.query(School).filter(School.name == "上海校区").first()
     9 db_session.query(Student).filter(Student.name == "陈琦").update({"school_id": sch.id})
    10 
    11 db_session.commit()
    12 db_session.close()
    View Code

    4、数据的删除

     1 from crud.create_table import engine
     2 from crud_foreignkey.create_table_foreignkey import School, Student
     3 from sqlalchemy.orm import sessionmaker
     4 Session = sessionmaker(engine)
     5 db_session = Session()
     6 
     7 # 删除所有上海校区的学生
     8 sch_obj = db_session.query(School).filter(School.name == '上海校区').first()
     9 db_session.query(Student).filter(Student.id == sch_obj.id).delete()
    10 
    11 db_session.commit()
    12 db_session.close()
    View Code

    三、其他的查询方式

     1 ret = session.query(User).filter_by(name='DragonFire').all()
     2 ret = session.query(User).filter(User.id > 1, User.name == 'DragonFire').all()
     3 ret = session.query(User).filter(User.id.between(1, 3), User.name == 'DragonFire').all() # between 大于1小于3的
     4 ret = session.query(User).filter(User.id.in_([1,3,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的
     5 ret = session.query(User).filter(~User.id.in_([1,3,4])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的
     6 ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='DragonFire'))).all() 子查询
     7 from sqlalchemy import and_, or_
     8 ret = session.query(User).filter(and_(User.id > 3, User.name == 'DragonFire')).all()
     9 ret = session.query(User).filter(or_(User.id < 2, User.name == 'DragonFire')).all()
    10 ret = session.query(User).filter(
    11     or_(
    12         User.id < 2,
    13         and_(User.name == 'eric', User.id > 3),
    14         User.extra != ""
    15     )).all()
    16 # select * from User where id<2 or (name="eric" and id>3) or extra != "" 
    17 
    18 # 通配符
    19 ret = db_session.query(User).filter(User.name.like('e%')).all()
    20 ret = db_session.query(User).filter(~User.name.like('e%')).all()
    21 
    22 # 限制
    23 ret = db_session.query(User)[1:2]
    24 
    25 # 排序
    26 ret = db_session.query(User).order_by(User.name.desc()).all()
    27 ret = db_session.query(User).order_by(User.name.desc(), User.id.asc()).all()
    28 
    29 # 分组
    30 from sqlalchemy.sql import func
    31 
    32 ret = db_session.query(User).group_by(User.extra).all()
    33 ret = db_session.query(
    34     func.max(User.id),
    35     func.sum(User.id),
    36     func.min(User.id)).group_by(User.name).all()
    37 
    38 ret = db_session.query(
    39     func.max(User.id),
    40     func.sum(User.id),
    41     func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all()
    View Code

    四、修改操作的其他方式

     1 #高级版更新操作
     2 from create_table import User,engine
     3 from sqlalchemy.orm import sessionmaker
     4 
     5 Session = sessionmaker(engine)
     6 db_session = Session()
     7 
     8 #直接修改
     9 # db_session.query(User).filter(User.id > 0).update({"name" : "099"})
    10 
    11 #在原有值基础上添加 
    12 db_session.query(User).filter(User.id > 0).update({User.name: User.name + "099"}, synchronize_session=False)
    13 db_session.commit()
    14 
    15 #在原有值基础上添加 
    16 # db_session.query(User).filter(User.id > 0).update({"age": User.age + 1}, synchronize_session="evaluate")
    17 # db_session.commit()
    View Code
  • 相关阅读:
    大数据平台布局图
    springMvc入门Demo例子
    用photoShop不改变图片尺寸修改存储大小
    ABBYY FineReader OCR识别图片文字到word电子
    servlet+jsp实例demo
    Runtime Hosts
    Hosting the Common Language Runtime
    Programming with Application Domains
    Application Domains Overview
    Application Domains
  • 原文地址:https://www.cnblogs.com/liaopeng123/p/10316543.html
Copyright © 2020-2023  润新知