总目录:https://blog.csdn.net/qq_41106844/article/details/105553392
Python-后端 - 子目录:https://blog.csdn.net/qq_41106844/article/details/105553324
武沛齐的sqlalchemy:https://www.cnblogs.com/wupeiqi/articles/8259356.html
官方文档:https://www.osgeo.cn/sqlalchemy/
SQLAlchemy
ORM(关系映射)框架
类对应数据库的表
对象对应数据库的行
作用:帮助我们使用类和对象快速实现数据库操作
操作数据库的库
-原生:
PyMySql(支持Python3和Python2)
MySqlDB(不支持Python3)
-ORM框架:
SQLAlchemy
使用
创建引擎
engine = create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/python_ech?charsct=utf8",
max_overflow = 0, //超过连接池大小外最多创建的连接
pool_size = 5, //连接池大小
pool_timeout = 30, //线程最多等待时间
pool_recycle = 1 //多久后进行回收(-1不回收)
)
设置映射
Base = declarative_base()
class Users(Base):
__tablename__ = 'users'
id = Column(Integer,primary_key=True)
name = Column(String(32),index=True,nullable=False)
age = Column(Integer,index=True,nullable=False)
banji = Column(Integer,index=True,nullable=False)
启动
Base.metadata.create_all(engine) //新建
Base.metadata.drop(engine) //删除
增删改查
创建连接
SessionFactory = sessionmaker(bind = engine)
session = SessionFactory()
增
obj1 = Users(name="赵谦")
obj2 = Users(name="孙莉")
session.add(obj1)
session.add(obj2) //插入一个对象
session.add_all([
Users(name="赵谦"),
Users(name="孙莉")
]) //插入多个对象
session.commit() //执行
session.close() //关闭连接
查
result = session.query(Users).all() //取出Users表内所有的数据
for row in result:
print(row.id,row.name,row.age) //遍历显示
result1 = session.query(Users).filter(Users.id>3) //取出Users表内id大于3的数据
for row in result:
print(row.id,row.name,row.age) //遍历显示
result1 = session.query(Users).filter(Users.id>3).first() //取出Users表内id大于3中的第一条数据
print(result.id,result.name,result.age)
删
session.query(Users).filter(Users.id < 3).delete() //将id小于3的全部删除
session.commit()
改
session.query(Users).filter(Users.id == 3).updata({Users.name:"周武",Users.age:27}) //将id等于3的name改为周武,age改为27
session.query(Users).filter(Users.id == 3).updata({'name':Users.name+"郑旺"},syschronize_session=False)
session.commit()
-常用操作
1.实例化数据(可以使用索引)
result = session.query(Users.id,Users.name,Users.age).all()
for item in result:
print(item[0],item.id,item.name)
2.label别名
result = session.query(Users.id,Users.name.label('xm'),Users.age).all()
3.查看SQL语句
result = session.query(Users.id,Users.name,Users.age)
print(result)
4.查询默认条件是and
session.query(Users).filter(Users.id > 1,Users.name == '王五').all()
5.between用于限制范围
session.query(Users).filter(Users.id.between(1,3),Users.name == '王五').all()
6.in_用于限制范围 in可以是另一条语句 ~Users.id.in_([1,3,4])则为not
session.query(Users).filter(Users.id.in_([1,3,4]),Users.name == '王五').all()
7.使用in_实现子查询
session.query(Users).filter(Users.id.in_(session.query(Users.id).filter(Users.name != "李四")),Users.name == '王五').all()
8.or_操作符
session.filter(or_(Users.id>3,Users.name == "张三")).all()
9. filter_by传递的是参数,其他与filter相同
session.query(Users).filter_by(name = "张三").all()
10.通配符的使用 %:若干各字符 _:一个字符
session.query(Users).filter(User.name.like('张%')).all()
11.切片
session.query(Users)[1:3]
12. 降序排序 asc升序 User.age.desc(),User.id.desc()先按照age排序,若age相同,按照id排序
session.query(Users).order_by(User.age.desc()).all()
13. 分组 可以使用func聚合条件
session.query(Users).group_by(User.banji).all()
14.使用func聚合条件
session.query(func.max(Users.id)).group_by(User.banji).all()
15.组合
q1.union(q2).all() q1.union_all(q2).all()
上面是我第一次学习时的笔记。
下面开始说这次复习:
安装
语法
python -m pip install sqlalchemy
连接数据库
from sqlalchemy import create_engine
#数据库类型+数据库引擎://用户名:密码@IP地址:端口号/数据库名?charset=使用的编码
engine=create_engine('mysql+pymysql://root: @127.0.0.1:3306/sql_py?charset=utf8')
print(engine)
Engine(mysql+pymysql://root:***@127.0.0.1:3306/sql_py?charset=utf8)
![20155953-805de9a53c6fc154.png](https://i-blog.csdnimg.cn/blog_migrate/fc1abecc72fa0fc9a80e1281610f155b.png)
建表
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer
#数据库类型+数据库引擎://用户名:密码@IP地址:端口号/数据库名?charset=使用的编码
engine=create_engine('mysql+pymysql://root: @127.0.0.1:3306/sql_py?charset=utf8')
#生成orm基类
Base = declarative_base()
#继承生成的orm基类
class Student(Base):
__tablename__ ="student" #表名
id = Column(Integer,primary_key=True) #int类型,设置主键
name = Column(String(32)) #字符串类型,32位长度
banji = Column(String(32))
#创建表
Base.metadata.create_all(engine)
![20155953-8d983ca267b4861c.png](https://i-blog.csdnimg.cn/blog_migrate/e68c4632fe8b4deb0dcb7f97be89d5dd.png)
![20155953-60024bc598602854.png](https://i-blog.csdnimg.cn/blog_migrate/340512c487ab8d15540e62b33dc993ff.png)
插入数据
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer
from sqlalchemy.orm import sessionmaker
#数据库类型+数据库引擎://用户名:密码@IP地址:端口号/数据库名?charset=使用的编码
engine=create_engine('mysql+pymysql://root: @127.0.0.1:3306/sql_py?charset=utf8')
#生成orm基类
Base = declarative_base()
class Student(Base):#继承生成的orm基类
__tablename__ ="student" #表名
id = Column(Integer,primary_key=True)#int类型,设置主键
name = Column(String(32))#字符串类型,32位长度
banji = Column(String(32))
#创建与数据库的会话,Session_Class为一个类
Session_Class = sessionmaker(bind=engine)
#实例化与数据库的会话
Session = Session_Class()
#插入一行数据
t1=Student(name="小明",banji="三班")
Session.add(t1)
#插入多行数据
Session.add_all([
Student(name="小红",banji="二班"),
Student(name="小刚",banji="一班")
])
#必须要提交
Session.commit()
#关闭连接
Session.close()
![20155953-43b9d20e875c08ae.png](https://i-blog.csdnimg.cn/blog_migrate/d0296569ff09b772ffac859b123f89c3.png)
![20155953-0c3a9c1363bad75d.png](https://i-blog.csdnimg.cn/blog_migrate/c8379969f5422a93c87388656702c892.png)
为了更好的进行下面的操作,在数据库中插入了二十条数据。
查询数据
普通查询
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer
from sqlalchemy.orm import sessionmaker
#数据库类型+数据库引擎://用户名:密码@IP地址:端口号/数据库名?charset=使用的编码
engine=create_engine('mysql+pymysql://root: @127.0.0.1:3306/sql_py?charset=utf8')
#生成orm基类
Base = declarative_base()
class Student(Base):#继承生成的orm基类
__tablename__ ="student" #表名
id = Column(Integer,primary_key=True)#int类型,设置主键
name = Column(String(32))#字符串类型,32位长度
banji = Column(String(32))
#创建与数据库的会话,Session_Class为一个类
Session_Class = sessionmaker(bind=engine)
#实例化与数据库的会话
Session = Session_Class()
#取出Student表内所有的数据
result = Session.query(Student).all()
for rowin result:
print(row.id, row.name, row.banji)
#关闭连接
Session.close()
![20155953-0dadf01a3f8ac496.png](https://i-blog.csdnimg.cn/blog_migrate/9e94c09fa4b7404e3db260bd71841d27.png)
![20155953-19d63bb06f31f86e.png](https://i-blog.csdnimg.cn/blog_migrate/8010e920aa4b64dfd5bbdfa6bbe8a79c.png)
条件查询(filter)
#取出Student表中id大于15的记录
result = Session.query(Student).filter(Student.id >=15)
for rowin result:
print(row.id, row.name, row.banji)
![20155953-b0b04c885afb9b18.png](https://i-blog.csdnimg.cn/blog_migrate/a1242cff9da131ad582db6e02c2edd2f.png)
![20155953-e80285b80146479a.png](https://i-blog.csdnimg.cn/blog_migrate/ab8ff8ed1023b88c4d1d32df89f2dfd4.png)
first()
如果只想要第一条数据就可以用first()。
#取出Student表中id大于15的记录中的第一条
result = Session.query(Student).filter(Student.id >=15).first()
print(result.id,result.name,result.banji)
![20155953-a01962d5fde8542e.png](https://i-blog.csdnimg.cn/blog_migrate/61445b9c042686c2418963afa0c3e665.png)
![20155953-6fae41be0c4c3469.png](https://i-blog.csdnimg.cn/blog_migrate/df8ddaf8f769ba12487347ff7500897d.png)
排序查询
#取出Student表中数据,按照banji降序排序 默认升序排序
result = Session.query(Student).order_by(Student.banji.desc()).all()
for rowin result:
print(row.id, row.name, row.banji)
![20155953-8a3bc769700a3473.png](https://i-blog.csdnimg.cn/blog_migrate/f64d9fe0dc71f86bc71cc0ca6b2a5558.png)
![20155953-63bd630b1f290141.png](https://i-blog.csdnimg.cn/blog_migrate/936e366da53cead34a7d8b3393dea2b9.png)
双排序
#取出Student表中数据,外部按照banji降序排序,内部按照id升序排序
result = Session.query(Student).order_by(Student.banji.desc(),Student.id).all()
for rowin result:
print(row.id, row.name, row.banji)
![20155953-d2af2552bac34a55.png](https://i-blog.csdnimg.cn/blog_migrate/24cef2f4b14b1db77f91580e0b698e44.png)
![20155953-91e4c35256ab0b26.png](https://i-blog.csdnimg.cn/blog_migrate/81ee2b995d1dab2d67f710bc6b5b9c51.png)
分组查询
#取出Student表中数据,按照banji分组。
result = Session.query(Student).group_by(Student.banji).all()
for rowin result:
print(row.id, row.name, row.banji)
![20155953-fa867398e0c87249.png](https://i-blog.csdnimg.cn/blog_migrate/042da7e9c37f6d0a1fd05ac620d7f358.png)
![20155953-4aebf5003823d736.png](https://i-blog.csdnimg.cn/blog_migrate/5a30e519f260143a0a9152de70644f56.png)
分组查询一般都是结合聚合函数。
注 需要导入func函数
from sqlalchemy.sql import func
#取出Student表中数据,按照banji分组,查询每班有多少人。
result = Session.query(func.count(Student.id)).group_by(Student.banji).all()
for rowin result:
print(row)
![20155953-00fd873c5b4fa9fd.png](https://i-blog.csdnimg.cn/blog_migrate/b2726b02dcf98cc2b9d4e8ec68ab01fd.png)
运行结果是元组类型
![20155953-1503b2716927918f.png](https://i-blog.csdnimg.cn/blog_migrate/f8514c3a5b1c1da460f79487317559c5.png)
分组查询当然有分组条件了
#取出Student表中数据,按照banji分组,查询班级人数是偶数的班级人数。
result = Session.query(func.count(Student.id)).group_by(Student.banji).having(func.count(Student.id)%2 ==0).all()
for rowin result:
print(row)
![20155953-0262eef790b33646.png](https://i-blog.csdnimg.cn/blog_migrate/b4465c24113184422f67bfce749ecca0.png)
![20155953-143dd8b2ce2d0e4b.png](https://i-blog.csdnimg.cn/blog_migrate/049fb27a1018ffc6dfdd43b579313635.png)
删除数据
删除数据建立在查询数据之上,查询出来数据,删除。
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer
from sqlalchemy.orm import sessionmaker
#数据库类型+数据库引擎://用户名:密码@IP地址:端口号/数据库名?charset=使用的编码
engine=create_engine('mysql+pymysql://root: @127.0.0.1:3306/sql_py?charset=utf8')
#生成orm基类
Base = declarative_base()
class Student(Base):#继承生成的orm基类
__tablename__ ="student" #表名
id = Column(Integer,primary_key=True)#int类型,设置主键
name = Column(String(32))#字符串类型,32位长度
banji = Column(String(32))
#创建与数据库的会话,Session_Class为一个类
Session_Class = sessionmaker(bind=engine)
#实例化与数据库的会话
Session = Session_Class()
#删除id大于15的信息
result = Session.query(Student).filter(Student.id >=15).delete()
#提交修改
Session.commit()
#关闭连接
Session.close()
![20155953-fc8ac8121af7c34d.png](https://i-blog.csdnimg.cn/blog_migrate/bf24e4651235a9014ace5dc0120b6d96.png)
![20155953-1a6cc9a35d69dee9.png](https://i-blog.csdnimg.cn/blog_migrate/3f78bcfdb98d4f75c31383464d65ddc2.png)
修改数据
#一些常见的修改操作
Session.query(Student).filter(Student.id ==1).update({Student.banji:'五班'})
Session.query(Student).filter(Student.id ==2).update({'name':'王红'})
Session.query(Student).filter(Student.id ==3).update({'name':Student.name+"(尖子生)"},synchronize_session=False)
synchronize_session用于query在进行delete or update操作时,对session的同步策略。
也就是修改是否要在当前session中生效。
![20155953-25f014c9d69ba16e.png](https://i-blog.csdnimg.cn/blog_migrate/17905644056933d7219af431d94f9b24.png)
![20155953-e1e7882aee636939.png](https://i-blog.csdnimg.cn/blog_migrate/2698f5c2e8779f24fdb38fa6ef615522.png)
事务
![20155953-232cd3d1520ae005.png](https://i-blog.csdnimg.cn/blog_migrate/4e30f9b3e2e83952d68d9f7b28bf4525.png)
实例:郑旺要和冯晨换班。
#换班
try:
Session.query(Student).filter(Student.id ==7).update({Student.banji:'二班'})
raise Exception #在这里抛出错误 让操作中断
Session.query(Student).filter(Student.id ==8).update({Student.banji:'三班'})
Session.commit()
except Exception as e:
#收到错误,回滚,结束事务。
Session.rollback()
Session.commit()
![20155953-02c074ce0ed1cd8b.png](https://i-blog.csdnimg.cn/blog_migrate/48fe26a8d40befdb98806eaaa3c252e1.png)
![20155953-76757d10c76357a4.png](https://i-blog.csdnimg.cn/blog_migrate/54f9a92ae4f7012a98a3ce0997d7c9a4.png)
其他常用操作
别名
#label 设置别名
result = Session.query(Student.id.label("学号")).filter(Student.id <=5)
for i in result:
print(i.学号)
![20155953-ea1149478e281643.png](https://i-blog.csdnimg.cn/blog_migrate/c76f89d65b66af01cc35e26d54b2e65d.png)
![20155953-20aba855c0c7474a.png](https://i-blog.csdnimg.cn/blog_migrate/acc243f2b79461ad9e94db886598ee41.png)
查看SQL语句
#查看sql语句
result = Session.query(Student.id.label("学号")).filter(Student.id <=5)
print(result)
![20155953-4edbb98418a55258.png](https://i-blog.csdnimg.cn/blog_migrate/1555c1994583d6a77feb630a6d18dc4c.png)
![20155953-a917c738a9e782b1.png](https://i-blog.csdnimg.cn/blog_migrate/44546427c4dc4fc4031c2bf9287b3668.png)
操作符
操作符有 in_ 和 ~in_ 两种
#使用in_ 操作符 找到符合的id
result = Session.query(Student).filter(Student.id.in_([1,3,4,5])).all()
for i in result:
print(i.id,i.name,i.banji)
![20155953-7d27329dbd220a8a.png](https://i-blog.csdnimg.cn/blog_migrate/40ea0a7a463978bda10a781cc6bfac16.png)
![20155953-64a7e2545f17d9d3.png](https://i-blog.csdnimg.cn/blog_migrate/3ad517ba652576ce4674edb6bfae6f04.png)
#~in_ 会输出和 in_ 相反的结果
result = Session.query(Student).filter(~Student.id.in_([1,3,4,5])).all()
for i in result:
print(i.id,i.name,i.banji)
![20155953-1b8573fd51689f28.png](https://i-blog.csdnimg.cn/blog_migrate/c8897516423f3fdefcb52c835dce5b4e.png)
![20155953-192949d3032bc809.png](https://i-blog.csdnimg.cn/blog_migrate/c081105432c120793eefaa4b91acb43c.png)
逻辑运算符(and_ in or_)
不适用的逻辑运算符:
print("----------普通查询----------")
#普通查询
result = Session.query(Student).filter(Student.banji =='一班',Student.id >=8).all()
for i in result:
print(i.id,i.name,i.banji)
![20155953-2f8fb673da280fd3.png](https://i-blog.csdnimg.cn/blog_migrate/1877876e8d23dd6cb4fb6bf525ae64f3.png)
![20155953-b0705fe4f0e6436c.png](https://i-blog.csdnimg.cn/blog_migrate/0cd729088588a2c48c148d1ed80e6dd9.png)
使用逻辑运算符:
from sqlalchemy import and_, or_
print("----------and_----------")
#使用and_
result = Session.query(Student).filter(and_(Student.banji =='一班',Student.id >=8)).all()
for i in result:
print(i.id,i.name,i.banji)
print("----------or_----------")
#使用or_
result = Session.query(Student).filter(or_(Student.banji =='一班',Student.id >=8)).all()
for i in result:
print(i.id,i.name,i.banji)
print("----------or_(and_)----------")
#and_ 和 or_ 可以嵌套使用 也可以并行使用
result = Session.query(Student).filter(
or_(
Student.banji =='一班',
and_(Student.id >=8,Student.id <=12)
)
).all()
for i in result:
print(i.id,i.name,i.banji)
![20155953-e2580bd9cdae037c.png](https://i-blog.csdnimg.cn/blog_migrate/77865a9ba95fad299d5c297f533112ed.png)
![20155953-435b44939a89de9c.png](https://i-blog.csdnimg.cn/blog_migrate/d3fca283e368acb075b37a03b11ebc62.png)
通配符
#使用通配符
result = Session.query(Student).filter(Student.name.like('小%')).all()
for i in result:
print(i.id,i.name,i.banji)
print("----------使用反向通配符---------")
#同样也有反向的
result = Session.query(Student).filter(~Student.name.like('小%')).all()
for i in result:
print(i.id,i.name,i.banji)
![20155953-296761c275aae5e9.png](https://i-blog.csdnimg.cn/blog_migrate/24b4688fe7b08e6e88f6799eb621eb11.png)
![20155953-5f9e9ca40bd737ff.png](https://i-blog.csdnimg.cn/blog_migrate/831b88c32e26e831bbcfe8908c1a677c.png)
联合查询
Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
union_all:对两个结果集进行并集操作,包括重复行,不进行排序;
![20155953-61738d4415b43607.png](https://i-blog.csdnimg.cn/blog_migrate/b2747f69c1f17fa3a9125afb7c7b8067.png)
我们建立另一个表
s1 = Session.query(Student.name).filter(Student.id >=1)
s2 = Session.query(Student1.name).filter(Student1.id >=1)
result=s1.union(s2).all()
for iin result:
print(i.name)
print("---")
s1 = Session.query(Student.name).filter(Student.id >=1)
s2 = Session.query(Student1.name).filter(Student1.id >=1)
result=s1.union_all(s2).all()
for iin result:
print(i.name)
![20155953-69cc7a8df2a3f4e5.png](https://i-blog.csdnimg.cn/blog_migrate/91ea9c4936f1aaffee2ea7231b23b77e.png)
![20155953-1a895e9f086e0027.png](https://i-blog.csdnimg.cn/blog_migrate/9ed558203c1b538b16b9cfe956018914.png)
![20155953-c1f12275ed011e51.png](https://i-blog.csdnimg.cn/blog_migrate/3686c5dd147c1311ec2c6ea552df00f9.png)