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()