Python工具类（一）—— 操作Mysql数据库
如何调用直接看__main__函数里如何调用此工具类就阔以啦！
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))
