ORM框架之SQLAlchemy
SQLAlchemy是Python编程语言下的一款开源软件。提供了SQL工具包及对象关系映射（ORM：Object Relational Mappers）工具。SQLAlchemy“采用简单的Python语言，为高效和高性能的数据库访问设计，实现了完整的企业级持久模型”。SQLAlchemy的理念是，SQL数据库的量级和性能重要于对象集合；而对象集合的抽象又重要于表和行。因此，SQLAlchemy采用了类似于Java里Hibernate的数据映射模型，而不是其他ORM框架采用的Active Record模型。
1.单数据表
1.1单数据表创建
1 #pip install SQLAlchemy模块 2 3 #(1)创建对象基类------类似django中的Model 4 from sqlalchemy.ext.declarative import declarative_base 5 Base=declarative_base() 6 7 #(2)定义类对象---表 8 from sqlalchemy import Column,Integer,String 9 class Student(Base):#继承基类 10 __tablename__='student'#表名 11 id=Column(Integer,primary_key=True,autoincrement=True)#类属性---表字段 12 name=Column(String(32),nullable=False,index=True,unique=True) 13 14 15 #(3)创建数据库引擎（依赖pymysql模块驱动） 16 from sqlalchemy.engine import create_engine 17 engine=create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy?charset=utf8")#源码有配置说明：'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名' 18 19 20 # (4)创建表 21 Base.metadata.create_all(engine)#创建基于Base基类的engine数据库引擎中的所有表 22 # Base.metadata.drop_all(engine)#删除基于Base基类的engine数据库引擎中的所有表
1.2单数据表的增删改查
1.2.1增
1 from sqlalchemy.orm import sessionmaker 2 from create_table import Student,engine 3 4 #创建与数据库的连接 5 session=sessionmaker(engine)#初始化与数据库引擎的连接类型 6 db_session=session()#创建数据库连接 7 8 9 10 #增 11 #单个增加add 12 s=Student(name='yang')#实例化对象 13 db_session.add(s)#创建插入语句（只在当前的缓存中执行,但是id会被占用） 14 db_session.commit()#提交数据库执行同步更新指令 15 db_session.close()#使用完之后最好关闭连接 16 17 18 #批量增加add_all 19 s_list=[Student(name='zhang'),Student(name='li')] 20 db_session.add_all(s_list) 21 db_session.commit() 22 db_session.close()#使用完之后最好关闭连接
1.2.2查
1 from sqlalchemy.orm import sessionmaker 2 from create_table import Student,engine 3 4 #创建与数据库的连接 5 session=sessionmaker(engine)#初始化与数据库引擎的连接类型 6 db_session=session()#创建数据库连接 7 8 #查query 9 # （在执行其他操作之后务必关闭数据库连接，否则查询的是上一次连接建立时导入缓存的数据，导致数据不准确） 10 print(db_session.query(Student))#直接打印出原生SQL：SELECT student.id AS student_id, student.name AS student_name FROM student 11 12 #普通查询 13 res_all=db_session.query(Student).all()#查询所有 14 print(res_all) 15 for i in res_all: 16 print(i.id,i.name) 17 18 19 res_first=db_session.query(Student).first()#查询所有结果的第一个 20 print(res_first) 21 print(res_first.id,res_first.name) 22 23 res_one=db_session.query(Student)[1]#查询结果中选取某一个 24 # res_one=db_session.query(Student).all()[1] 25 print(res_one) 26 print(res_one.id,res_one.name) 27 28 29 res_many=db_session.query(Student)[:2]#查询结果中的连续的某几个 30 # res_many=db_session.query(Student).all()[:2] 31 print(res_many) 32 for i in res_many: 33 print(i.id,i.name) 34 35 # 带条件的查询 36 res=db_session.query(Student).filter(Student.id>1) 37 print(res)#直接输出了原生SQL语句 38 #SELECT student.id AS student_id, student.name AS student_name 39 # FROM student 40 # WHERE student.id > %(id_1)s 41 42 #filter中的算数运算符过滤 43 res=db_session.query(Student).filter(Student.id>1).all() 44 print(res,[(i.id,i.name) for i in res],sep='\n') 45 res=db_session.query(Student).filter(Student.id==5).all() 46 print(res,[(i.id,i.name) for i in res],sep='\n') 47 res=db_session.query(Student).filter(Student.id!=1).all() 48 print(res,[(i.id,i.name) for i in res],sep='\n') 49 50 51 #filter中的并列条件过滤(条件之间用逗号隔开) 52 res=db_session.query(Student).filter(Student.id>1,Student.name!='yang').all() 53 print(res,[(i.id,i.name) for i in res],sep='\n')
1.2.3改
1 from sqlalchemy.orm import sessionmaker 2 from create_table import Student,engine 3 4 #创建与数据库的连接 5 session=sessionmaker(engine)#初始化与数据库引擎的连接类型 6 db_session=session()#创建数据库连接 7 8 # 改(基于查询) 9 res=db_session.query(Student).filter(Student.name=='YANG').update({"name":'yang'})#单条修改，返回修改条数 10 db_session.commit()#务必要提交到数据库 11 print(res) 12 db_session.close() 13 14 15 res=db_session.query(Student).filter(Student.id>5).update({"name":'yang'})#更新多条，注意更新的该字段不能设置唯一，否则报错 16 db_session.commit() 17 print(res)#更新记录条数 18 db_session.close()
1.2.4删
1 from sqlalchemy.orm import sessionmaker 2 from create_table import Student,engine 3 4 #创建与数据库的连接 5 session=sessionmaker(engine)#初始化与数据库引擎的连接类型 6 db_session=session()#创建数据库连接 7 8 # 删（基于查询） 9 res=db_session.query(Student).filter(Student.id>8).delete()#返回删除的记录条数 10 db_session.commit() 11 print(res) 12 db_session.close()
1.2.5 高级版查询操作
1 # 高级版查询操作,厉害了哦 2 #老规矩 3 from create_table import Student,engine 4 from sqlalchemy.orm import sessionmaker 5 6 Session = sessionmaker(engine) 7 db_session = Session() 8 9 10 # db_session.add_all([User(name='yang'),User(name='zhang'),User(name='wang')]) 11 # db_session.commit() 12 13 # 查询数据表操作 14 # and or 15 from sqlalchemy.sql import and_ , or_,text 16 ret = db_session.query(Student).filter(and_(Student.id > 3, Student.name == 'yang')).all() 17 print(ret) 18 ret = db_session.query(Student).filter(or_(Student.id < 2, Student.name == 'yang')).all() 19 print(ret) 20 21 # # 查询所有数据 22 r1 = db_session.query(Student).all() 23 24 # # 查询数据 指定查询数据列 加入别名 25 r2 = db_session.query(Student.name.label('username'), Student.id).first() 26 print(r2.id,r2.username) # 3 wang 27 28 # 表达式筛选条件 29 r3 = db_session.query(Student).filter(Student.name == "yang").all() 30 31 # # 原生SQL筛选条件 32 r4 = db_session.query(Student).filter_by(name='yang').all() 33 r5 = db_session.query(Student).filter_by(name='yang').first() 34 35 # 字符串匹配方式筛选条件 并使用 order_by进行排序 36 r6 = db_session.query(Student).filter(text("id<:value and name=:name")).params(value=224, name='yang').order_by(Student.id).all() 37 38 # #原生SQL查询 39 r7 = db_session.query(Student).from_statement(text("SELECT * FROM User where name=:name")).params(name='yang').all() 40 41 # # 筛选查询列 42 # # query的时候我们不在使用User ORM对象,而是使用User.name来对内容进行选取 43 user_list = db_session.query(Student.name).all() 44 print(user_list)#[('wang',), ('yang',), ('zhang',)] 45 for row in user_list: 46 print(row.name) 47 # 48 # # 别名映射 name as nick 49 user_list = db_session.query(Student.name.label("nick")).all() 50 print(user_list) 51 for row in user_list: 52 print(row.nick) # 这里要写别名了 53 # 54 # # 筛选条件格式 55 user_list = db_session.query(Student).filter(Student.name == "yang").all() 56 user_list = db_session.query(Student).filter(Student.name == "yang").first() 57 user_list = db_session.query(Student).filter_by(name="yang").first() 58 for row in user_list: 59 print(row.nick) 60 61 # 复杂查询 62 from sqlalchemy.sql import text 63 user_list = db_session.query(Student).filter(text("id<:value and name=:name")).params(value=3,name="DragonFire") 64 65 # 查询语句 66 from sqlalchemy.sql import text 67 user_list = db_session.query(Student).filter(text("select * from User id<:value and name=:name")).params(value=3,name="DragonFire") 68 69 # 排序 : 70 user_list = db_session.query(Student).order_by(Student.id).all() 71 user_list = db_session.query(Student).order_by(Student.id.desc()).all() 72 for row in user_list: 73 print(row.name,row.id) 74 75 #其他查询条件 76 """ 77 ret = session.query(Student).filter_by(name='yang').all() 78 ret = session.query(Student).filter(Student.id > 1, Student.name == 'yang').all() 79 ret = session.query(Student).filter(Student.id.between(1, 3), Student.name == 'yang').all() # between 大于1小于3的 80 ret = session.query(Student).filter(Student.id.in_([1,3,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的 81 ret = session.query(Student).filter(~Student.id.in_([1,3,4])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的 82 ret = session.query(Student).filter(Student.id.in_(session.query(Student.id).filter_by(name='yang'))).all() 子查询 83 from sqlalchemy import and_, or_ 84 ret = session.query(Student).filter(and_(Student.id > 3, Student.name == 'yang')).all() 85 ret = session.query(Student).filter(or_(Student.id < 2, Student.name == 'yang')).all() 86 ret = session.query(Student).filter( 87 or_( 88 Student.id < 2, 89 and_(User.name == 'eric', Student.id > 3), 90 Student.extra != "" 91 )).all() 92 # select * from Student where id<2 or (name="eric" and id>3) or extra != "" 93 94 # 通配符 95 ret = db_session.query(Student).filter(Student.name.like('e%')).all() 96 ret = db_session.query(Student).filter(~Student.name.like('e%')).all() 97 98 # 限制 99 ret = db_session.query(Student)[1:2] 100 101 # 排序 102 ret = db_session.query(Student).order_by(Student.name.desc()).all() 103 ret = db_session.query(Student).order_by(Student.name.desc(), Student.id.asc()).all() 104 105 # 分组 106 from sqlalchemy.sql import func 107 108 ret = db_session.query(Student).group_by(Student.extra).all() 109 ret = db_session.query( 110 func.max(Student.id), 111 func.sum(Student.id), 112 func.min(Student.id)).group_by(Student.name).all() 113 114 ret = db_session.query( 115 func.max(Student.id), 116 func.sum(Student.id), 117 func.min(Student.id)).group_by(Student.name).having(func.min(Student.id) >2).all() 118 """ 119 120 # 关闭连接 121 db_session.close()
1.2.6高级版修改操作
1 #高级版更新操作 2 from create_table import Student,engine 3 from sqlalchemy.orm import sessionmaker 4 5 Session = sessionmaker(engine) 6 db_session = Session() 7 8 #直接修改 9 db_session.query(Student).filter(Student.id > 0).update({"name" : "099"}) 10 11 #在原有值基础上添加 - 1 12 db_session.query(Student).filter(Student.id > 0).update({Student.name: Student.name + "099"}, synchronize_session=False) 13 14 #在原有值基础上添加 - 2 15 db_session.query(Student).filter(Student.id > 0).update({"age": Student.age + 1}, synchronize_session="evaluate") 16 db_session.commit() 17 db_session.close()
2.一对多ForeignKey数据表(基于relationship)
2.1ForeignKey一对多数据表及relationshipi关系创建
1 from sqlalchemy.ext.declarative import declarative_base 2 #(1)创建基类 3 Base=declarative_base() 4 5 from sqlalchemy import Column,Integer,String,ForeignKey 6 from sqlalchemy.orm import relationship 7 #(2)创建对象，ForeignKey及relationship关系 8 class ClassTable(Base): 9 __tablename__='classtable' 10 id=Column(Integer,primary_key=True) 11 name=Column(String(32),unique=False) 12 13 class Student(Base): 14 __tablename__='student' 15 id=Column(Integer,primary_key=True) 16 name=Column(String(32),nullable=False) 17 #创建foreignKey和relationship关系 18 class_id=Column(Integer,ForeignKey('classtable.id')) # 关联字段,让class_id 与 classtable 的 id 进行关联,主外键关系(这里的ForeignKey一定要是表名.id不是对象名） 19 stc=relationship("ClassTable",backref='cts')# 将student 与 ClassTable 创建关系 这个不是字段,只是关系,backref是反向关联的关键字 20 21 22 #(3)创建数据库引擎 23 from sqlalchemy import create_engine 24 engine=create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy?charset=utf8") 25 26 #(4)基于数据库引擎创建集成基类的表 27 Base.metadata.create_all(engine) 28 # Base.metadata.drop_all(engine)
2.2一对多基于relationship的增查和改删操作
2.2.1一对多基于relationship的增
1 from sqlalchemy.orm import sessionmaker 2 from create_ForeignKey_table import Student,ClassTable,engine 3 #（1）建立数据库链接会话 4 session=sessionmaker(engine) 5 db_session=session() 6 7 #一对多的增 8 9 #普通的增 10 11 #先增加班级 12 db_session.add(ClassTable(name='三年一班')) 13 db_session.add_all([ClassTable(name='三年二班'),ClassTable(name='三年三班')]) 14 db_session.commit() 15 #查询要添加到的班级 16 cla=db_session.query(ClassTable).filter(ClassTable.name=='三年一班').first() 17 #增加对应班级的学生 18 db_session.add(Student(name='yang',class_id=cla.id)) 19 db_session.add_all([Student(name='zhang',class_id=cla.id),Student(name='li',class_id=cla.id)]) 20 db_session.commit() 21 db_session.close() 22 23 #基于relationshi增加 24 25 #正向增加（foreiKey决定） 26 db_session.add(Student(name='he',stc=ClassTable(name='二年一班'))) 27 db_session.commit() 28 29 30 #反向增加 31 class_obj=ClassTable(name='二年二班')#实例化班级或者查询出一个班级 32 # class_obj=db_session.query(ClassTable).filter(ClassTable.name=='二年二班').first() 33 34 # 向 Student 数据表中添加 1条或多条数据 并将 1条或多条数据的class_id 写成 class_obj的id 35 class_obj.cts=[Student(name='wu'),Student(name='feng')]#添加一个也必须是列表 36 37 db_session.add(class_obj) 38 db_session.commit() 39 db_session.close() 40 41 一对多基于relationship的增
2.2.2一对多基于relationship的查
1 from sqlalchemy.orm import sessionmaker 2 from create_ForeignKey_table import Student,ClassTable,engine 3 #（1）建立数据库链接会话 4 session=sessionmaker(engine) 5 db_session=session() 6 7 #查(基于relationship) 8 #正向查询 9 stu_list=db_session.query(Student).all() 10 print([(stu.id,stu.name,stu.stc.name,stu.stc.id) for stu in stu_list]) 11 12 #反向查询 13 cla_list=db_session.query(ClassTable).all() 14 # print([(cla.id,cla.name,[stu.name for stu in cla.cts])for cla in cla_list]) 15 for cla in cla_list: 16 for stu in cla.cts:#通过relationship设置的backref反向关联到学生对象 17 print(cla.id,cla.name,stu.name) 18 19 一对多基于relationship的查
2.2.3一对多的改
1 from sqlalchemy.orm import sessionmaker 2 from create_ForeignKey_table import Student,ClassTable,engine 3 #（1）建立数据库链接会话 4 session=sessionmaker(engine) 5 db_session=session() 6 7 #通过班级修改学生信息 8 class_obj=db_session.query(ClassTable).filter(ClassTable.name=='二年二班').first() 9 db_session.query(Student).filter(Student.class_id==class_obj.id).update({'name':'22'}) 10 db_session.commit() 11 db_session.close() 12 13 14 #通过学生修改班级信息 15 stu_obj=db_session.query(Student).filter(Student.name=='22').first() 16 db_session.query(ClassTable).filter(ClassTable.id==stu_obj.class_id).update({'name':'二年四班'}) 17 db_session.commit() 18 db_session.close() 19 20 一对多的改
2.2.4一对多的删
1 from sqlalchemy.orm import sessionmaker 2 from create_ForeignKey_table import Student,ClassTable,engine 3 #（1）建立数据库链接会话 4 session=sessionmaker(engine) 5 db_session=session() 6 7 #删 8 class_obj=db_session.query(ClassTable).filter(ClassTable.name=='二年四班').first() 9 res=db_session.query(Student).filter(Student.class_id==class_obj.id).delete() 10 db_session.commit() 11 print(res) 12 db_session.close() 13 14 一对多的删
3.多对多基于第三张的数据表
3.1多对多数据表relationship及第三张表ForeignKey创建
1 from sqlalchemy.ext.declarative import declarative_base 2 #（1）创建基类 3 Base=declarative_base() 4 5 6 from sqlalchemy import Column,Integer,String,ForeignKey 7 from sqlalchemy.orm import relationship 8 #（2）创建对象和relationship关系 9 class Girl(Base): 10 __tablename__='girl' 11 id=Column(Integer,primary_key=True) 12 name=Column(String(32),nullable=False) 13 gtb=relationship('Boy',secondary='friendship', backref='btg')#创建多对多的relationship关系，注意第三张表的桥梁关系 14 15 16 class Boy(Base): 17 __tablename__='boy' 18 id = Column(Integer, primary_key=True) 19 name = Column(String(32), nullable=False) 20 21 #多对多关系必须创建第三张关联表 22 class Friendship(Base): 23 __tablename__='friendship' 24 id = Column(Integer, primary_key=True) 25 girl_id=Column(Integer,ForeignKey('girl.id'))#创建于girl表的外键关联 26 boy_id = Column(Integer, ForeignKey('boy.id'))#创建于boy表的外键关联 27 28 #（3）创建数据库引擎 29 from sqlalchemy import create_engine 30 engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy?charset=utf8') 31 32 #（4）基于数据库引擎创建继承Base基类的表 33 Base.metadata.create_all(engine)
3.2多对多基于relationship的增查
3.2.1多对多基于relationship的增
1 from sqlalchemy.orm import sessionmaker 2 from create_MTM_table import engine,Girl,Boy 3 #(1)创建数据库连接 4 session=sessionmaker(engine) 5 db_session=session() 6 7 8 #多对多表的增 9 #MTM基于relationship的正向增 10 db_session.add(Girl(name='g1',gtb=[Boy(name='b1'),Boy(name='b11')])) 11 db_session.commit() 12 13 #MTM基于relationship的反向增 14 boy_obj=Boy(name='b2') 15 boy_obj.btg=[Girl(name='g2'),Girl(name='g22')] 16 db_session.add(boy_obj) 17 db_session.commit() 18 19 db_session.close()
3.2.1多对多基于relationship的查
1 from sqlalchemy.orm import sessionmaker 2 from create_MTM_table import engine,Girl,Boy 3 #(1)创建数据库连接 4 session=sessionmaker(engine) 5 db_session=session() 6 7 #多对多表的查 8 #通过girl查所有的boy 9 girl_list=db_session.query(Girl).all() 10 for girl in girl_list: 11 for boy in girl.gtb: 12 print(girl.name,"----------",boy.name) 13 #通过boy查询所有的girl 14 boy_list=db_session.query(Boy).all() 15 for boy in boy_list: 16 for girl in boy.btg: 17 print(boy.name, "----------", girl.name)
