class MysqlPool:
def __init__(self, config_template=config_templates, *args, **kwargs):
self.config = {
'creator': pymysql,
'host': config_template['MYSQL']['HOST'],
'port': config_template['MYSQL']['PORT'],
'user': config_template['MYSQL']['USER'],
'password': config_template['MYSQL']['PASSWD'],
'db': config_template['MYSQL']['DB'],
'charset': config_template['MYSQL']['CHARSET'],
'maxconnections': 70,
'cursorclass': pymysql.cursors.DictCursor
}
self.pool = PooledDB(**self.config)
def __enter__(self):
try:
self.conn = self.pool.connection()
self.cursor = self.conn.cursor()
except:
raise ValueError('mysql connect error {0}'.format(self.host))
return self
# 释放资源
def __del__(self):
self.cursor.close()
self.conn.close()
def __exit__(self, exc_type, exc_val, exc_tb):
self.cursor.close()
self.conn.close()
# 查询
def query(self, sql):
try:
with self:
self.cursor.execute(sql)
res = self.cursor.fetchall()
except Exception as e:
logging.error("error", e)
raise e
return res
def change(self, sql):
resnum = 0
try:
with self:
resnum = self.cursor.execute(sql)
self.conn.commit()
except Exception as e:
# 错误回滚
logging.error("error", e)
self.conn.rollback()
return resnum
class Database:
"""mysql database connection."""
def __init__(self):
# read from config file
self.host = config.get('DATABASE', 'MYSQL_HOST')
self.port = int(config.get('DATABASE', 'MYSQL_PORT'))
self.user = config.get('DATABASE', 'MYSQL_USER')
self.passwd = config.get('DATABASE', 'MYSQL_PASSWD')
self.db = config.get('DATABASE', 'MYSQL_DB')
# mysql database initialize
'''
self._db = pymysql.connect(host=self.host,
port=self.port,
user=self.user,
passwd=self.passwd,
db=self.db,
charset="utf8",
autocommit=True)
'''
# init self.pool
self.pool = PooledDB(pymysql, db=self.db, host=self.host, user=self.user, passwd=self.passwd, port=self.port,
charset="utf8", use_unicode=True, autocommit=True)
self.conn = self.pool.connection(shareable=True)
def __del__(self):
self.conn.close()
def cursor(self):
# return self._db.cursor()
cursor = self.conn.cursor()
return cursor
def update(self, sql):
self.cursor = self.conn.cursor()
try:
self.cursor.execute(sql)
except Exception:
logging.error("Update Exception Occurred", Exception)
logging.error("ERROR:", sql)
self.conn.rollback()
finally:
self.cursor.close()
def insert(self, sql):
self.cursor = self.conn.cursor()
try:
self.cursor.execute(sql)
except Exception:
logging.error("Insert Exception Occurred", Exception)
logging.error("ERROR:", sql)
self.conn.rollback()
finally:
self.cursor.close()
def selectone(self, sql):
self.cursor = self.conn.cursor()
try:
self.cursor.execute(sql)
except Exception:
logging.error("Select Exception Occurred", Exception)
finally:
result = self.cursor.fetchone()
self.cursor.close()
return result
def selectall(self, sql):
self.cursor = self.conn.cursor()
try:
self.cursor.execute(sql)
except Exception:
logging.error("Select Exception Occurred", Exception)
finally:
results = self.cursor.fetchall()
self.cursor.close()
return results
def delete(self, sql):
self.cursor = self.conn.cursor()
try:
self.cursor.execute(sql)
except Exception:
logging.error("Delete Exception Occurred", Exception)
self.conn.rollback()
finally:
self.cursor.close()