python mysql连接池

话不多说，直接撸代码
  1 # coding=utf-8
  2 from DBUtils.PooledDB import PooledDB
  3 import pymysql as mysql
  4 import traceback
  5 import logging
  6 import logging.handlers
  7 
  8 
  9 class DB(object):
 10     def __init__(self):
 11         self.host = db_host
 12         self.name = db_name
 13         self.user = db_user
 14         self.passwd = db_passwd
 15         self.pool = PooledDB(mysql, mincached=4, maxcached=10, host=self.host, db=self.name, user=self.user,
 16                              passwd=self.passwd, setsession=['SET AUTOCOMMIT = 1'])
 17 
 18     # 连接数据库
 19     def connect_db(self):
 20         self.db = self.pool.connection()
 21         self.cur = self.db.cursor()
 22 
 23     # 关闭连接
 24     def close_db(self):
 25         self.cur.close()
 26         self.db.close()
 27 
 28     # 执行sql
 29     def execute(self, sql):
 30         self.connect_db()
 31         return self.cur.execute(sql)
 32 
 33     # 获取所有数据列表
 34     def get_list(self, table, fields):
 35         sql = "select %s from %s" % (",".join(fields), table)
 36         try:
 37             self.execute(sql)
 38             result = self.cur.fetchall()
 39             if result:
 40                 result = [dict((k, row[i]) for i, k in enumerate(fields)) for row in result]
 41             else:
 42                 result = {}
 43             return result
 44         except:
 45             self.WriteLog('db').info("Execute '%s' error: %s" % (sql, traceback.format_exc()))
 46         finally:
 47             self.close_db()
 48 
 49     # 获取某一条数据，返回字典
 50     def get_one(self, table, fields, where):
 51         conditions = []
 52         if isinstance(where, dict) and where:
 53             for k, v in where.items():
 54                 conditions.append("%s='%s'" % (k, v))
 55         sql = "select %s from %s where %s" % (",".join(fields), table, ' AND '.join(conditions))
 56         try:
 57             self.execute(sql)
 58             result = self.cur.fetchone()
 59             if result:
 60                 result = dict((k, result[i]) for i, k in enumerate(fields))
 61             else:
 62                 result = {}
 63             return result
 64         except:
 65             self.WriteLog('db').info("Execute '%s' error: %s" % (sql, traceback.format_exc()))
 66         finally:
 67             self.close_db()
 68 
 69     # 更新数据
 70     def update(self, table, fields):
 71         data = ",".join(["%s='%s'" % (k, v) for k, v in fields.items()])
 72         sql = "update %s set %s where id=%s " % (table, data, fields["id"])
 73         try:
 74             return self.execute(sql)
 75         except:
 76             self.WriteLog('db').info("Execute '%s' error: %s" % (sql, traceback.format_exc()))
 77         finally:
 78             self.close_db()
 79 
 80     # 添加数据
 81     def create(self, table, data):
 82         fields, values = [], []
 83         for k, v in data.items():
 84             fields.append(k)
 85             values.append("'%s'" % v)
 86         sql = "insert into %s (%s) values (%s)" % (table, ",".join(fields), ",".join(values))
 87         try:
 88             return self.execute(sql)
 89         except:
 90             self.WriteLog('db').info("Execute '%s' error: %s" % (sql, traceback.format_exc()))
 91         finally:
 92             self.close_db()
 93 
 94     # 删除数据
 95     def delete(self, table, where):
 96         conditions = []
 97         if isinstance(where, dict) and where:
 98             for k, v in where.items():
 99                 conditions.append("%s='%s'" % (k, v))
100         sql = "delete from %s where %s" % (table, ' AND '.join(conditions))
101         try:
102             return self.execute(sql)
103         except:
104             self.WriteLog('db').info("Execute '%s' error: %s" % (sql, traceback.format_exc()))
105         finally:
106             self.close_db()
107 
108     def WriteLog(self, log_name):
109         log_filename = "/db.log"
110         log_level = logging.DEBUG
111         format = logging.Formatter('%(asctime)s %(filename)s [line:%(lineno)2d]-%(funcName)s %(levelname)s %(message)s')
112         handler = logging.handlers.RotatingFileHandler(log_filename, mode='a', maxBytes=10 * 1024 * 1024, backupCount=5)
113         handler.setFormatter(format)
114         logger = logging.getLogger(log_name)
115         logger.addHandler(handler)
116         logger.setLevel(log_level)
117         return logger  # 函数最终将实例化的logger对象返回，后面直接调用即可
View Code