flask数据库连接池DBUtils

数据库连接池

为啥要使用数据库连接池

  • 频繁的连接和断开数据库,消耗大,效率低
  • DBUtils可以创建多个线程连接数据库,且一直保持连接,不会断开
  • 执行数据库操作时,由数据池分配线程,当数据池空时,可选择等待或者抛错

安装

pip3 install DBUtils

基础用法

  • 创建数据池
    ```python
    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,大大提高数据库的操作效率
上一篇:mysql之DBUtils


下一篇:java基础(30):DBUtils、连接池