import pymysql
class SKQ: “”" “”"
def __init__(self): config = { 'host': 'xx.xx.xx', # 开发版 'port': 3306, 'user': 'huajian', 'password': '666666', 'db': 'decoder_sit', 'charset': 'utf8', 'cursorclass': pymysql.cursors.DictCursor } # 创建连接 self.connection = pymysql.connect(**config) def add(self, _dict, table): # 执行sql语句 try: with self.connection.cursor() as cursor: _listIndex = list(_dict.keys()) _list = list(_dict.values()) values = ["'{}'".format(i) for i in _list] item = "{}".format(','.join(_listIndex)) value = "{}".format(','.join(values)) sql = "INSERT INTO {} ({}) VALUES({});".format(table, item, value) print(sql) cursor.execute(sql) self.connection.commit() return True except Exception as e: print(e) self.connection.rollback() def updata(self, table, values_dict, upvalues_dict): # UPDATE table_name SET field1=new-value1, field2=new-value2 # [WHERE Clause] try: with self.connection.cursor() as cursor: value = [] dict_list = values_dict.items() for i in dict_list: if i[1]: value += ["{} = '{}'".format(i[0], i[1])] upvalue = [] updict_list = upvalues_dict.items() for i in updict_list: if i[1]: upvalue += ["{} = '{}'".format(i[0], i[1])] sql = "UPDATE {} SET {} where {};".format(table, ' , '.join(upvalue), ' and '.join(value)) print(sql) cursor.execute(sql) self.connection.commit() return True except Exception as e: print(e) self.connection.rollback() def findall(self, table, item, values_dict): # 执行sql语句 try: with self.connection.cursor() as cursor: value = [] dict_list = values_dict.items() for i in dict_list: if i[1]: value += ["{} = '{}'".format(i[0], i[1])] sql = "select {} from {} where {};".format(item, table, ' and '.join(value)) # print(sql) cursor.execute(sql) # 获取查询结果 result = cursor.fetchall() # 获取全部结果，取一条为 fetchone() return result except Exception as e: print(e) self.connection.rollback() def getMAX(self, table, item): try: with self.connection.cursor() as cursor: sql = 'select max({}) from {};'.format(item, table) print(sql) cursor.execute(sql) # 获取查询结果 result = cursor.fetchall() # 获取全部结果，取一条为 fetchone() return result except Exception as e: print(e) self.connection.rollback() def dell(self, table, values_dict): # 执行sql语句 try: with self.connection.cursor() as cursor: value = [] dict_list = values_dict.items() for i in dict_list: if i[1]: value += ["{} like '{}'".format(i[0], i[1])] sql = "DELETE FROM `{}` WHERE {};".format(table, ' and '.join(value)) print(sql) cursor.execute(sql) # 获取查询结果 self.connection.commit() return True except Exception as e: print(e) self.connection.rollback()