数据库连接池
为啥要使用数据库连接池
- 频繁的连接和断开数据库,消耗大,效率低
- DBUtils可以创建多个线程连接数据库,且一直保持连接,不会断开
- 执行数据库操作时,由数据池分配线程,当数据池空时,可选择等待或者抛错
安装
pip3 install DBUtils
基础用法
-
创建数据池
```python
import time
import threadingimport pymysql
from DBUtils.PooledDB import PooledDB# 创建数据库连接池
POOL = PooledDB(
creator=pymysql,
maxconnections=20, # 定义最大连接数
mincached=2, # 定义起始连接数
host='127.0.0.1',
blocking=True, # 连接池用完时,True是等待,False时抛错
port=3306,
user='root',
password='123',
database='pooldb',
charset='utf8'
)# 去数据库连接池获取一个连接
conn = POOL.connection()cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute('select * from tb1')
result = cursor.fetchall()# 将此连接放还给连接池
conn.close()
```
在flask中应用
-
定义
```
import pymysqlfrom DBUtils.PooledDB import PooledDB
class SQLHelper(object):
def init(self):
# 创建数据库连接池
self.pool = PooledDB(
creator=pymysql,
maxconnections=5,
mincached=2,
blocking=True,
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='s23day02',
charset='utf8'
)def connect(self): conn = self.pool.connection() cursor = conn.cursor() return conn,cursor def disconnect(self,conn,cursor): cursor.close() conn.close() def fetchone(self,sql,params=None): """ 获取单条 :param sql: :param params: :return: """ if not params: params = [] conn,cursor = self.connect() cursor.execute(sql, params) result = cursor.fetchone() self.disconnect(conn,cursor) return result def fetchall(self,sql,params=None): """ 获取所有 :param sql: :param params: :return: """ import pymysql if not params: params = [] conn, cursor = self.connect() cursor.execute(sql,params) result = cursor.fetchall() self.disconnect(conn, cursor) return result def commit(self,sql,params): """ 增删改 :param sql: :param params: :return: """ import pymysql if not params: params = [] conn, cursor = self.connect() cursor.execute(sql, params) conn.commit() self.disconnect(conn, cursor)
db = SQLHelper()
``` -
使用单例模式进行
from flask import Blueprint,url_for,request,render_template,session,redirect from ..utils.sqlhelper import db # 创建了一个蓝图对象 account = Blueprint('account',__name__) @account.route('/login',methods=['GET','POST']) def login(): if request.method == 'GET': return render_template('login.html') user = request.form.get('user') pwd = request.form.get('pwd') # 根据用户名和密码去数据库进行校验 # 连接/SQL语句/关闭 result = db.fetchone('select * from user where username=%s and password=%s',[user,pwd]) if result: # 在session中存储一个值 session['user_info'] = user return redirect(url_for('user.user_list')) return render_template('login.html',error="用户名或密码错误")
【说明】SQLhelper类
- 封装了数据库操作的相关方法,以便之后业务功能调取,在一定程度上时减少了代码的重复
- 配合DBUtils,大大提高数据库的操作效率