python databaseoperate_python操作数据库

#! /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)