aiomysql

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;"