采用连接池的方式来操作DB
#-*- coding:utf-8 -*-
#!/usr/bin/python3
import pymysql
import configUtil
from DBUtils.PooledDB import PooledDB
class MysqlUtil(object):
# 连接池对象
__pool = None
def __init__(self, config):
# 数据库构造函数，从连接池中取出连接，并生成操作游标
self.pool = MysqlUtil.__get_conn(config)
@staticmethod
def __get_conn(config):
"""
@summary: 静态方法，从连接池中取出连接
@return MySQLdb.connection
"""
host = configUtil.read_config(config, "datasource_url", "mysqlConfig")
username = configUtil.read_config(config, "datasource_username", "mysqlConfig")
db_pwd = configUtil.read_config(config, "datasource_password", "mysqlConfig")
db_database = configUtil.read_config(config, "datasource_database", "mysqlConfig")
if MysqlUtil.__pool is None:
__pool = PooledDB(pymysql, mincached=1, maxcached=10, maxconnections=10,
host=host, port=3306, user=username, passwd=db_pwd,
db=db_database, use_unicode=False, blocking=False, charset="utf8")
return __pool
def get_all(self, sql):
"""
@summary: 执行查询，并取出所有结果集
@param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
@param param: 可选参数，条件列表值(元组/列表)
@return: result list(字典对象)/boolean 查询到的结果集
"""
try:
con = self.pool.connection()
cur = con.cursor()
count = cur.execute(sql)
if count > 0:
result = cur.fetchall()
else:
result = False
return result
except Exception as e:
print('SQL执行有误,原因:', e)
finally:
cur.close()
con.close()
def update(self, sql):
try:
con = self.pool.connection()
cur = con.cursor()
cur.execute(sql)
mit()
except Exception as e:
con.rollback() # 事务回滚
print('SQL执行有误,原因:', e)
finally:
cur.close()
con.close()
