import pymysql
class MyDBConnector:
def __init__(self, logger, host, user, password, schema, charset):
self.logger = logger
self.host = host
self.user = user
self.password = password
self.schema = schema
self.charset = charset
self.logger.info(('init with:{}|{}|{}|{}'.format(host, user, password, schema, charset)))
def __connect(self):
self.conn = pymysql.connect(host=self.host,
user=self.user,
password=self.password,
database=self.schema,
charset=self.charset)
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
self.logger.info('connected')
def __disconnect(self):
self.cursor.close()
self.conn.close()
self.logger.info('disconnected')
def query(self, sql_statement):
query_result = []
try:
self.__connect()
self.logger.info(('searching:{}'.format(sql_statement)))
self.cursor.execute(sql_statement)
query_result = self.cursor.fetchall()
self.logger.info('searched')
self.__disconnect()
except Exception as ex:
line_number = ex.__traceback__.tb_lineno
self.logger.info('[{}]:{}'.format(line_number,ex))
raise Exception(ex)
return query_result
def insert(self, sql_statement, dataset):
try:
self.__connect()
self.logger.info(('adding:{}'.format(sql_statement)))
self.cursor.executemany(sql_statement, dataset)
self.conn.commit()
self.logger.info('added')
self.__disconnect()
except Exception as ex:
self.conn.rollback()
line_number = ex.__traceback__.tb_lineno
self.logger.info('[{}]:{}'.format(line_number,ex))
raise Exception(ex)
def update(self, sql_statement):
try:
self.__connect()
self.logger.info(('updating:{}'.format(sql_statement)))
self.cursor.execute(sql_statement)
self.conn.commit()
self.logger.info('updated')
self.__disconnect()
except Exception as ex:
self.conn.rollback()
line_number = ex.__traceback__.tb_lineno
self.logger.info('[{}]:{}'.format(line_number,ex))
raise Exception(ex)
def executeAll(self, sql_statement_list):
try:
self.__connect()
for sql_statement in sql_statement_list:
self.logger.info(('prepareing:{}'.format(sql_statement)))
self.cursor.execute(sql_statement)
self.conn.commit()
self.logger.info('executed')
self.__disconnect()
except Exception as ex:
self.conn.rollback()
line_number = ex.__traceback__.tb_lineno
self.logger.info('[{}]:{}'.format(line_number,ex))
raise Exception(ex)
