from pymysql import connect from retrying import retry
class Down_Pdf(): def __init__(self): self.mysql_host = '127.0.0.1' self.mysql_port = 3306 self.mysql_user = 'root' self.mysql_password = 'xxxx' self.mysql_db = 'datadb' self.conn = connect(host=self.mysql_host, port=self.mysql_port, user=self.mysql_user,password=self.mysql_password, db=self.mysql_db, charset='utf8mb4') self.cur = self.conn.cursor() def new_link(self):#重新连接数据库 self.conn = connect(host=self.mysql_host, port=self.mysql_port, user=self.mysql_user,password=self.mysql_password, db=self.mysql_db, charset='utf8mb4') self.cur = self.conn.cursor() @retry(stop_max_attempt_number=6, wait_fixed=2000) def all_search(self, sql, data=None, to_json=False): """ :param sql:select id from table where param1=%s and param2=%s :param data:list [(1,2),(3,4)] :param to_json: 返回结果转成字典 """ try: self.conn.ping() except: self.new_link() results = [] if data: for i in data: self.cur.execute(sql % i) datas = self.cur.fetchall() self.conn.commit() if to_json: if not data: return {} columns = [i[0] for i in self.cur.description] datas = [dict(zip(columns, r)) for r in datas] results += list(datas) else: self.cur.execute(sql) datas = self.cur.fetchall() self.conn.commit() if to_json: if not datas: return {} columns = [i[0] for i in self.cur.description] datas = [dict(zip(columns, r)) for r in datas] results += list(datas) return results @retry(stop_max_attempt_number=6, wait_fixed=2000) def one_search(self, sql, to_json=False): """ :param sql: :param to_json: 返回结果转成字典 """ try: self.conn.ping() except: self.new_link() self.cur.execute(sql) data = self.cur.fetchone() if to_json: if not data: return {} columns = [i[0] for i in self.cur.description] data = dict(zip(columns, data)) self.conn.commit() # 每次查询后进行事务提交,下次查询到数据库更新后的数据 return data @retry(stop_max_attempt_number=6, wait_fixed=2000) def submit_data(self, sql): try: self.conn.ping() except: self.new_link() self.cur.execute(sql) self.conn.commit() @retry(stop_max_attempt_number=6, wait_fixed=2000) def close(self): try: self.conn.ping() except: self.new_link() self.cur.close() self.conn.close() @retry(stop_max_attempt_number=6, wait_fixed=2000) def execute_many(self, sql, data): """ :param sql: 写入/查询 :param data: [(1,2,3)] :param to_result: 返回结果 :param to_json: 返回结果转成字典 """ if not data: return try: self.conn.ping() except: self.new_link() try: self.conn.ping(reconnect=True) self.cur.executemany(sql, data) self.conn.commit() except Exception as e: print(e)