def __init__(self):
try:
self.__pool = PooledDB(
creator=pymysql, # 使用链接数据库的模块
maxconnections=3, # 连接池允许的最大连接数，0和None表示不限制连接数
mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
maxcached=1, # 链接池中最多闲置的链接，0和None不限制
maxshared=1, # 链接池中最多共享的链接数量，0和None表示全部共享
blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制
setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
ping=0,
# ping MySQL服务端，检查是否服务可用。
# 如：0 = None = never,
# 1 = default = whenever it is requested,
# 2 = when a cursor is created,
# 4 = when a query is executed,
# 7 = always
host=host,
port=port,
user=user,
password=pwd,
database=db,
charset='utf8'
)
info = f'Success connect to Mysql: {host}, user: {user}, db: {db}'
logger.info(info)
MysqlPool.pool = self.__pool
except Exception as e:
logger.error(f'Failed to connect to Mysql: {host} db: {db}')
logger.error(e)
return
def execute_sql(self, sql):
if None != self.pool:
with self.pool.connection() as db:
with db.cursor() as cur:
try:
cur.execute(sql)
db.commit()
logger.debug(f'[success execute]: {sql}')
except Exception as e:
logger.error(sql)
logger.error(e)
def query_sql(self, sql):
if None != self.pool:
with self.pool.connection() as db:
with db.cursor() as cur:
try:
cur.execute(sql)
res = cur.fetchall()
logger.debug(f'[success execute]: {sql}')
for row in res:
logger.info(row)
except Exception as e:
logger.error(sql)
logger.error(e)
def db_connect(self, ):
self._connection = pymysql.connect(host=self.host,
port=self.port,
user=self.user,
password=self.password,
database=self.db,
charset='utf8')
if not self._connection:
logger.error(
f'Failed to connect to Mysql: {self.host} db: {self.db}')
raise NameError
else:
# holder_pwd = '*'*len(self.password)
info = f'Success connect to Mysql: {self.host}, user: {self.user}, db: {self.db}'
logger.info(info)
self.connection = self._connection
def execut_sql(self, sql_statement):
if self._connection:
logger.info(f'Execute sql: {sql_statement}')
else:
logger.error(f'No db connection for execute sql : {sql_statement}')
return
cur = self.connection.cursor()
try:
cur.execute(sql_statement)
rows = cur.fetchall()
self.connection.commit()
for row in rows:
str_row = str(row)
logger.info(str_row)
except Exception:
self.connection.rollback()
logger.error('Execute sql error.')
raise
finally:
cur.close()