Python工具类（一）—— 操作Mysql数据库

1 # encoding=utf-8
  2 import pymysql
  3 
  4 # 导入所有Mysql配置常量,请自行指定文件
  5 from conf.settings import *
  6 
  7 
  8 class MysqlConnection(object):
  9     """
 10     mysql操作类，对mysql数据库进行增删改查
 11     """
 12 
 13     def __init__(self, config):
 14         # Connect to the database
 15         self.connection = pymysql.connect(**config)
 16         self.connection.autocommit(True)
 17         self.cursor = self.connection.cursor()
 18 
 19     def QueryAll(self, sql):
 20         """
 21         查询所有数据
 22         :param sql:
 23         :return:
 24         """
 25         # 数据库若断开即重连
 26         self.reConnect()
 27 
 28         self.cursor.execute(sql)
 29         return self.cursor.fetchall()
 30 
 31     def QueryMany(self, sql, n):
 32         """
 33         查询某几条数据数据
 34         :param sql:
 35         :return:
 36         """
 37         # 数据库若断开即重连
 38         self.reConnect()
 39 
 40         self.cursor.execute(sql)
 41         return self.cursor.fetchmany(n)
 42 
 43     def QueryOne(self, sql):
 44         """
 45         查询某几条数据数据
 46         :param sql:
 47         :return:
 48         """
 49         # 数据库若断开即重连
 50         self.reConnect()
 51 
 52         self.cursor.execute(sql)
 53         return self.cursor.fetchone()
 54 
 55     # return self.cursor.fetchone()
 56 
 57     def reConnect(self):
 58         """
 59         重连机制
 60         :return:
 61         """
 62         try:
 63             self.connection.ping()
 64         except:
 65             self.connection()
 66 
 67     def Operate(self, sql, params=None, DML=True):
 68         """
 69         数据库操作:增删改查
 70         DML: insert / update / delete
 71         DDL: CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
 72         """
 73         try:
 74             # 数据库若断开即重连
 75             self.reConnect()
 76 
 77             with self.connection.cursor() as cursor:
 78                 cursor.execute(sql, params)
 79 
 80                 self.connection.commit()
 81 
 82         except Exception as e:
 83             if DML:
 84                 # 涉及DML操作时,若抛异常需要回滚
 85                 self.connection.rollback()
 86             print(e)
 87 
 88     def __del__(self):
 89         """
 90         MysqlConnection实例对象被释放时调用此方法,用于关闭cursor和connection连接
 91         """
 92         self.cursor.close()
 93         self.connection.close()
 94 
 95 
 96 if __name__ == "__main__":
 97     # 初始化MysqlConnection实例对象需要传Mysql配置信息的字典
 98     config = {'host': MYSQL_HOST, 'charset': CHARSET, 'db': DB, 'user': USER, 'port': MYSQL_PORT, 'password': PASSWORD}
 99     msc = MysqlConnection(config)
100     sql = "delete from users where username ='%s'" % "123456"
101 
102     print(msc.Operate(sql))