import datetime import numpy as np import oracledb import pandas as pd import matplotlib.pyplot as plt from pptx import Presentation from pptx.util import Inches import os import matplotlib.colors from matplotlib.patches import Patch import time import logging import test import concurrent.futures # 获取当前时间 now = datetime.datetime.now() # 将时间转换为字符串，格式为YYYY-MM-DD_HH-MM-SS timestamp = now.strftime("%Y-%m-%d") logger = logging.getLogger() logger.setLevel(logging.INFO) format = logging.Formatter('%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s') logFile = f'{timestamp}-log.txt' fh = logging.FileHandler(logFile, mode='a', encoding='utf-8') fh.setLevel(logging.INFO) fh.setFormatter(format) logger.addHandler(fh) start_time1 = time.time() # 数据库连接信息 dsn = ( "(DESCRIPTION=" "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.164.60)(PORT=1521)))" "(CONNECT_DATA=(SERVICE_NAME=YMSDB01)))" ) username = 'acme' password = 'acme' oracledb.init_oracle_client(lib_dir=r"C:\Users\Q06412\Downloads\instantclient-basic-windows.x64-21.17.0.0.0dbru\instantclient_21_17") # 创建连接 connection = oracledb.connect(user=username, password=password, dsn=dsn) try: # 创建一个游标 cursor = connection.cursor() logging.info("SQL查询5个状态为0的lot任务---开始") # SQL 查询语句 sql_query = """ SELECT DISTINCT DRB_ID FROM ( SELECT DISTINCT DRB_ID,LOT_ID,ROW_NUMBER () OVER (ORDER BY CREATE_TIME ASC) AS RN FROM DRB_INFO_CONFIG dic WHERE STATUS=0 ) DRB_CNT WHERE RN <=5 """ # 执行查询 cursor.execute(sql_query) # 获取所有结果 lotresults = cursor.fetchall() logging.info("SQL查询5个状态为0的lot任务---结束") # print(len(results)) ==5 遍历每个drblot for i in range(0, len(lotresults)): # 5个lot查出来立马修改status=2，表示正在进程中 sql_query = """ update DRB_INFO_CONFIG set STATUS = :1 where DRB_ID = :2 """ cursor.execute(sql_query, (2, lotresults[i][0])) connection.commit() except Exception as e: logging.info(e) finally: # 关闭游标和连接 cursor.close() connection.close() def execute_query(sql_query): # 每个线程独立创建连接（关键！避免线程安全问题） # 创建连接 connection = oracledb.connect(user=username, password=password, dsn=dsn) cursor = connection.cursor() try: cursor.execute(sql_query) results = cursor.fetchall() return results # 返回结果供主线程收集 except Exception as ee: logging.info(ee) finally: connection.close() # 确保关闭连接 with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor: # 控制并发数 # 提交所有查询任务 future_to_query = { executor.submit(execute_query, test.sql_query): test for test in lotresults } # 收集结果（按完成顺序） results = [] for future in concurrent.futures.as_completed(future_to_query): test = future_to_query[future] try: result = future.result() results.append(result) # 存储结果 except Exception as e: print(f"Query {test.sql_query} failed: {e}") end_time1 = time.time() run_time = end_time1 - start_time1 logging.info("代码运行时间为： %s 秒", run_time)