import sys import os from queue import Queue import multiprocessing path = sys.executable module_path = os.path.join(os.path.dirname(path), 'libs') sys.path.append(module_path) # print(sys.path) import time import threading import traceback from asyncio import Lock import aiomysql import asyncio from excel_handle import excel_handle from openpyxl.styles import Alignment from log import soc_log class AioMysqlExecutor: alignment_center = Alignment(horizontal='center', vertical='center') def __init__(self, loop): self.loop = loop self.pool = None self.cur = None self.conn = None self.data = None self.excel_controller = excel_handle self.row = 2 self.write_result = False self.q = Queue(200) self.count = 0 async def sleep_sleep(self): await asyncio.sleep(1) print('sleep') async def _connect(self): print('---------20--------', self.pool) if not self.pool: print('创建连接池') self.pool = await aiomysql.create_pool(maxsize=20, host='127.0.0.1', port=3306, user='root', password='root', db='dvpcloud', loop=self.loop) async def execute_sql(self, sql, i=0): if not sql: return async with self.pool.acquire() as conn: async with conn.cursor() as cur: await cur.execute(sql) self.data = await cur.fetchall() print('===========55==================') soc_log.info('===========55==================') self.q.put(self.data) await asyncio.sleep(1) self.count += 1 async def close(self): self.pool.close() await self.pool.wait_closed() def write_data(self, data=None): if not self.write_result: self.write_result = True self.excel_controller.sheet.cell(1, 4, 'result') # data = self.q.get() for d in data: self.excel_controller.write_data(self.row, 1, str(d[2])) self.excel_controller.write_data(self.row, 2, str(d[0])) self.excel_controller.write_data(self.row, 3, str(d[1])) self.excel_controller.sheet.cell(self.row, 3).alignment = self.alignment_center self.excel_controller.write_data(self.row, 4, str(d[3])) self.row += 1 def main(): loop.create_task(exe._connect()) t1 = threading.Thread(target=loop.run_forever) t1.start() time.sleep(1) # asyncio.run_coroutine_threadsafe(exe._connect(), loop=loop) # while True: # time.sleep(2) # for i in range(10): # asyncio.run_coroutine_threadsafe(exe.execute_sql(sql, i), loop=loop) # time.sleep(5) loop = asyncio.new_event_loop() asyncio.set_event_loop(loop) exe = AioMysqlExecutor(loop) main() def main_get_temperature(): try: sql = "select r3_name, return_data, test_time, result from t_r3_result " \ "where (r3_name = 'Soc温度最大值' or r3_name = 'Soc_max temperature') and result = 'Pass' limit {},{};" sql_list = [] for i in range(20): sql_list.append(sql.format(i * 100 * 1000, (i+1) * 100 * 1000)) tasks = [] for sql in sql_list: tasks.append(asyncio.run_coroutine_threadsafe(exe.execute_sql(sql), loop=loop)) time.sleep(1) start = time.time() while True: tasks = len(asyncio.all_tasks(loop=loop)) if tasks == 0: break if exe.q.empty(): time.sleep(0.1) continue # data = exe.q.get_nowait() start1 = time.time() # soc_log.info('{}{}'.format(data, '124===============')) end1 = time.time() print('写log:', end1 - start1) exe.write_data() exe.excel_controller.save_work_book() end = time.time() print(end - start) print(sys.executable) except: print(traceback.format_exc()) if __name__ == '__main__': start2 = time.time() main_get_temperature() end2 = time.time() print('总耗时:', end2 - start2) pid = multiprocessing.current_process().ident os.popen('taskkill /f /pid {}'.format(pid)) ''' 2023-09-13 16:38:35,168 : Task was destroyed but it is pending! task: <Task pending name='Task-19' coro=<AioMysqlExecutor.execute_sql() running at D:\lqs_project\my_prac\aiomysql_loop_prac.py:66> wait_for=<Future pending cb=[<TaskWakeupMethWrapper object at 0x000001FDF0BCE3D0>()]> cb=[_chain_future.<locals>._call_set_state() at D:\Python\Python39\lib\asyncio\futures.py:391]> ''' '''select * from t_executor;''' "select r3_name, return_data, test_time, result from t_r3_result " \ "where (r3_name = 'Soc温度最大值' or r3_name = 'Soc_max temperature') and result = 'Pass' limit 10000;"
aiomysql
最新推荐文章于 2024-08-21 22:35:36 发布