def db_insert(values):
"""
数据库插入操作
:param values:
:return:
"""
returnCode = 0
errorMessage = "操作成功"
session = SESSION()
status = 1 # 1成功 2失败
try:
t_ems_equipment = Equipment()
t_failing_sn = FailingSn()
if len(values['sn']) != 14:
returnCode = 501400
errorMessage = "sn参数不合法"
fail_sn = session.query(FailingSn).filter(
FailingSn.sn == values['sn']).all()
if len(fail_sn) == 0:
values['status'] = 2
t_failing_sn.insert(values)
return
sn_obj = session.query(Equipment).filter(
Equipment.sn == values['sn']).all()
if len(sn_obj) == 0:
data_result = t_ems_equipment.insert(values)
if data_result["errorcode"] == -1:
returnCode = 501100
errorMessage = "操作数据库错误"
RUNLOG.info("操作数据库错误")
status = 2
RUNLOG.info("sn:%s新增失败" % values['sn'])
# 插入数据库失败的数据，先查看是不是已经存在设备正常表中了，如果没有，请插入到设备异常表
if len(sn_obj) == 0:
values['status'] = 2
t_failing_sn.insert(values)
else:
# 对于新增成功的设备，先处理设备异常表中是否存在此设备号并删除
session.query(FailingSn).filter(
FailingSn.sn == values['sn']).delete(
synchronize_session=False)
session.flush()
session.commit()
except BaseException:
session.rollback()
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
session.close()
return returnCode, errorMessage, status
def join_ex(self, join_data, argc, *args, **kwargs):
"""......"""
result = []
session = SESSION()
try:
ins = []
if isinstance(args, tuple):
for arg in args:
ins.append(getattr(self.model, arg))
else:
logging.error("join of args is not tuple!")
return buffer
if isinstance(argc, list):
for darg in argc:
ins.append(getattr(join_data[0], darg))
instance = session.query(*ins).join(join_data[0],
join_data[1] == join_data[2])
for item in self._apply_kwargs(instance, **kwargs).all():
row = {}
for key in item.keys():
row[key] = item.__getattribute__(key)
result.append(row)
except BaseException:
logging.error(traceback.format_exc())
session.close()
return result
def db_insert(values):
"""
数据库插入操作
:param values:
:return:
"""
returnCode = 0
errorMessage = "操作成功"
session = SESSION()
try:
t_ems_equipment = Equipment()
if len(values['sn']) >= 16:
RUNLOG.info("sn:%s长度大于16！！！" % values['sn'])
failing_insert(values)
va_sn = {'sn': values['sn']}
data_ex = t_ems_equipment.all(**va_sn)
if len(data_ex) != 0:
returnCode = 501002
errorMessage = "数据库已存在此数据"
return
data_result = t_ems_equipment.insert(values)
if data_result["errorcode"] == -1:
returnCode = 501100
errorMessage = "操作数据库错误"
RUNLOG.info("操作数据库错误")
RUNLOG.info("sn:%s新增失败" % values['sn'])
if "way" in values.keys():
failing_insert(values)
else:
# RUNLOG.info("sn:%s新增成功之后，删除表t_failing_sn中的这条sn记录" % values['sn'])
session.query(FailingSn).filter(FailingSn.sn == values['sn']).delete(synchronize_session=False)
session.flush()
session.commit()
except BaseException:
session.rollback()
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
session.close()
return returnCode, errorMessage
def count(self, table_column, **kwargs):
number = 0
session = SESSION()
try:
instance = session.query(
func.count(getattr(self.model, table_column)))
number = self._apply_kwargs(instance, **kwargs).scalar()
except:
logging.error(traceback.format_exc())
finally:
session.close()
return number
def one(self, **kwargs):
"""查询一个值"""
result = []
session = SESSION()
try:
instance = session.query(self.model)
result_class = self._apply_kwargs(instance, **kwargs).one()
result.append(self.to_dict(result_class.__dict__))
except BaseException:
logging.error(traceback.format_exc())
session.close()
return result
def all(self, **kwargs):
"""查询所有"""
result = []
session = SESSION()
try:
instance = session.query(self.model)
for row in self._apply_kwargs(instance, **kwargs).all():
result.append(self.to_dict(row.__dict__))
except BaseException:
logging.error(traceback.format_exc())
finally:
print 2
session.close()
return result
def update(self, values, **kwargs):
"""更新"""
session = SESSION()
instance = session.query(self.model)
number = 0
try:
number = self._apply_kwargs(instance, **kwargs).update(values)
for row in self._apply_kwargs(instance, **kwargs).all():
if hasattr(row, 'update_time'):
setattr(row, 'update_time', datetime.datetime.now())
session.flush()
session.commit()
except BaseException:
session.rollback()
logging.error(traceback.format_exc())
session.close()
return number
def get(self, *args):
"""
query based on primary_keys
"""
result = []
session = SESSION()
try:
instance = session.query(self.model)
if isinstance(args, tuple):
for arg in args:
result.append(self.to_dict(instance.get(arg).__dict__))
else:
result.append(self.to_dict(instance.get(args).__dict__))
except BaseException:
logging.error(traceback.format_exc())
session.close()
return result
def delete(self, **kwargs):
"""删除"""
session = SESSION()
instance = session.query(self.model)
print "instance::", instance
print '**kwargs::::', kwargs
number = 0
try:
number = self._apply_kwargs(
instance, **kwargs).delete(synchronize_session=False)
session.flush()
session.commit()
except BaseException:
session.rollback()
logging.error(traceback.format_exc())
session.close()
return number
def max(self, table_column, **kwargs):
"""最大值"""
session = SESSION()
instance = session.query(func.max(getattr(self.model, table_column)))
session.close()
return self._apply_kwargs(instance, **kwargs).scalar()