SQLAlchemy

目录

• 简单介绍
• 创建数据表
• 操作数据库
  • 增加数据 (insert)
  • 查询数据 (select)
  • 修改数据 (update)
  • 删除数据 (delete)
• 高级查询操作
• 高级修改操作
• 数据类型参数
• 常用 SQLAlchemy 列选项
• 常用 SQLAlchemy 关系选项
• 查询过滤器
  • 查询执行函数

简单介绍

SQLAlchemy是Python SQL工具包和对象关系映射器(ORM框架)

优点:
	- 兼容性比较强
	- SQL的功能全面和灵活性强。
	- 允许对象模型和数据库模式从一开始就干净地分离的方式发展。

创建数据表

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import engine

# declarative_base: 声明基类,  Base 就是 ORM 模型
Base = declarative_base()

# 当前的Object继承了Base也就是代表了Object继承了ORM的模型
class User(Base): # 相当于 Django Models中的 Model
    # 设置表名
    __tablename__ = 'users'  
    # 创建ID字段 == 创建ID数据列, 字段 = Column(数据类型,索引,主键,外键,等等)
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(16), nullable=False)

# 创建数据库引擎
my_engine = engine.create_engine("mysql+pymysql://root:123@127.0.0.1:3306/alchemy?charset=utf8")

# Base 自动检索所有继承Base的ORM 对象 并且创建所有的数据表
Base.metadata.create_all(my_engine)

操作数据库

增加数据 (insert)

# 导入之前创建好的ORM 对象
from create_tab import User, my_engine
# 打开数据库会话, 创建了一个操纵数据库的窗口
from sqlalchemy.orm import sessionmaker

# 创建 sessionmaker 会话对象,将数据库引擎 engine 交给 sessionmaker (相当于选择要操作的数据库)
Session = sessionmaker(my_engine)
# 打开会话对象 Session, 创建查询窗口
db_session = Session()  

1. 添加一条语句
# 使用Users ORM模型创建一条数据
user = User(name='jack')
# 在db_session会话中添加一条 UserORM 模型创建的数据
db_session.add(user)
# 使用 db_session 会话执行sql语句, 将所有指令一次性提交
db_session.commit()

2. 添加多条语句
user_list = [
    User(name="jason1"),
    User(name="jason2"),
    User(name="jason3")
]
db_session.add_all(user_list)
db_session.commit()
# 关闭会话
db_session.close()

查询数据 (select)

from create_tab import User, my_engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(my_engine)
db_session = Session()  

1. 查询所有数据: all()
# 拿到 object 对象
user_list = db_session.query(User).all()
# ORM对象 直接使用调用属性的方法, 拿出对应字段的值
for item in user_list:
    print(item.id, item.name)

2. 查询一条语句
res = db_session.query(User).first()
print(res.id, res.name)

3. 带条件的查询
res = db_session.query(User).filter(User.id == 2).first()
res = db_session.query(User).filter(User.id >= 2).all()
for i in res:
    print(i.id, i.name)
db_session.close()

注意: 如果不取数据, 返回值是原生的 SQL 语句
res = db_session.query(User).filter(User.id >= 20)

修改数据 (update)

1. 更新一条语句
res = db_session.query(User).filter(User.id == 2).update({'name': 'jack1'})
# 返回执行更新的的条数
print(res) 
db_session.commit()

2. 更新多条
res = db_session.query(User).filter(User.id >= 2).update({'name': 'jack'})
db_session.commit()
db_session.close()

删除数据 (delete)

res = db_session.query(User).filter(User.id == 2).delete()
# 返回执行被删除的条数
print(res)
db_session.commit()
db_session.close()

高级查询操作

from create_tab import User, my_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import text
from sqlalchemy import or_, and_

Session = sessionmaker(my_engine)
db_session = Session()  

1. or、 and 查询, and 通常用于多层次嵌套时候使用
res = db_session.query(User).filter(or_(User.id == 1, User.id == 3)).all()
res = db_session.query(User).filter(User.id >= 2, User.id <= 5).all() # between

2. 并列条件查询
res = db_session.query(User).filter(User.id == 2, User.name == 'jack').all()

3. 给指定的查询数据列加入别名映射 (name as username)
res = db_session.query(User.name.label('username'), User.id).first()
print(res.id, res.username)

4. 原生SQL筛选条件
res = db_session.query(User).filter_by(name='jason').all()

5. 字符串匹配方式筛选条件, 并使用 order_by 进行排序
res = db_session.query(User).filter(text("id<:value and name=:name")).params(value=224, name='jason').order_by(User.id).all()

6. 原生SQL查询
res = db_session.query(User).from_statement(text("SELECT * FROM User where name=:name")).params(name='jason').all()

