1 sqlalchemy介绍
# 介绍
sqlalchemy是一个基于Python实现的ORM框架，跟web框架无关，独立的
ORM框架 是指将数据库的数据 与 python之间的对象的 转换
python操作数据库： # 基本就这两个选择
原生--->pymysql
orm--->sqlalchemy
# 安装
pip install sqlalchemy
# 常见的ORM框架
django的orm
sqlalchemy（大而重）
peewee（小而轻） # 了解
异步orm框架：GINO # 了解
# 常见的微服务框架
Python： nameko # python没有一个特别好的微服务框架
java ：dubbo（阿里开源）、springcloud
go ：grpc、go-zero、go-micro
2 快速使用 (原生sql)
# 写原生sql 不常用 不如直接用pyMySQL
import time
import threading
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine
engine = create_engine(
"mysql+pymysql://root:111@127.0.0.1:3306/cnblogs?charset=utf8", # 默认就是utf8编码
# 格式："数据库类型 + 数据库驱动(底层操作数据库的模块) ：//用户:密码 @数据库的地址:端口/库名字?参数名=值 "
max_overflow=0, # 超过连接池大小外最多创建的连接 最大为8
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置） # -1 不重置
)
def task(arg):
conn = engine.raw_connection() # 建立链接
cursor = conn.cursor()
cursor.execute(
"select * from article"
)
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()
for i in range(20):
t = threading.Thread(target=task, args=(i,))
t.start()
3 创建models表
3.0 指定地址和库
# sqlalchemy 指定地址和库
使用engin(引擎) 连哪个库 哪个地址
Base.metadata.create_all(engine) # 创建Base的所有表
Base.metadata.drop_all(engine) # 删除Base的所有表
# sqlalchemy只能创建和删除表 不能新增和删除修改字段，不能创建数据库
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
def init_db():
engine = create_engine(
"mysql+pymysql://root:111@127.0.0.1:3306/aaa?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
Base.metadata.create_all(engine) # 被Base管理的所有表，创建出来
def drop_db():
engine = create_engine(
"mysql+pymysql://root:111@127.0.0.1:3306/aaa?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
Base.metadata.drop_all(engine) # 删除所有被Base管理的表
if __name__ == '__main__':
init_db()
# drop_db()
3.1 单表
import datetime
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.ext.declarative import declarative_base
# 创建对象的基类
Base = declarative_base()
class Users(Base):
id = Column(Integer, primary_key=True) # id 主键
name = Column(String(32), index=True, nullable=False) # name列，索引，不可为空
email = Column(String(32), unique=True) # 唯一
# datetime.datetime.now不能加括号调用，加了括号，以后永远是当前的执行时间
ctime = Column(DateTime, default=datetime.datetime.now)
extra = Column(Text, nullable=True)
__tablename__ = 'users' # 数据库表名称
__table_args__ = (
UniqueConstraint('id', 'name', name='uix_id_name'), # 联合唯一
Index('ix_id_name', 'name', 'email'), # 联合索引
)
def __str__(self):
return self.name
def __repr__(self):
return self.name
# 注：
对比django-ORM：没有Meta类元信息，直接以'__tablename__' 双划线字段 指定
3.2 一对多
# 一对多关系：
一个Hobby 可以有多个人喜欢，关联字段写在多的一方 Person
from sqlalchemy.orm import relationship # 用来链表操作
class Hobby(Base):
__tablename__ = 'hobby'
id = Column(Integer, primary_key=True)
caption = Column(String(50), default='篮球')
class Person(Base):
__tablename__ = 'person'
nid = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=True)
# hobby指的是tablename(表名)而不是类名，跟hobby表的id字段建立外键关系
hobby_id = Column(Integer, ForeignKey("hobby.id"))
# 跟数据库无关，不会新增字段，只用于快速链表操作
# 类名，backref参数用于反向查询
hobby = relationship('Hobby', backref='persons')
3.3 多对多
# 多对多关系：
男孩和女孩约会，一个男孩可以约多个女孩，一个女孩可以约多个男孩
# 第三张表
class Boy2Girl(Base):
__tablename__ = 'boy2girl'
id = Column(Integer, primary_key=True, autoincrement=True)
girl_id = Column(Integer, ForeignKey('girl.id'))
boy_id = Column(Integer, ForeignKey('boy.id'))
class Girl(Base):
__tablename__ = 'girl'
id = Column(Integer, primary_key=True)
name = Column(String(64), unique=True, nullable=False)
class Boy(Base):
__tablename__ = 'boy'
id = Column(Integer, primary_key=True, autoincrement=True) # 主键默认自增
name = Column(String(64), unique=True, nullable=False)
# 与生成表结构无关，仅用于查询方便,放在哪个单表中都可以 secondary 指定多对多的第三张表
girls = relationship('Girl', secondary='boy2girl', backref='boys')
4 基于scoped_session实现线程安全
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from models import Users
engine = create_engine("mysql+pymysql://root:111@127.0.0.1:3306/aaa", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
"""
# 线程安全，基于本地线程实现每个线程用同一个session
# 特殊的：scoped_session中有原来方法的Session中的一下方法：
public_methods = (
'__contains__', '__iter__', 'add', 'add_all', 'begin', 'begin_nested',
'close', 'commit', 'connection', 'delete', 'execute', 'expire',
'expire_all', 'expunge', 'expunge_all', 'flush', 'get_bind',
'is_modified', 'bulk_save_objects', 'bulk_insert_mappings',
'bulk_update_mappings',
'merge', 'query', 'refresh', 'rollback',
'scalar'
)
# scoped_session类 并没有继承Session类,但是却有它的所有方法
本质是用了一个装饰器函数，循环将上面的public_methods 动态写入了scoped_session类中
"""
# session=Session() 以后不直接Session对象，而是使用scoped_session 包裹
session = scoped_session(Session)
# ############# 执行ORM操作 #############
obj1 = Users(name="egon111")
session.add(obj1)
# 提交事务
session.commit()
# 关闭session
session.close()
5 单表的数据操作
5.1 基本的增删改查
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Users, Person, Hobby, Girl, Boy, Boy2Girl
# 第一步：得到engine对象
engine = create_engine("mysql+pymysql://root:111@127.0.0.1:3306/aaa", max_overflow=0, pool_size=5)
# 第二步：得到sessionmaker对象，当成一个Session类
Session = sessionmaker(bind=engine)
# 第三步：创建Session对象
# 每次执行数据库操作时，都需要创建一个session
session = Session()
# 第四步：以后使用session来操作数据
### 1 增单个
# 1.先创建出一个user对象
lqz = Users(name='lqz', email='33@qq.com')
# 2.把对象增加到数据库中
session.add(lqz) # add() 只能同时增一个
# 3.提交事务 (增删改 需要提交)
session.commit()
# 4.把连接放回到池中
session.close()
### 2 增多个
# 1.先创建出一个表对象
lqz = Users(name='lqz1', email='333@qq.com')
egon = Users(name='egon', email='343@qq.com')
lyf=Girl(name='刘亦菲')
# 2.把对象增加到数据库中
session.add_all([lqz,egon,lyf]) # add_all() 能同时增多个 参数是可迭代对象
# 3.提交事务
session.commit()
# 4.把连接放回到池中
session.close()
### 3 基本查 查是最多的，现在先讲简单的
lqz=session.query(Users).filter_by(name='lqz').first() # 查一个 返回Users对象
lqz=session.query(Users).filter_by(name='lqz').all() # 查所有 返回列表套对象
print(lqz)
session.close()
### 4 删除 先查再删 不能查到具体的对象 没有delete和update方法 只能到queryset对象层
res=session.query(Users).filter_by(name='lqz').delete() # 返回影响的行数
session.commit()
session.close()
### 5 修改 先查再改
res=session.query(Users).filter_by(name='lqz1').update({'name':'lqz_nb'}) # update参数 只能字典的形式
# 类似于原来的F查询：把表中字段取出来使用
# synchronize_session=False 表示字符串相加 synchronize vt.（使）同步
res=session.query(Users).filter_by(name='lqz_nb').update({Users.name: Users.name + "099"}, synchronize_session=False)
# synchronize_session="evaluate" 表示数字相加
session.query(Users).filter_by(name='1').update({"id": Users.id + 10}, synchronize_session="evaluate")
# 注：
如果原字段数据不能转成数字，会直接设为0 # 其实有错，但不会报错
session.commit()
session.close()
5.2 详细的查询操作
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Users, Person, Hobby, Girl, Boy, Boy2Girl
from sqlalchemy.sql import text
# 第一步：得到engine对象
engine = create_engine("mysql+pymysql://root:111@127.0.0.1:3306/aaa", max_overflow=0, pool_size=5)
# 第二步：得到Session对象，当成一个类
Session = sessionmaker(bind=engine)
# 第三步：创建session对象
# 每次执行数据库操作时，都需要创建一个session
session = Session()
# 第四步：以后使用session来操作数据
# 1.查询所有
# select * from users;
r = session.query(Users).all() # 返回的是列表
# 2.查询特定字段 且重命名字段
# select name as xx ,email from users;
r = session.query(Users.name.label('xx'), Users.email).all() # 返回的是列表套元祖
# 3.查询过滤条件
filter 和 filter_by
# filter 传的是表达式
r = session.query(Users).filter(Users.id >= 10).all()
r = session.query(Users).filter(Users.id == 3).all()
# filter_by 传的是参数
r = session.query(Users).filter_by(name='egon').all()
r = session.query(Users).filter_by(name='egon').first()
# 4.查询过滤条件 之占位符 需要借助 text函数
from sqlalchemy.sql import text
:value 占位数字
:name 占位字符串 用params传参数
# select * from users where id <224 and name = 'fred'
r = session.query(Users).filter(text("id<:value and name=:name")).params(value=10, name='egon1').all()
# 5.自定义查询sql 了解
r = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='egon').all()
5.3 查询常用操作
# 打印原生sql语句 去掉.all()等获取具体对象的方法 即可
aa=session.query(Person).join(Favor, isouter=True)
print(aa)
# 1.条件查询
# filter_by 传的是参数
ret = session.query(Users).filter_by(name='egon').all()
# filter传表达式 默认以 and 连接
ret = session.query(Users).filter(Users.id > 1, Users.name == 'egon').all()
# '.between' 在什么之间 前闭后闭
# select * from user where name=egon id between 1 and 3;
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'egon').all()
# '.in_' 符合某一个 注意下划线
ret = session.query(Users).filter(Users.id.in_([1,3,4]))
# '~' 非 取反 除什么外
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
# 子查询 二次筛选
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='egon'))).all()
from sqlalchemy import and_, or_
# or_包裹的都是or条件， and_包裹的都是and条件 Q查询
ret = session.query(Users).filter(and_(Users.id > 2, Users.name == 'egon')).all()
ret = session.query(Users).filter(or_(Users.id >10, Users.name == 'egon')).all()
ret = session.query(Users).filter(
or_(
Users.id < 2,
and_(Users.name == 'egon', Users.id > 3),
Users.extra != ""
)).all()
# 2.通配符，以e开头，不以e开头
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()
# 3.限制，用于分页，区间
ret = session.query(Users)[(8-1)*10:10] # 每页显示10条，第8页
# 4.排序，根据name降序排列（从大到小）
ret = session.query(Users).order_by(Users.name.desc()).all()
# 第一个条件降序重复后，再按第二个条件升序排
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc())
# 5.分组：一旦用了分组，查询的字段只能是分组字段和聚合函数的字段 因为其他字段重复了 不知道咋放
# select * from users group by user.extra
ret = session.query(Users).group_by(Users.extra)
# 分组+聚合 取最大id，id之和，最小id 聚合函数min max avg sum count
from sqlalchemy.sql import func
# select max(id),sum(id),min(id) from users group by users.name
ret = session.query(
func.max(Users.id),
func.sum(Users.id),
func.min(Users.id)).group_by(Users.name).all()
# 分组+筛选 haviing
# select max(id),sum(id),min(id) as min_id from users group by users.name having min_id>2
ret = session.query(
func.max(Users.id),
func.sum(Users.id),
func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()
'''
Django--orm：
filter 在annotate前 表示过滤
value 在annotate前 表示分组的字段
filter 在annotate后 表示having
value 在annotate后 表示取字段
'''
Users.object.all().value(Users.name)
.annotate(min_id=Min(Users.id),max_id=Max(Users.id),sum_id=Sum(Users.id))
.filter(min_id__gte=2)
.values(min_id,max_id,sum_id)
# 6.连表 默认用forinkey关联
# select * from person,hobby where person.hobby_id=hobby.id
ret = session.query(Person, Hobby).filter(Person.hobby_id == Hobby.id)
# join连表 表示是inner join 共有字段
# select * from person inner join on person.hobby_id=hobby.id
ret = session.query(Person).join(Hobby)
# isouter外连 表示左连接 Person left join Favor 没有右连接，反过来即可
ret = session.query(Person).join(Hobby, isouter=True)
ret = session.query(Hobby).join(Person, isouter=True)
# isouter外连 可指定on条件(连表条件 某些没有外键关系，可通过条件拼接表) 支持多个条件，用and_,同上
# SELECT * FROM person LEFT OUTER JOIN hobby ON hobby.id = person.id
ret = session.query(Person).join(Hobby,Person.nid==Hobby.id, isouter=True)
# 7.组合 UNION 操作符用于合并两个或多个 SELECT 语句的结果集 了解
# union和union all的区别？
union ： 合并 且去重
union all ： 合并 不去重
q1 = session.query(Person.name).filter(Person.nid > 0)
q2 = session.query(Hobby.caption).filter(Hobby.id > 0)
ret = q1.union(q2).all()
q1 = session.query(Person.name).filter(Person.nid > 0)
q2 = session.query(Hobby.caption).filter(Hobby.id > 0)
ret = q1.union_all(q2).all()
print(ret)
6.多表的数据操作
6.1基于relationship操作一对多
import time
import threading
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from sqlalchemy.engine.result import ResultProxy
from db import Users, Hosts, Hobby, Person
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# 新增数据
# 方式一：外键字段 _id 但相对应的数据要先创建
session.add_all([
Hobby(caption='乒乓球'),
Hobby(caption='羽毛球'),
Person(name='张三', hobby_id=3),
Person(name='李四', hobby_id=4),
])
# 方式二：外键字段 为表对象 正向 为字段属性
person = Person(name='张九', hobby=Hobby(caption='姑娘'))
session.add(person)
# 反向 为表对象的'.'属性 赋值 属性名 由 relations的 backref决定
hb = Hobby(caption='人妖')
hb.persons = [Person(name='文飞'), Person(name='博雅')]
session.add(hb)
session.commit()
# 使用relationship 正向查询 基于对象的跨表查询
v = session.query(Person).first()
print(v.name)
print(v.hobby.caption) # 跨表hobby对象的caption字段
# 使用relationship 反向查询 基于对象的跨表查询
v = session.query(Hobby).first()
print(v.caption)
print(v.persons)
session.close()
6.2 基于relationship操作多对多
import time
import threading
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from sqlalchemy.engine.result import ResultProxy
from models import Users, Person, Hobby, Girl, Boy, Boy2Girl
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# 新增
# 方式一： 先创建各自对象，再中间表创建关系
boy = Boy(name='小刚')
girl1 = Girl(name='小静')
girl2 = Girl(name='小月')
session.add_all([boy,girl1,girl2])
session.commit()
b1=Boy2Girl(girl_id=4,boy_id=2)
b2=Boy2Girl(girl_id=5,boy_id=2)
session.add_all([b1,b2])
session.commit()
# 方式二： 通过relations创建 前提是Boy表中有girls字段属性 正向
boy = Boy(name='小王', girls=[Girl(name='小花'), Girl(name='小华')])
session.add(boy)
session.commit()
# 反向
girl = Girl(name='小美')
girl.boys=[Boy(name='小王'), Boy(name='小花')]
session.add(girl)
session.commit()
# 基于对象的跨表查 正向
xg=session.query(Boy).filter_by(name='小刚').first()
print(xg.girls)
# 反向
xj=session.query(Girl).filter_by(name='小静').first()
print(xj.boys)
session.close()
