SQLAlchemy数据库查询

SQLAlchemy 查询示例

1. 限制查询返回结果

# 限制返回条数，从第一条开始
print(session.query(User).filter(User.username != 'jingqi').limit(2).all())

# 从第三条开始返回查询结果
print(session.query(User).filter(User.username != 'jingqi').offset(2).all())

# 切片返回记录
print(session.query(User).filter(User.username != 'jingqi').slice(2, 3).all())

2. 排序之后再进行限制

from sqlalchemy import desc

# 按用户名升序排序
print(session.query(User).filter(User.username != 'budong').order_by(User.username).all())

# 按用户名降序排序，并进行切片
print(session.query(User).filter(User.username != 'budong').order_by(desc(User.username)).slice(1, 3).all())

3. 不等于查询

print(session.query(User).filter(User.username != 'jingqi').all())

4. 模糊匹配

# 使用 like 进行模糊匹配
print(session.query(User).filter(User.username.like('jingqi')).all())

# 使用 notlike 进行不匹配
print(session.query(User).filter(User.username.notlike('jingqi')).all())

5. 成员属于和不属于查询

# 成员属于 in_
print(session.query(User).filter(User.username.in_(['jingqi', 'jingqi1'])).all())

# 成员不属于 notin_
print(session.query(User).filter(User.username.notin_(['jingqi', 'jingqi2'])).all())

6. 空判断

# 判断 username 是否为 None
print(session.query(User).filter(User.username == None).all())
print(session.query(User).filter(User.username.is_(None)).all())
print(session.query(User).filter(User.username.isnot(None)).all())

7. 多条件查询

print(session.query(User).filter(User.username.isnot(None), User.password == 'qwe123').all())

8. 选择条件

from sqlalchemy import or_, and_

# 或条件
print(session.query(User).filter(or_(User.username == 'jingqi', User.password == 'qwe123')).all())

# 与条件
print(session.query(User).filter(and_(User.username == 'jingqi2', User.password == '111')).all())

9. 字符串开头和结尾匹配

# 查询名字结尾字符为 'g' 的所有数据
endwith_g = session.query(User).filter(User.name.endswith("g")).all()
print(endwith_g)

# 查询名字开头字符为 'z' 的所有数据
startwith_z = session.query(User).filter(User.name.startswith("z")).all()
print(startwith_z)

10. 分页查询

# 每页3个，查询第2页的数据
ret = session.query(User).paginate(2, 3)  # 假设有使用 Flask-SQLAlchemy
print(ret.page)
print(ret.items)

11. 聚合函数

from sqlalchemy import func

# 按密码分组并计数
print(session.query(User.password, func.count(User.id)).group_by(User.password).all())

# 按密码分组并计数，且计数大于1
print(session.query(User.password, func.count(User.id)).group_by(User.password).having(func.count(User.id) > 1).all())

# 求和、最大值和最小值
print(session.query(User.password, func.sum(User.id)).group_by(User.password).all())
print(session.query(User.password, func.max(User.id)).group_by(User.password).all())
print(session.query(User.password, func.min(User.id)).group_by(User.password).all())

12. 使用 extract 提取时间中的分钟或天

from sqlalchemy.sql import extract

# 提取分钟
print(session.query(extract('minute', User.creatime).label('minute'), func.count('*').label('count')).group_by('minute').all())

# 提取天
print(session.query(extract('day', User.creatime).label('day'), func.count('*').label('count')).group_by('day').all())

13. 多表查询

内连接

# 内连接示例
for u, a in session.query(User, Address).filter(User.id == Address.user_id).filter(Address.email_address == 'jack@google.com').all():
    print(u)
    print(a)

# 另一种内连接
print(session.query(User.username, UserDetails.lost_login).join(UserDetails, UserDetails.id == User.id).all())

左连接

# 左连接示例
print(session.query(User.username, UserDetails.lost_login).outerjoin(UserDetails, UserDetails.id == User.id).all())

子查询

# 声明一个子表
sql_0 = session.query(UserDetails.lost_login).subquery()

# 使用子查询
print(session.query(User).filter((User.creatime > all_(sql_0))).all())
print(session.query(User).filter((User.creatime > any_(sql_0))).all())

14. 原生 SQL 查询

# 原生 SQL 查询
sql_1 = """
SELECT * FROM user
"""
row = session.execute(sql_1)
print(row, dir(row))

# 获取数据
print(row.fetchone())
print(row.fetchmany())
print(row.fetchall())

# 循环获取
for i in row:
    print('===', i)