ORM框架之SQLAlchemy

　　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 

                        

                            

                                
< 上一篇
oracle 12c 学习
                            
                            

                                 
下一篇 >
MySQL 有关MHA搭建与切换的几个错误log