一、创建表以及数据库的连接
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()
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()
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()
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()
三、其他的查询方式
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()
四、修改操作的其他方式
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()