def rollback(self):
"""
手动回滚
:return:
"""
try:
self._session.rollback()
except Exception as e:
logger.error(e)
def commit(self):
"""
提交
:return:
"""
try:
self._session.commit()
except Exception as e:
self._session.rollback()
logger.error(e)
return False
return True
def delete_sql(self, sql):
"""
删除sql
:param sql:
:return:
"""
try:
self._session.execute(sql)
self._session.flush()
except Exception as e:
self._session.rollback()
logger.error(e)
return False
return True
def select_sql(self, sql):
"""
查询sql
:param sql:
:return:
"""
try:
result = self._session.execute(sql).fetchall()
self._session.flush()
except Exception as e:
self._session.rollback()
logger.error(e)
return False
return result
def delete(self, ins):
"""
模型 删除
:param ins:
:return:
"""
try:
self._session.delete(ins)
self._session.flush()
except Exception as e:
self._session.rollback()
logger.error(e)
return False
return True
def save(self, ins):
"""
模型 新增 保存
:param ins:
:return:
"""
try:
self._session.add(ins)
self._session.flush()
except Exception as e:
self._session.rollback()
logger.error(e)
return False
return ins
def save(self):
"""
保存
:param is_commit:
:return:
"""
try:
db.session.add(self)
db.session.commit()
return self
except Exception as e:
db.session.rollback()
logger.error(e)
return 0
def deleteList(self, inss: list):
"""
模型 批量删除
:param inss:
:return:
"""
try:
for ins in inss:
self._session.delete(ins)
self._session.flush()
except Exception as e:
self._session.rollback()
logger.error(e)
return False
return True
def delete(self):
"""
删除
:param is_commit:
:return:
"""
try:
db.session.delete(self)
db.session.commit()
return True
except Exception as e:
# 加入数据库commit提交失败，必须回滚！！！
db.session.rollback()
logger.error(e)
return 0
def update(self, **kwargs):
"""
更新
:param is_commit:
:param kwargs:
:return:
"""
try:
for key, value in kwargs.items():
setattr(self, key, value)
return self.save()
except Exception as e:
db.session.rollback()
logger.error(e)
return 0
def update_sql(self, sql):
"""
sql 更新
如 获取 更新后的数据 ,单个 可使用 get_cls_by_id,多个 执行sql
:param sql:
:return:
"""
try:
self._session.execute(sql)
self._session.flush()
except Exception as e:
self._session.rollback()
logger.error(e)
return False
return True
def insert_sql(self, sql):
"""
sql 插入
如 获取 新增后的数据 ,单个 可使用 get_cls_by_id,多个 执行sql
:param sql:
:param table_name:
:param only_key:
:return:
"""
try:
self._session.execute(sql)
self._session.flush()
except Exception as e:
self._session.rollback()
logger.error(e)
return False
return True
def update(self, ins, dataDict):
"""
模型更新
:param ins: object
:param dataDict: 更新信息
:return:
"""
try:
for key, value in dataDict.items():
setattr(ins, key, value)
self._session.add(ins)
self._session.flush()
except Exception as e:
self._session.rollback()
logger.error(e)
return False
return ins
def serachView(dataDict: dict,
tableName: db.Model,
groupBy: str = "",
orderByStr: str = "",
otherCondition: str = ""):
"""
reqeusts args:
{
"condition":[
{"field":"id","op":"equal","value":1},
{"field":"id","op":"notequal","value":2},
{"field":"id","op":"notin","value":"(2)"},
{"field":"id","op":"in","value":"(1,2)"},
{"field":"id","op":"less","value":3},
{"field":"id","op":"greater","value":0},
{"field":"id","op":"llike","value":1},
{"field":"id","op":"rlike","value":1},
{"field":"id","op":"like","value":1}
],
"page":{"pageIndex":1,"pageSize":30},
"multiSort":{"id":"desc"}
}
# example:
groupBy = " group by id "
orderByStr = " order by sort_id desc "
otherCondition = " id != 99 "
resultList = serachView(dataDict, tablename, groupBy=groupBy, orderByStr=orderByStr,otherCondition=otherCondition)
# id = 1 and id != 2 and id not in (2) and id in (1,2) and id < 3 and id > 0 and id like '%1' and id like '1%' and id like '%1%' order by id desc limit 0,30
:param dataDict:
:param tableName:
:param sqlStr:
:param groupBy:
:param orderByStr:
:param deptIdConditonStr:
:return:
"""
opDic = {
"in": "in",
"notin": "not in",
"equal": "=",
"notequal": "!=",
"less": "<",
"greater": ">",
"is": "is",
"llike": "like",
"rlike": "like",
"like": "like",
"contains": "like"
}
# 排序编辑
multiSort: dict = dataDict.get("multiSort", {})
if multiSort:
orderList = []
for key, value in multiSort.items():
orderStr = f"{key} {value}"
orderList.append(orderStr)
if orderByStr and orderList:
orderByStr += ","
orderByStr += " , ".join(orderList)
# 条件编辑
condition: list = dataDict.get("condition", [])
sqlStr: str = ""
if condition:
conditionList: list = []
for cond in condition:
field, op, value = cond["field"], cond["op"], cond["value"]
if field not in tableName.__table__.columns:
# 如果模型中没有此字段 就跳过
continue
if op == "llike":
sql_condition = f"{field} {opDic[op]} '%{str(value)}'"
elif op == "rlike":
sql_condition = f"{field} {opDic[op]} '{str(value)}%'"
elif op == "like":
sql_condition = f"{field} {opDic[op]} '%{str(value)}%'"
elif op in ["in", "not in", "is"]:
sql_condition = f"{field} {opDic[op]} '{str(value)}'"
else:
sql_condition = f"{field} {opDic[op]} {str(value)}"
conditionList.append(sql_condition)
sqlStr = " and ".join(conditionList)
if sqlStr.strip():
if otherCondition:
sqlStr = sqlStr + " and " + otherCondition
else:
sqlStr = sqlStr
else:
sqlStr = otherCondition
# 分页编辑
pageDic: dict = dataDict.get("page", {})
pageIndex: int = pageDic.get("pageIndex", 1)
pageSize: int = pageDic.get("pageSize", 20)
if pageIndex <= 0: pageIndex = 1
if pageSize > 50: pageSize = 50
try:
sqlStrquery = text(sqlStr)
orderByStr = text(orderByStr.strip())
groupBy = text(groupBy.strip())
if sqlStr.strip():
tableList = tableName.query.filter(sqlStrquery).group_by(
groupBy).order_by(orderByStr)
else:
tableList = tableName.query.group_by(groupBy).order_by(orderByStr)
# 这样返回可以使用更多分页的特性
return tableList.paginate(pageIndex,
per_page=pageSize,
error_out=False)
except Exception as e:
logger.error(e)
return []