PyMySQL 的封装使用

本文记录 python 中较常用的 mysql 使用方法：使用 pymysql 库
对 pymysql 操作的封装可使其在项目中更易于管理与使用

1. 安装

pip install PyMySQL

2. pymysql 操作封装成类

import pymysql


class MySQL():
    """pymysql 的封装"""
    def __init__(self, **conn_info):
        if 'charset' not in conn_info:
            conn_info['charset'] = 'utf8mb4'
        if 'cursorclass' not in conn_info:
            conn_info['cursorclass'] = pymysql.cursors.DictCursor
        self.conn = pymysql.connect(**conn_info)

    def execute(self, sql):
        try:
            with self.conn.cursor() as cur:
                cur.execute(sql)
                self.conn.commit()
        except Exception:
            import traceback
            print(traceback.format_exc())
            # 异常后回滚
            self.conn.rollback()
        finally:
            self.conn.close()

    def select(self, sql):
        """查询"""
        with self.conn.cursor() as cur:
            cur.execute(sql)
            res = cur.fetchall()
            return res

    def insert(self, sql):
        """插入"""
        return self.execute(sql)

    def update(self, sql):
        """更新"""
        return self.execute(sql)

    def delete(self, sql):
        """删除"""
        return self.execute(sql)


if __name__ == '__main__':
    conn_info = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'passwd': '123456',
        'database': 'mysql',
    }
    mysql = MySQL(**conn_info)
    sql = 'select * from user;'
    data = mysql.select(sql)
    print(type(data))       # <class 'list'>
    for row in data:
        print(type(row))    # <class 'dict'>