def getAll(self, params, page, limit):
whereStr = " 1=1 "
limitStr = ""
if ("jobName" in params.keys()):
whereStr = whereStr + " and jobName=\'" + params["jobName"] + "\'"
if ("jobParams" in params.keys()):
whereStr = whereStr + " and jobParams=\'" + params[
"jobParams"] + "\'"
if ("jobDesc" in params.keys()):
whereStr = whereStr + " and jobDesc=\'" + params["jobDesc"] + "\'"
if ((page != None) and (limit != None)):
limitStr = " order by id desc limit " + str(
(int(page) - 1) * int(limit)) + " , " + str(limit)
sqlStr = whereStr + limitStr
try:
jobDescs = dbSession.query(JobDesc).filter(text(sqlStr)).all()
jobsDescTotal = dbSession.query(func.count(JobDesc.id)).filter(
text(whereStr)).all()
results = self.getResult(jobDescs, totalTmps=jobsDescTotal)
dbSession.commit()
except Exception as e:
dbSession.rollback()
app.logger.info("ERROR:ServiceJobDesc.getAll():" +
e.args.__str__())
return results
def get_list(self, filters, order, field=(), offset=0, limit=10):
"""
列表
:param cls_:
:param filters:
:param order:
:param field:
:param offset:
:param limit:
:return:
"""
result = {}
result['page'] = {}
result['page']['count'] = dbSession.query(Comment).filter(
*filters).count()
result['list'] = []
result['page']['total_page'] = self.get_page_number(
result['page']['count'], limit)
result['page']['current_page'] = offset
if offset != 0:
offset = (offset - 1) * limit
if result['page']['count'] > 0:
result['list'] = dbSession.query(Comment).filter(*filters)
result['list'] = result['list'].order_by(order).offset(
offset).limit(limit).all()
if not field:
result['list'] = [res.to_dict() for res in result['list']]
else:
result['list'] = [
res.to_dict(only=field) for res in result['list']
]
return result
def get_list(self, cls_: object, filters=(), order='id desc', field=(), offset=0, limit=10):
"""
列表
:param cls_:
:param filters:
:param order:
:param field:
:param offset:
:param limit:
:return:
"""
result = {}
result['page'] = {}
result['page']['count'] = dbSession.query(cls_).filter(*filters).count()
result['list'] = []
result['page']['total_page'] = self.get_page_number(result['page']['count'], limit)
result['page']['current_page'] = offset
if offset != 0:
offset = (offset - 1) * limit
if result['page']['count'] > 0:
result['list'] = dbSession.query(cls_).filter(*filters)
order_list = order.split(' ')
if order_list[1] == 'desc':
result['list'] = result['list'].order_by(desc(order_list[0])).offset(offset).limit(limit).all()
else:
result['list'] = result['list'].order_by(asc(order_list[0])).offset(offset).limit(limit).all()
if not field:
result['list'] = [res.to_dict() for res in result['list']]
else:
result['list'] = [res.to_dict(only=field) for res in result['list']]
return result
def getByIpByDate(self, params, page=None, limit=None):
results = {}
whereStr = " 1=1 "
if ("ip" in params.keys()):
whereStr = whereStr + " and ip=\'" + params["ip"] + "\'"
if ("reportDate" in params.keys()):
whereStr = whereStr + " and reportDate=\'" + params[
"reportDate"] + "\'"
if ((page != None) and (limit != None)):
limitStr = " order by ip limit " + str(
(int(page) - 1) * int(limit)) + " , " + str(limit)
sqlStr = whereStr + limitStr
try:
backups = dbSession.query(Report).filter(text(sqlStr)).all()
backupsTotal = dbSession.query(func.count(Report.id)).filter(
text(whereStr)).all()
results = self.getResult(backups, totalTmps=backupsTotal)
dbSession.rollback()
except Exception as e:
dbSession.rollback()
app.logger.info(e.args.__str__())
return results
def deleteJob(self, jobId):
try:
dbSession.query(CronJob).filter(CronJob.jobId == jobId).delete()
dbSession.commit()
except Exception as e:
dbSession.rollback()
app.logger.info("ERROR:ServiceJob.deleteJob():" +
e.args.__str__() + jobId)
def delete(self, id):
"""
根据id删除用户
:param id:
:return:
"""
dbSession.query(User).filter_by(id=id).delete()
return dbSession.commit()
def update(email, updated_time):
"""
更新更新时间
:param email:
:param updated_time:
:return:
"""
dbSession.query(User).filter_by(email=email).update({'updated_time': updated_time})
return dbSession.commit()
def get_count(self, cls_: object, filters: set, field=None) -> int:
"""
统计数据
:param cls_: 数据模型实体类
:param filters: 查询条件
:param field: 查询字段
:return: int统计记录数量
"""
if field == None:
return dbSession.query(cls_).filter(*filters).count()
else:
return dbSession.query(cls_).filter(*filters).count(field)
def get_one(self, filters, order='id desc', field=()):
"""
获取一条
:param filters:
:param order:
:param field:
:return:
"""
result = dbSession.query(User).filter(*filters)
# 查询结果为空
if result == None:
return None
order_list = order.split(' ')
# 查询结果降序排列
if order_list[1] == 'desc':
result = result.order_by(desc(order_list[0])).first()
# 查询结果升序排列
else:
result = result.order_by(asc(order_list[0])).first()
# 查询字段为空
if not field:
result = result.to_dict()
else:
result = result.to_dict(only=field)
return result
def get(id):
"""
获取用户信息
:param id:
:return:
"""
return dbSession.query(User).filter_by(id=id).first()
def left_join():
"""
一对多left join 方式
:return:
"""
data = dbSession.query(Suggest).outerjoin(User, User.id == Suggest.user_id).all()
data_msg = Utils.list_to_dict(data)
return data_msg
def getMainMenu():
datas=[]
id=request.args.get("node");
if id=='root':
menus = dbSession.query(MainMenu).filter(MainMenu.parentId==0).all()
else:
menus=dbSession.query(MainMenu).filter(MainMenu.parentId==id).all()
for item in menus:
datas.append(item.to_json())
try:
dbSession.rollback()
except Exception as e:
app.logger.info(e.args.__str__())
dbSession.rollback()
return jsonify(datas)
def lock(self):
"""
行级锁
:return:
"""
query = dbSession.query(User).filter(
User.id == 34).with_for_update().first()
dbSession.execute('select sleep(10)')
def one_to_many():
"""
一对多普通方式
:return:
"""
data = dbSession.query(Suggest).filter(User.id == Suggest.user_id).all()
data_msg = Utils.list_to_dict(data)
return data_msg
def delete(self, cls_: object, filters: set) -> int:
"""
删除
:param cls_: 数据库模型实体类
:param filters: 查询条件
:return: int(删除的数据id)
"""
return dbSession.query(cls_).filter(*filters).delete(synchronize_session=False)
def getAll(self, page, limit):
whereStr = " 1=1 "
limitStr = ""
if ((page != None) and (limit != None)):
limitStr = " order by taskBeginTime desc limit " + str(
(int(page) - 1) * int(limit)) + " , " + str(limit)
sqlStr = whereStr + limitStr
try:
tasks = dbSession.query(Task).filter(text(sqlStr)).all()
tasksTotal = dbSession.query(func.count(Task.id)).filter().all()
results = self.getResult(tasks, totalTmps=tasksTotal)
dbSession.rollback()
except Exception as e:
dbSession.rollback()
app.logger.info(e.args.__str__())
return results
def edit(self, cls_: object, data: dict, filters: set) -> bool:
"""
修改
:param cls_: 数据库模型实体类
:param data: 数据
:param filters: 筛选条件
:return: bool
"""
return dbSession.query(cls_).filter(*filters).update(data, synchronize_session=False)
def get_data(uuid):
"""
根据uuid获取分片数据
:param uuid:
:return:
"""
obj = dbSession.query(ImgShard).filter_by(
uuid=uuid).order_by('index').all()
data = Utils.db_to_d(obj)
return data
def getByJobId(self, jobId):
job = None
try:
job = dbSession.query(CronJob).filter(
CronJob.jobId == jobId).first()
dbSession.commit()
except Exception as e:
dbSession.rollback()
app.logger.info("ERROR:ServiceJob.getByJobId():" +
e.args.__str__() + ":" + jobId)
return job
def get_all(self, cls_: object, filters: set, order: str = 'id desc', field: tuple = (), limit: int = 0) -> list:
"""
查询全部
:param cls_: 数据库模型实体类
:param filters: 查询条件
:param order: 排序
:param field: 字段
:param limit: 取多少条
:return: dict
"""
if not filters:
result = dbSession.query(cls_)
else:
result = dbSession.query(cls_).filter(*filters)
order_list = order.split(' ')
if order_list[1] == 'desc':
result = result.order_by(desc(order_list[0])).all()
else:
result = result.order_by(asc(order_list[0])).all()
if not field:
result = [res.to_dict() for res in result]
else:
result = [res.to_dict(only=field) for res in result]
return result
def get_one(self, filters, order='id desc', field=()):
"""
:param filters:
:param order:
:param field:
:return:
"""
result = dbSession.query(Log).filter(*filters)
order_list = order.split(' ')
if result == None:
return None
if order_list[1] == 'desc':
result = result.order_by(desc(order[0])).first()
else:
result = result.order_by(asc(order_list[0])).first()
if not field:
result = result.to_dict()
else:
result = result.to_dict(only=field)
return result
def get_one(self, cls_: object, filters: set, order: str = 'id desc', field: tuple = ()):
"""
获取一条
:param cls_: 数据库模型实体类
:param filters: 查询条件
:param order: 排序方式
:param field: 查询字段
:return: dict
"""
result = dbSession.query(cls_).filter(*filters)
order_list = order.split(' ')
if result == None:
return None
# 排序方式为降序
if order_list[1] == 'desc':
result = result.order_by(desc(order_list[0])).first()
else:
result = result.order_by(asc(order_list[0])).first()
if not field:
result = result.to_dict()
else:
result = result.to_dict(only=field)
return result
