pymysql连接池操作MySQL

pymysql连接池操作MySQL

• 安装

  pip install PyMySQL
  pip install DBUtils
  

• 上代码

  #!/usr/bin/python3
  # -*- coding:utf-8 -*-
  # @Author         : Charlie Zhang
  # @Email          : charlie.zhang@wiwide.com
  # @Time           : 2021/4/7 18:12
  # @Version        : 1.0
  # @File           : db.py
  # @Software       : PyCharm
  
  import logging
  import pymysql
  from dbutils.pooled_db import PooledDB
  from conf import config
  
  logger = logging.getLogger('wabasic')
  
  
  class MysqlPool:
      def __init__(self):
          self.POOL = PooledDB(
              creator=pymysql,  # 使用链接数据库的模块
              maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
              mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
              maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
              # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
              maxshared=3,
              blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
              maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
              setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
              # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
              ping=0,
              host=config.MYSQL_HOST,
              port=config.MYSQL_PORT,
              user=config.MYSQL_USER,
              password=config.MYSQL_PASSWORD,
              database=config.MYSQL_DATABASE,
              charset='utf8'
          )
  
      def __new__(cls, *args, **kw):
          """
          启用单例模式
          :param args:
          :param kw:
          :return:
          """
          if not hasattr(cls, '_instance'):
              cls._instance = object.__new__(cls)
          return cls._instance
  
      def connect(self):
          """
          启动连接
          :return:
          """
          try:
              conn = self.POOL.connection()
              cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
              return conn, cursor
          except Exception as e:
              logger.error(f'mysql connect error: {e}')
  
      @staticmethod
      def connect_close(conn, cursor):
          """
          关闭连接
          :param conn:
          :param cursor:
          :return:
          """
          try:
              cursor.close()
              conn.close()
          except Exception as e:
              logger.error(f'mysql close error: {e}')
  
      def fetch_all(self, sql, args=None):
          """
          批量查询
          :param sql:
          :param args:
          :return:
          """
          try:
              conn, cursor = self.connect()
              cursor.execute(sql, args)
              record_list = cursor.fetchall()
              self.connect_close(conn, cursor)
  
              return record_list
          except Exception as e:
              logger.error(f'fetchall error: {e}')
  
      def fetch_one(self, sql, args=None):
          """
          查询单条数据
          :param sql:
          :param args:
          :return:
          """
          try:
              conn, cursor = self.connect()
              cursor.execute(sql, args)
              result = cursor.fetchone()
              self.connect_close(conn, cursor)
              return result
          except Exception as e:
              logger.error(f'fetchone error: {e}')
  
      def insert(self, sql, args=None):
          """
          插入数据
          :param sql:
          :param args:
          :return:
          """
          conn, cursor = self.connect()
          try:
              row = cursor.execute(sql, args)
              conn.commit()
          except Exception as e:
              logger.error(f'insert error: {e}')
              conn.rollback()
              row = {}
          self.connect_close(conn, cursor)
          return row
  
      # 增加多行
      def insertmany(self, sql, param):
          """
          :param sql:
          :param param: 必须是元组或列表[(),()]或（（），（））
          :return:
          """
          conn, cursor = self.connect()
          try:
              cursor.executemany(sql, param)
              conn.commit()
          except Exception as e:
              logger.error(f'insertmany error: {e}')
              conn.rollback()
          self.connect_close(cursor, conn)
  
      # 删除
      def delete(self, sql, param=None):
          conn, cursor = self.connect()
          try:
              cursor.execute(sql, param)
          except Exception as e:
              logger.error(f'delete error: {e}')
              conn.rollback()
          self.connect_close(cursor, conn)
  
      # 更新
      def update(self, sql, param=None):
          conn, cursor = self.connect()
          try:
              cursor.execute(sql, param)
              conn.commit()
          except Exception as e:
              logger.error(f'update error: {e}')
              conn.rollback()
          self.connect_close(cursor, conn)
  
  
  if __name__ == '__main__':
      # 实例化
      mp = MysqlPool()
      """
      查询单条
      """
      values = [('浙江省公众信息产业有限公司无线运营分公司', '311267618', '浙江省杭州市拱墅区莫干山路118号', '黄寅义', '18057198713', '18057198713@189.cn')]
      data = mp.fetch_all("SELECT bn.netbar_wacode FROM area_province AS ap LEFT JOIN wa_basic_fj_0001 AS wbf ON ap.id = wbf.province_id LEFT JOIN base_netbarwacode AS bn ON wbf.id = bn.place_id WHERE ap.`code` = %s;", ('460000', ))
      # data = mp.fetch_one("select province_id, id from area_city where code = %s", ('460100', ))
      print(data)
      # """
      # 批量读取
      # """
      # data = mp.fetch_all("select * from record where user_id=%s", (1,))
      # print(data)
      # """
      # 插入数据
      # """
      #
      # data = mp.insert("insert into record(line,ctime,user_id)values(%s,%s,%s)", (22, '2019-11-11', 1))
      # print(data)
  
      # 增加多条
      # sql3 = 'insert into userinfo (name,password) VALUES (%s,%s)'
      # li = li = [
      #     ('分省', '123'),
      #     ('到达','456')
      # ]
      # ret = mp.insertmany(sql3,li)
      # print(ret)
      # 删除
      # sql4 = 'delete from  userinfo WHERE name=%s'
      # args = 'xxxx'
      # ret = mp.delete(sql4, args)
      # print(ret)
      # 更新
      # sql5 = r'update userinfo set password=%s WHERE name LIKE %s'
      # args = ('993333993', '%old%')
      # ret = mp.update(sql5, args)
      # print(ret)