采用连接池的方式来操作 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: 查询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)
- con.commit()
- except Exception as e:
- con.rollback() # 事务回滚
- print('SQL 执行有误, 原因:', e)
- finally:
- cur.close()
- con.close()
来源: http://www.bubuko.com/infodetail-3234224.html