python-pymysql常用功能封装

---

简介

pymysql是一个客户端，本文进行一个简单封装，主要是结构化和批量操作

包安装

pip3 install pymysql

代码

import pymysql

class MysqlCli:
    # 初始化
    def __init__(self,host,user,password,database,port=3306,charset='utf8',use_unicode=True):
        """
        :param host:主机名
        :param user: 用户
        :param password: 密码
        :param database: 数据库
        :param port: 端口，默认3306
        :param charset: 字符集，默认utf8
        :param use_unicode: 使用unicode，默认True
        """

        self.connect = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            port=port,
            charset=charset,
            use_unicode=use_unicode
        )
        self.cursor = self.connect.cursor(pymysql.cursors.DictCursor)

    # 关闭连接
    def close(self):
        self.cursor.close()
        self.connect.close()

    # 增删改
    def operate(self,sql,param=None)->int:
        """
        :param sql: sql语句
        :param param: 元组
        :return: 受影响行数 int
        """
        try:
            self.cursor.execute(sql,param)
            self.connect.commit()
            return self.cursor.rowcount
        except Exception as e:
            self.connect.rollback()
            raise Exception(f"operate error: {e}")

    # 批量增删改
    def operate_many(self,sql,params=None)->int:
        """
        :param sql: sql语句
        :param params: params是列表，元素是元组
        :return: 受影响行数 int
        """
        try:
            self.cursor.executemany(sql,params)
            self.connect.commit()
            return self.cursor.rowcount
        except Exception as e:
            self.connect.rollback()
            raise Exception(f"operate_many error: {e}")

    # 查询（结构化）
    def select_one(self,sql,params=None)->dict:
        """
        :param sql: sql语句
        :param params: 元组
        :return: 字典
        """
        try:
          self.cursor.execute(sql,params)
          res = self.cursor.fetchone()
          self.cursor.close()
          return res
        except Exception as e:
            self.connect.rollback()
            raise Exception(f"select_one error: {e}")

    # 批量查询（结构化）
    def select_all(self,sql,params=None)->list:
        """
        :param sql: sql语句
        :param params: 元组
        :return: 列表，元素为字典
        """
        try:
          self.cursor.execute(sql,params)
          res = self.cursor.fetchall()
          self.cursor.close()
          return res
        except Exception as e:
            self.connect.rollback()
            raise Exception(f"select_all error: {e}")

    # 插入一条数据 字典与列名保持一致
    def insert_one(self,table:str,data:dict)->int:
        """
        :param table: 表名
        :param data: 数据
        :return:
        """
        columns = '`'+'`,`'.join(data.keys())+'`'
        placeholders = ', '.join(['%s'] * len(data))
        query = f"INSERT INTO `{table}` ({columns}) VALUES ({placeholders})"
        return self.operate(query, tuple(data.values()))

    # 插入多条数据
    def insert_many(self,table:str,data_list:list)->int:
        """
        :param table: 表名
        :param data_list: 列表，元素为字典
        :return:
        """
        columns = '`'+'`,`'.join(data_list[0].keys())+'`'
        placeholders = ','.join(['%s'] * len(data_list[0]))
        query = f'INSERT INTO `{table}` ({columns}) VALUES ({placeholders})'
        args = [tuple(data.values()) for data in data_list]
        return self.operate_many(query, args)

    # 更新一条数据
    def update_one(self,table:str,data:dict,condition:str):
        """
        :param table: 表名
        :param data: 数据，字典
        :param condition: where后条件
        :return:
        """
        set_clause = ', '.join([f"{key} = %s" for key in data.keys()])
        query = f"UPDATE `{table}` SET {set_clause} WHERE {condition}"
        return self.operate(query,tuple(data.values()))

测试

表结构如下：

上面代码放到了helper.py中，测试代码如下：

from helper import MysqlCli
import uuid
import unittest
my_cli = MysqlCli(host="127.0.0.1", user="root", password="12345678", database="test")

class Test(unittest.TestCase):
    def test_operate(self):
        sql = "delete from users"
        res = my_cli.operate(sql)
        print(res)
        my_cli.close()

    def test_insert_one(self):
        user = {
            "id": str(uuid.uuid4()),
            "name":"lady_killer"
        }
        res = my_cli.insert_one("users",user)
        print(res)
        my_cli.close()

    def test_insert_many(self):
        users = [
            {
                "id": str(uuid.uuid4()),
                "name": "lady_killer1"
            },
            {
                "id": str(uuid.uuid4()),
                "name": "lady_killer2"
            }
        ]
        res = my_cli.insert_many("users",users)
        print(res)
        my_cli.close()

    def test_update_one(self):
        user = {
            "name":"lady_killer9"
        }
        res = my_cli.update_one("users",user,"id='26c2a5be-4092-486c-abe8-e9cba0682729'")
        print(res)
        my_cli.close()

结果截图：

可以结合Python-logging详解（彩色日志扩展，多进程安全等），添加日志。如果是并发情况下，不能仅使用一个客户端，可以考虑连接池。

参考

github-pymysql
pymysql-docs