django中使用mysql连接池

连接池代码如下，并加入了一些sql生成的方便，便于编码：

#coding: utf-8
#author: alienliang
#date  : 2019年12月26日

import traceback, pymysql
from DBUtils.PooledDB import PooledDB

class MysqlPool:
    config = {
        'creator': pymysql,
        'host':'localhost',
        'port': 3306,
        'user': 'root',
        'password': '',
        'db': 'test',
        'charset': 'utf8',
        'maxconnections': 70, # 连接池最大连接数量
        'cursorclass': pymysql.cursors.DictCursor,
        'autocommit': True
    }
    pool = PooledDB(**config)
    print('mysql连接池创建成功')
 
    def __enter__(self):
        print('开启数据库连接')
        if not getattr(self, 'conn', None) or not getattr(MysqlPool, 'cursor', None):
            self.conn = MysqlPool.pool.connection()
            self.cursor = self.conn.cursor()
        return self
 
    def __exit__(self, type, value, trace):
        print('关闭数据库连接')
        self.cursor.close()
        self.conn.close()
    
    def __repr__(self):
        return '<{0.__module__}.{0.__name__} (sql="{1}")>'.format(
            type(self),  hasattr(self, 'sql') and self.sql or '')
        
    def execute(self):
        '''执行sql
        '''
        print('执行: %s' % self.sql)
        want_ret = self.sql.startswith('select') or False
        
        ret_list = []
        try:
            update_num = self.cursor.execute(self.sql)
            if want_ret: ret_list = self.cursor.fetchall()
        except BaseException:
            logger.error('执行sql失败: %s' % traceback.format_exc())
            update_num = -1
            
        print('受影响的行数: %s' % update_num)
        
        if want_ret:
            return ret_list
        else:
            return update_num
    
    def select(self, schema):
        '''生成select语句
        schema: 指定数据库和表，如db.tablename
        '''
        self.sql = 'select * from %s' % schema
        return self
    
    def delete(self, schema):
        '''生成delete语句
        schema: 指定数据库和表，如db.tablename
        '''
        self.sql = 'delete from %s' % schema
        return self
        
    def update(self, schema):
        '''生成update语句
        schema: 指定数据库和表，如db.tablename
        '''
        self.sql = 'update %s set' % schema
        return self
            
    def insert(self, schema):
        '''生成insert语句
        schema: 指定数据库和表，如db.tablename
        '''
        self.sql = 'insert into %s set' % schema
        return self
    
    def deal_with_str(self, value):
        '''处理mysql自带方法
        '''
        if isinstance(value, str):
            if 'method:' in value:
                value = value.replace('method:', '')
            else:
                value = "'%s'" % value
        return value
    
    def get_sql(self):
        return self.sql
        
    def all(self):
        return self
    
    def first(self):
        self.sql += ' limit 1'
        return self
    
    def orderby(self, field, desc=False):
        self.sql += ' order by %s%s' % (field, desc and ' desc' or '')
        return self
    
    def groupby(self, field, desc=False):
        self.sql += ' group by %s%s' % (field, desc and ' desc' or '')
        return self
    
    def filter(self, condtions='and', **kwargs):
        '''根据条件筛选
        condtions: 多个条件时使用and/or
        kwargs: 要筛选的字段（更多查询方式可按需添加）
            id=3: id=3
            id__gt=3: id>3
            id__gte=3: id>=3
            name__like='%test%': name like '%test%'
            id__in=(1, 2, 3): id in (1, 2, 3)
        '''
        filter_list = []
        for key, value in kwargs.items():
            value = self.deal_with_str(value)
            filter_list.append(self.get_condtion(key, value))
        
        condtions = ' %s ' % condtions
        filter_sql = condtions.join(filter_list)
        self.sql += ' where %s' % filter_sql
        
        return self
        
    def get_condtion(self, key, value):
        keys = key.split('__')
        if len(keys) != 2:
            return '%s=%s' % (key, value)
        
        field = keys[0]
        condtion = keys[1]
        if 'gt' == condtion:
            condtion_str = '%s>%s' % (field, value)
        elif 'gte' ==  condtion:
            condtion_str = '%s>=%s' % (field, value)
        elif 'lt' == condtion:
            condtion_str = '%s<%s' % (field, value)
        elif 'lte' == condtion:
            condtion_str = '%s<=%s' % (field, value)
        elif 'like' == condtion:
            condtion_str = '%s like %s' % (field, value)
        elif 'in' == condtion:
            if not isinstance(value, tuple):
                raise TypeError('argument "__in" must a tuple')
            if len(value) != 2:
                raise TypeError('argument "__in" tuple length must be 2')
            value1 = self.deal_with_str(value[0])
            value2 = self.deal_with_str(value[1])
            condtion_str = '%s in (%s, %s)' % (field, value1, value2)
        elif 'between' == condtion:
            if not isinstance(value, tuple):
                raise TypeError('argument "__between" must a tuple')
            if len(value) != 2:
                raise TypeError('argument "__between" tuple length must be 2')
            value1 = self.deal_with_str(value[0])
            value2 = self.deal_with_str(value[1])
            condtion_str = '%s between %s and %s' % (field, value1, value2)
        else:
            raise TypeError('got an unexpected keyword argument "%s"' % key)
        
        return condtion_str
            
    def set_field(self, **kwargs):
        '''设置字段的值
        '''
        fields = []
        for key, value in kwargs.items():
            value = self.deal_with_str(value)  
            fields.append('%s=%s' % (key, value))
        
        fields_sql = ', '.join(fields)
        self.sql += " " + fields_sql
        
        return self
        
def db_conn(func):
    def wrapper(request, *args, **kw):
        with  MysqlPool() as db:
            ret = func(db, request, *args, **kw)
        return ret
    
    return wrapper

 

 

用法如下：

#coding: utf-8
#author: liangshiqiang
#date  : 2019年12月26日

import mysql_pool

@mysql_pool.db_conn #该方法需调用连接池
def get_username(db, request):
    #查询用户表所有数据
    ret_list = db.select('account').all().execute()
    #查询用户表id=1的所有数据
    ret_list = db.select('account').filter(id=1).execute()
    #查询用户表id>10的所有数据
    ret_list = db.select('account').filter(id__gt=10).execute()
    #更新用户表id=1的用户名称
    db.update('account').set(name='newname').filter(id=1).execute()
    #删除用户表id=1的数据
    db.delete('account').filter(id=1).execute()
    #插入用户表记录
    db.insert('account').set(id=3, name='name3').execute()
    #需要用到mysql自带方法的，比如要使用now()方法
    db.insert('account').set(id=3, name='name3', update_time='method:now()').execute()