def delete_method(self, session, orm):
"""
通用删除数据方法
:type session:
:param session: 数据库连接
:type orm: class
:param orm: model类 --该类需要有构造方法
:type datas: dict
:param datas:{
"user_id":6156161
}
:rtype: Boolean, str or dict
:return:True or False, str or dict
"""
data = self.request
key, value = data.items()[0]
ret = getattr(orm, key)
sql_result = session.query(orm).filter(ret == value)
if sql_result.count() is 1:
session.delete(sql_result.one())
result = operate_commit(session)
else:
operate_commit(session)
result = 'record is not exited'
return result
def add_method(self, session, orm):
"""
通用插入数据方法
:type session:
:param session: 数据库连接
:type orm: class
:param orm: model类 --该类需要有构造方法
:type datas: dict
:param datas:{
"a":"b",
"c":1
}
:rtype: Boolean
:return:True or False
"""
data = self.request
# 兼容Orm有初始化参数和无初始化参数
try:
model = orm(**data)
except:
model = orm()
for key, value in data.items():
setattr(model, key, value)
session.add(model)
return operate_commit(session)
def query_method(self, session, orm):
"""
通用查询数据详情方法
:type session:
:param session: 数据库连接
:type orm: class
:param orm: model类 --该类需要有构造方法
:type data: dict
:param data:{
"primary_key":{
"id":10006
},
"response":[
"name",
"des"
]
}
:rtype: Boolean, str or dict
:return:True or False, str or dict
"""
data = self.request
key, value = data['primary_key'].items()[0]
ret = getattr(orm, key)
sql_result = session.query(orm).filter(ret == value)
if sql_result.count() is 1:
result_content = sql_result.one()
# 判断是否自定义response
if data['response']:
result = {
res_key: getattr(result_content, res_key)
for res_key in data['response']
}
else:
# 返回所有
result = result_content.to_json()
state = operate_commit(session)
else:
operate_commit(session)
result = 'record is not exited'
state = False
return state, result
def modify_method(self, session, orm):
"""
通用编辑方法
:type session:
:param session: 数据库连接
:type orm: class
:param orm: model类 --该类需要有构造方法
:type data: dict
:param data:{
"primary_key":{
"id":10006
},
"items":{
"name":"llx"
}
}
:rtype: Boolean, str or dict
:return:True or False, str or dict
"""
data = self.request
primary_key = data['primary_key']
items = data['items']
# 获取查询key
primary_key_key, key_value = primary_key.items()[0]
ret_key = getattr(orm, primary_key_key)
sql_result = session.query(orm).filter(
ret_key == key_value).one_or_none()
if sql_result:
for item_key, item_value in items.items():
setattr(sql_result, item_key, item_value)
return operate_commit(session)
else:
return 'record is not exited'
def query_method(self, session, *orm):
"""
通用联表查询数据详情方法
:type session:
:param session: 数据库连接
:type Orm: list[class]
:param Orm: model类 --list[0]主表
:type reuqest: dict
:param reuqest:{
"cond": {
"name": "leas",
"des": "",
"create_time":{
"start_time":"2017-09-11 11:56:22",
"end_time":"2017-09-11 11:56:22"
}
},
"response":{
"news":["name", "des"],
"tag":["tag_name"],
"type":["type_name"]
}, #需要返回的的数据
"sort": {
"name": True
},
"limit": 2,
"page": 1
}
:rtype: Boolean, Int , list[dict]
:return:True or False, Int, list[dict]
"""
request = self.request
# 返回的list
response = request['response']
# 分页
limit = request['limit']
offset = (request['page'] - 1) * request['limit']
# &&条件
cond = request['cond']
sql_cond = []
main_orm = orm[0]
for key, value in cond.items():
ret = getattr(main_orm, key)
# 判断是不是时间段条件
# 时间段字段key字符串必须包含'time'字符串
if isinstance(value, dict) and 'time' in key:
sql_cond.append(ret.between(value.values()[0], value.values()[1]))
# 当value类型为list时添加in条件
elif isinstance(value, list):
sql_cond.append(ret.in_(value))
else:
sql_cond.append(ret.like('%' + str(value) + '%') if value is not None else "")
condition = and_(
*sql_cond
)
# 排序
sort = request['sort'] # key 排序字段 True 降序 False 升序
if sort:
sort_key, sort_value = sort.items()[0]
sort_ret = getattr(main_orm, sort_key)
if sort_value:
sort_ret = sort_ret.desc()
else:
sort_ret = None
# 联表
sql_result = session.query(*orm).join(*orm[1:], isouter=True).filter(
condition
)
# 0为False
if limit:
sql_content = sql_result.order_by(sort_ret).limit(limit).offset(offset)
else:
sql_content = sql_result.order_by(sort_ret)
sql_total = sql_result.count()
result = []
for i in sql_content:
try:
# 多表情况
c = {}
for a in i:
if a:
# 判断是否有返回字段
if response[a.__tablename__]:
c[a.__tablename__] = {}
for res_key in response[a.__tablename__]:
c[a.__tablename__][res_key] = getattr(a, res_key)
else:
# 使用表名作为key
c[a.__tablename__] = a.to_json()
result.append(c)
except:
# 单表情况
if response[i.__tablename__]:
c = {}
for res_key in response[i.__tablename__]:
c[res_key] = getattr(i, res_key)
result.append(c)
else:
result.append(i.to_json())
return operate_commit(session), sql_total, result
def query_method(self, session, orm):
"""
通用查询数据详情方法
:type session:
:param session: 数据库连接
:type orm: class
:param orm: model类 --该类需要有构造方法
:type reuqest: dict
:param reuqest:{
"cond": {
"name": "leas",
"des": "",
"create_time":{
"start_time":"2017-09-11 11:56:22",
"end_time":"2017-09-11 11:56:22"
}
},
"sort": {
"name": True
},
"response":["name", "des"] #需要返回的的数据
"limit": 2,
"page": 1
}
:rtype: Boolean, Int , list[dict]
:return:True or False, Int, list[dict]
"""
request = self.request
# 返回的list
response = request['response']
# 分页
limit = request['limit']
offset = (request['page'] - 1) * request['limit']
# &&条件
cond = request['cond']
sql_cond = []
for key, value in cond.items():
ret = getattr(orm, key)
# 判断是不是时间段条件
# 时间段字段key字符串必须包含'time'字符串
if isinstance(value, dict) and 'time' in key:
sql_cond.append(
ret.between(value.values()[0],
value.values()[1]))
# 当value类型为list时添加in条件
elif isinstance(value, list):
sql_cond.append(ret.in_(value))
else:
sql_cond.append(
ret.like('%' + str(value) +
'%') if value is not None else "")
condition = and_(*sql_cond)
# 排序
sort = request['sort'] # key 排序字段 True 降序 False 升序
if sort:
sort_key, sort_value = sort.items()[0]
sort_ret = getattr(orm, sort_key)
if sort_value:
sort_ret = sort_ret.desc()
else:
sort_ret = None
sql_result = session.query(orm).filter(condition)
# 0为False
if limit:
sql_content = sql_result.order_by(sort_ret).limit(limit).offset(
offset)
else:
sql_content = sql_result.order_by(sort_ret)
sql_total = sql_result.count()
# 如果不添加返回字段，返回所有
if response:
result = []
for i in sql_content:
c = {}
for res_key in response:
c[res_key] = getattr(i, res_key)
result.append(c)
else:
result = [i.to_json() for i in sql_content]
return operate_commit(session), sql_total, result