#! /usr/bin/env python3#-*- coding: UTF-8 -*-
"""@Time: 2019/12/31 19:43
@Author: hengxin"""
importpymysqlimportdatetimeimportdecimalimportjsonfrom tools.Log importLogfrom tools.Config importConfigclassDataBaseOperate(object):
L= Log("DataBaseOperate")
C=Config()defoperate(self, sql):
db= pymysql.connect(host=self.C.DB_HOST,
port=self.C.DB_PORT,
user=self.C.DB_USER,
passwd=self.C.DB_PWD)
db.ping(reconnect=True)
con= db.cursor(cursor=pymysql.cursors.DictCursor)try:#此处新增 单次连接执行多条SQL的功能, 兼容书写时首尾多输入空格的情况
sql_list = sql.strip().split(";")try:#此处兼容以分号结尾的单句SQL仍返回一维列表
sql_list.remove('')exceptValueError as e:
self.L.logger.error(e)raise Exception("SQL请以分号 ; 结束")if len(sql_list) < 2:
con.execute(sql)
self.L.logger.info('\n\t' +sql)
effect_row=con.rowcountif sql.lower().startswith('select'):
self.L.logger.info('\n\t' +sql)#if effect_row != 1:
#self.L.logger.info(sql)
#else:
#pass
self.L.logger.info("影响行数 %s" %effect_row)else:passresults=con.fetchall()
db.commit()#print(results)
for result inresults:for fields inresult:ifisinstance(result[fields], datetime.datetime):
result[fields]= str(result[fields].strftime('%Y-%m-%d %H:%M:%S'))elifisinstance(result[fields], datetime.date):
result[fields]= str(result[fields].strftime('%Y-%m-%d'))elifisinstance(result[fields], decimal.Decimal):
result[fields]=float(result[fields])else:
results=[]for sql insql_list:if sql != '':
con.execute(sql)
self.L.logger.info(sql)
effect_row=con.rowcountif sql.lower().startswith('select'):
self.L.logger.info('\n\t' +sql)#if effect_row != 1:
#self.L.logger.info(sql)
#else:
#pass
else:passself.L.logger.info("影响行数 %s" %effect_row)
results.append(con.fetchall())
db.commit()else:pass
for result inresults:for r inresult:for fields inr:ifisinstance(r[fields], datetime.datetime):
r[fields]= str(r[fields].strftime('%Y-%m-%d %H:%M:%S'))elifisinstance(r[fields], datetime.date):
r[fields]= str(r[fields].strftime('%Y-%m-%d'))elifisinstance(r[fields], decimal.Decimal):
r[fields]=float(r[fields])
con.close()#if sql.lower().startswith('select'):
self.L.logger.debug("\n" + json.dumps(results, ensure_ascii=False,
sort_keys=True, indent=2, separators=(',', ':')))#else:
#pass
returnresultsexceptException as e:
db.rollback()
self.L.logger.error(e)raise KeyError(e)