第02篇 PyMySQL数据库单连接实战应用

PyMySQL进阶目标

在项目中我们使用PyMySQL库连接并操作数据库时，需要开发对PyMySQL进行二次封装成公共组件。
该公共组件主要是对数据的增删改查以及回滚等操作，这样避免重复造轮子，也避免了代码的混乱。

PyMySQL组件封装

# -*- coding: utf-8 -*-
import pymysql


class MySQLClient:
    def __init__(self, host='localhost', port=3306, user='root', password='*', database='test', charset='utf8'):
        """功能：初始化数据配置信息"""
        self.conn = pymysql.connect(
            host=host, port=port, user=user, passwd=password, database=database, charset=charset)

    def select(self, sql_str, fmt_args=None, data_type=pymysql.cursors.Cursor):
        """功能：查询数据，返回（数据列表，字段列表，数据长度）"""
        cursor = self.conn.cursor(cursor=data_type)
        try:
            effect_count = cursor.execute(query=sql_str, args=fmt_args)
            data_lines = list(cursor.fetchall())
            return data_lines, [column[0] for column in cursor.description], effect_count
        except Exception as error:
            raise error
        finally:
            cursor.close()

    def insert(self, sql_str, insert_lines=None, commit=True):
        """功能：插入数据, 返回（受影响的行数，执行插入后最大行ID）"""
        cursor = self.conn.cursor()
        try:
            effect_count = cursor.executemany(query=sql_str, args=insert_lines)
            if commit:
                self.commit()
            return effect_count, cursor.lastrowid
        except Exception as error:
            self.conn.rollback()
            raise error
        finally:
            cursor.close()

    def execute(self, sql_str, fmt_args=None, commit=True):
        """功能：删除数据或修改数据，返回（受影响的行数）"""
        cursor = self.conn.cursor()
        try:
            count = cursor.execute(sql_str, args=fmt_args)
            if commit:
                self.commit()
            return count
        except Exception as error:
            self.conn.rollback()
            raise error
        finally:
            cursor.close()

    def commit(self):
        """功能：提交数据到数据库"""
        self.conn.commit()

    def close(self):
        """功能：关闭数据库连接"""
        self.conn.close()

    def __enter__(self):
        """功能：返回给as后变量"""
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """功能：断开数据库连接"""
        self.close()