06-PyMySQL 的封装使用

  • 阅读: 295
  • 更新: 2022-06-11

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

1. 安装

1
pip install PyMySQL

2. pymysql 操作封装成类

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
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'>

=== 全文完 ===


欢迎加入QQ群:778391413

京公网安备 11011302003970号 京ICP备2022012301号-1