pymysql封装

  1 import pymysql
  2 from configs.global_data import Mysql
  3 from pymysql.cursors import DictCursor
  4 
  5 
  6 class MysqlHandler:
  7 
  8     def __init__(self):
  9         # 创建数据库连接
 10         self.conn = pymysql.connect(**Mysql.db_config)
 11 
 12     def __del__(self):
 13         """
 14         对象销毁时自动执行关闭数据库连接
 15         :return:
 16         """
 17         self.conn.close()
 18 
 19     def fetchone(self, sql):
 20         """
 21         获取数据库一条数据
 22         :param sql: 要执行的sql语句
 23         :return:
 24         """
 25         # 创建游标
 26         with self.conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
 27             # 执行sql
 28             cursor.execute(sql)
 29             # 获取数据
 30             return cursor.fetchone()
 31 
 32     def fetchmany(self, sql):
 33         """
 34         获取数据库多条数据
 35         :param sql: 要执行的sql语句
 36         :return:
 37         """
 38         # 创建游标
 39         with self.conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
 40             # 执行sql
 41             cursor.execute(sql)
 42             # 获取数据
 43             return cursor.fetchmany()
 44 
 45     def fetchall(self, sql):
 46         """
 47         获取全部数据
 48         :param sql: 要执行的sql语句
 49         :return:
 50         """
 51         # 创建游标
 52         with self.conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
 53             # 执行sql
 54             cursor.execute(sql)
 55             # 获取数据
 56             return cursor.fetchall()
 57 
 58     def is_exist(self, sql):
 59         """
 60         是否存在数据
 61         :param sql: 要执行的sql语句
 62         :return:
 63         """
 64         # 创建游标
 65         with self.conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
 66             # 执行sql
 67             cursor.execute(sql)
 68             # 判断数据是否存在
 69             if cursor.fetchone():
 70                 return True
 71             else:
 72                 return False
 73 
 74     def insert_into(self, sql):
 75         """
 76         插入数据
 77         :param sql: 要执行的sql
 78         :return:
 79         """
 80         try:
 81             with self.conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
 82                 # 执行sql
 83                 cursor.execute(sql)
 84                 # 提交事务
 85                 self.conn.commit()
 86         except Exception as e:
 87             self.conn.rollback()
 88             raise e
 89 
 90     def update(self, sql):
 91         """
 92         更新数据
 93         :param sql: 要执行的sql
 94         :return:
 95         """
 96         try:
 97             with self.conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
 98                 # 执行sql
 99                 cursor.execute(sql)
100                 # 提交事务
101                 self.conn.commit()
102         except Exception as e:
103             self.conn.rollback()
104             raise e
105 
106     def delete(self, sql):
107         """
108         删除数据
109         :param sql: 要执行的sql
110         :return:
111         """
112         try:
113             with self.conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
114                 # 执行sql
115                 cursor.execute(sql)
116                 # 提交事务
117                 self.conn.commit()
118         except Exception as e:
119             self.conn.rollback()
120             raise e
121 
122 
123 if __name__ == '__main__':
124     db = MysqlHandler()
125     sql1 = "SELECT * FROM `user` LIMIT 10"
126     res = db.fetchone(sql1)
127     print(res)