数据库连接池
为啥要使用数据库连接池
- 频繁的连接和断开数据库,消耗大,效率低
- DBUtils可以创建多个线程连接数据库,且一直保持连接,不会断开
- 执行数据库操作时,由数据池分配线程,当数据池空时,可选择等待或者抛错
安装
pip3 install DBUtils
基础用法
- 创建数据池
import time import threading import 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 pymysql from 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,大大提高数据库的操作效率