sqlalchemy 使用 ORM 查询,分页以及返回json的方式
目前学习到该处,记录下,有更正确或优雅的方式,欢迎评论区里告知,谢谢
貌似直接使用 skip (offset) 的方式更简洁
前端计算skip(offset) 的 值,
由 page limit 的方式 改用 : skip 和 limit 的方式
改动最小
助手函数版
# common/dbhelper
from sqlalchemy.orm.query import Query
from app.schemas.common import Page
def EmptyWhere(i):
return (i is not None) and (i != '')
def CreateWhere(_model, _data, _col_list: list) -> list:
sql_where = []
if type(_data) == dict:
for col in _col_list:
if EmptyWhere(_data.get(col)):
sql_where.append(getattr(_model, col) == _data.get(col))
if type(_data) == object:
for col in _col_list:
if EmptyWhere(getattr(_data, col)):
sql_where.append(getattr(_model, col) == getattr(_data, col))
return sql_where
def GetPage(query: Query, page: int, limit: int) -> Page:
offset = int((page - 1) * limit)
total = query.count()
results_dict = query.offset(
0 if offset < 0 else offset).limit(limit).all()
return Page(page=page, limit=limit, total=total, data=results_dict)
orm 的版本
from sqlalchemy.orm import Session
# orm 的版本
def get_list(db: Session,
page: int = 1, limit: int = 10,
xx: str = None, bb: str = None):
offset = (page - 1) * limit
sql_where = []
if not xx is None:
sql_where.append(models.xx.Xx.xx == xx)
if not bb is None:
sql_where.append(models.xx.Xx.bb == bb)
query = db.query(models.xx.Xx).where(*sql_where)
results_dict = query.offset(offset).limit(limit).all()
total = query.count()
res = {
'page': page,
'limit': limit,
'total': total,
'data': results_dict,
}
return res
sql 版本
from sqlalchemy.orm import Session
from sqlalchemy.sql import text
# sql 版本
def get_list(db: Session, page: int = 1, limit: int = 10):
offset = (page - 1) * limit
total = 0
try:
total = db.execute(text('SELECT count(*) FROM xxx;')).one()[0]
except Exception as e:
print('Exception', e)
# 执行SQL语句
with db:
result = db.execute(text('SELECT * FROM xxx limit :offset,:limit;'),
{"offset": offset, "limit": limit})
# 将结果转换为字典列表
results_dict = [dict(row) for row in result.mappings()]
res = {
'page': page,
'limit': limit,
'total': total,
'data': results_dict,
}
return res