7. query的时候不再使用 User ORM 对象,而是使用 User.name来对内容进行选取
user_list = db_session.query(User.name).all()
for row in user_list:
    print(row.name)
    
8. 排序 : asc()
user_list = db_session.query(User).order_by(User.id.desc()).all()

9. 其他查询
1) in_([1,3,4])  # 只查询id 等于1,3,4的
ret = db_session.query(User).filter(User.id.in_([1,3,4])).all() 

2) ~User.in_([1,3,4]) # 查询不等于1,3,4的
ret = db_session.query(User).filter(~User.id.in_([1,3,4])).all() 

3) 子查询
ret = db_session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='jason'))).all()

4) 通配符
ret = db_session.query(User).filter(User.name.like('e%')).all()

5) 限制
ret = db_session.query(User)[1:2]

6) 分组
from sqlalchemy.sql import func

ret = db_session.query(User).group_by(User.extra).all()
ret = db_session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).all()

ret = db_session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all()

高级修改操作

1. 在原有值基础上添加 - 1
=db_session.query(User).filter(User.id > 0).update({User.name: User.name + "099"}, synchronize_session=False)

2. 在原有值基础上添加 - 2
db_session.query(User).filter(User.id > 0).update({"age": User.age + 1}, synchronize_session="evaluate")

db_session.commit()

synchronize_session用于query在进行delete or update操作时，对db_session的同步策略。
False: 表示不同步
evaluate: 在操作之前，用query中的条件直接对session的identity_map中的objects进行eval操作，将符合条件的记录下来。

数据类型参数

类型名	Python类型	说明
Integer	int	普通整数，一般是 32 位
SmallInteger	int	取值范围小的整数，一般是 16 位
Big Integer	int 或 long	不限制精度的整数
Float	float	浮点数
Numeric	decimal.Decimal	定点数
String	str	变长字符串
Text	str	变长字符串，对较长或不限长度的字符串做了优化
Unicode	unicode	变长 Unicode 字符串
Unicode Text	unicode	变长 Unicode 字符串，对较长或不限长度的字符串做了优化
Boolean	bool	布尔值
Date	datetime.date	日期
Time	datetime.time	时间
DateTime	datetime.datetime	日期和时间
Interval	datetime.timedelta	时间间隔
Enum	str	一组字符串
PickleType	任何 Python 对象	自动使用 Pickle 序列化
LargeBinary	str	二进制文件

常用 SQLAlchemy 列选项

选项名	说明
primary_key	如果设为 True，这列就是表的主键
unique	如果设为 True，这列不允许出现重复的值
index	如果设为 True，为这列创建索引，提升查询效率
nullable	如果设为 True，这列允许使用空值；如果设为 False，这列不允许使用空值
default	为这列定义默认值

常用 SQLAlchemy 关系选项

选项名	说明
backref	在关系的另一个模型中添加反向引用
primaryjoin	明确指定两个模型之间使用的联结条件。只在模棱两可的关系中需要指定.
lazy	指定如何加载相关记录。可选值select、joined等
uselist	如果设为 Fales，不使用列表，而使用标量值
order_by	指定关系中记录的排序方式
secondary	指定多对多关系中关系表的名字
secondaryjoin	SQLAlchemy 无法自行决定时，指定多对多关系中的二级联结条件

lazy 可选值如下 :
	- select     # 首次访问时按需加载
	- immediate  # 源对象加载后就加载
	- joined     # 加载记录，但使用联结
	- subquery   # 立即加载，但使用子查询
	- noload     # 永不加载
	- dynamic    # 不加载记录，但提供加载记录的查询

查询过滤器

过滤器	说明
filter()	把过滤器添加到原查询上, 返回一个新查询
filter_by()	把等值过滤器添加到原查询上, 返回一个新查询
limit()	使用是zing的值限制原查询返回的结果数量, 返回一个新查询
offset()	偏移原查询返回的结果, 返回一个新查询
order_by()	根据指定条件对原查询结果进行排序, 返回一个新查询
group_by()	根据指定条件对原查询结果进行分组, 返回一个新查询

查询执行函数

方法	说明
all()	以列表形式返回查询的所有结果
first()	返回查询的第一个结果，如果没有结果，则返回 None
first_or_404()	返回查询的第一个结果，如果没有结果，则终止请求，返回 404 错误响应
get()	返回指定主键对应的行，如果没有对应的行，则返回 None
get_or_404()	返回指定主键对应的行，如果没找到指定的主键，则终止请求，返回 404错误响应
count()	返回查询结果的数量
paginate()	返回一个 Paginate 对象，它包含指定范围内的结果