# -*- coding: utf-8 -*-
"""
@file: async_zt_flow_hcproblem
@author: maxiaolong01
@date: 2025/7/17 10:14
"""
import datetime
import asyncio
import time
from tortoise import run_async
from tortoise.transactions import in_transaction
from app.models.product import RequirementPool
from app.settings import TORTOISE_ORM
from app.utils.db import MySqlClient
BATCH_SIZE = 50 # 每批处理的项目数量
CONCURRENCY_LIMIT = 10 # 最大并发任务数
TIMING_DATA = {} # 存储各阶段耗时统计
class OptimizedMySqlClient:
"""增强版MySQL客户端（连接池管理）"""
def __init__(self):
self.pool = None
async def initialize_pool(self):
"""初始化连接池"""
if not self.pool:
self.pool = await MySqlClient.create_pool(TORTOISE_ORM)
print(f"连接池初始化完成: 最大连接数={self.pool.maxsize}")
return self.pool
async def execute_query(self, sql: str):
"""使用连接池执行查询"""
async with self.pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute(sql)
return await cursor.fetchall()
async def close_pool(self):
"""关闭连接池"""
if self.pool:
await self.pool.close()
self.pool = None
print("数据库连接池已关闭")
# 全局单例客户端
mysql_client = OptimizedMySqlClient()
async def get_zt_materiallib(sql: str, estimated_date: datetime.date) -> dict:
"""获取物料库信息（使用共享连接池）"""
start_time = time.perf_counter()
results = {}
for item in await mysql_client.execute_query(sql):
release_date = item.get("releaseDate", datetime.date(1971, 1, 1))
status = "按期关闭" if release_date <= estimated_date else "超期关闭"
results[item["id"]] = {
"release_date": str(release_date),
"software_project": item.get("name", "未关联"),
"requirement_status": status
}
TIMING_DATA.setdefault('materiallib', []).append(
time.perf_counter() - start_time)
return results
async def get_zt_build(build_id: int, estimated_date: datetime.date) -> dict:
"""获取构建信息（使用共享连接池）"""
start_time = time.perf_counter()
sql = f"""
SELECT b.id, b.name, m.releaseDate, p.name AS project_name
FROM zt_build b
LEFT JOIN zt_materiallib m ON m.topubbaseline = b.name AND m.status = '1'
LEFT JOIN zt_project p ON p.id = m.project
WHERE b.id = {build_id}
"""
items = await mysql_client.execute_query(sql)
if not items:
status = "超期未关闭" if datetime.date.today() > estimated_date else "按期未关闭"
result = {
"associated_version": "未关联",
"associated_version_id": build_id,
"release_date": str(datetime.date(1971, 1, 1)),
"software_project": "未关联",
"requirement_status": status
}
else:
item = items[0]
release_date = item.get("releaseDate", datetime.date(1971, 1, 1))
status = "按期关闭" if release_date <= estimated_date else "超期关闭"
result = {
"associated_version": item.get("name", "未关联"),
"associated_version_id": build_id,
"release_date": str(release_date),
"software_project": item.get("project_name", "未关联"),
"requirement_status": status
}
TIMING_DATA.setdefault('build', []).append(
time.perf_counter() - start_time)
return result
async def process_transformation(transform_id: int, estimated_date: datetime.date) -> dict:
"""处理需求转换（使用共享连接池）"""
start_time = time.perf_counter()
sql = f"""
SELECT id, stories FROM zt_build
WHERE stories LIKE '%{transform_id}%' AND deleted = '0'
ORDER BY id DESC LIMIT 1
"""
builds = await mysql_client.execute_query(sql)
if not builds:
status = "超期未关闭" if datetime.date.today() > estimated_date else "按期未关闭"
result = {
"rd_req_id": transform_id,
"release_date": str(datetime.date(1971, 1, 1)),
"software_project": "未关联",
"requirement_status": status
}
else:
build_id = builds[0].get("id")
build_data = await get_zt_build(build_id, estimated_date)
build_data["rd_req_id"] = transform_id
result = build_data
TIMING_DATA.setdefault('transformation', []).append(
time.perf_counter() - start_time)
return result
async def process_requirement_item(item):
"""处理单个需求项（使用事务批量提交）"""
start_time = time.perf_counter()
estimated_date = datetime.date.fromisoformat(
item.get("estimated_finish_date", "1971-01-01"))
transform_sql = f'SELECT * FROM zt_hctransform WHERE fromid = {item["zt_id"]} AND type = "hcrequirementtostory"'
transforms = await mysql_client.execute_query(transform_sql)
transform_tasks = [process_transformation(
t["transformid"], estimated_date) for t in transforms]
transform_results = await asyncio.gather(*transform_tasks)
base_data = {
"zt_id": item["zt_id"],
"project_no": item["project_no"],
"equip": item["equip"],
"req_name": item["req_name"],
"req_sub_date": datetime.datetime.strptime(
item.get("req_sub_date", "1971-01-01"), '%Y-%m-%d').strftime('%Y-%m-%d %H:%M:%S'),
"estimated_finish_date": datetime.datetime.strptime(
item.get("estimated_finish_date", "1971-01-01"), '%Y-%m-%d').strftime('%Y-%m-%d %H:%M:%S'),
"req_dev_leader": item["req_dev_leader"]
}
requirement_data = []
for transform in transform_results:
req_data = {
**base_data,
"associated_version": transform.get("associated_version", "未关联"),
"associated_version_id": transform.get("associated_version_id", 0),
"rd_req_id": transform.get("rd_req_id"),
"release_date": transform.get("release_date"),
"requirement_status": transform.get("requirement_status", "未关闭"),
"software_project": transform.get("software_project", "未关联")
}
requirement_data.append(RequirementPool(**req_data))
# 使用事务批量创建
if requirement_data:
async with in_transaction() as connection:
await RequirementPool.bulk_create(requirement_data, using_db=connection)
TIMING_DATA.setdefault('item', []).append(time.perf_counter() - start_time)
return len(requirement_data)
async def process_requirement_batch(item_batch: list):
"""批量处理需求项（带连接池的健康检查）"""
batch_start = time.perf_counter()
semaphore = asyncio.Semaphore(CONCURRENCY_LIMIT)
async def process_with_semaphore(item):
async with semaphore:
return await process_requirement_item(item)
results = await asyncio.gather(*[process_with_semaphore(item) for item in item_batch])
batch_time = time.perf_counter() - batch_start
TIMING_DATA.setdefault('batch', []).append({
'batch_size': len(item_batch),
'processing_time': batch_time,
'items_processed': sum(results)
})
async def get_zt_flow_hcrequirement_item(sqlfile: str = './app/tasks/sql/zt_flow_hcrequirement.sql'):
"""主处理函数（含连接池生命周期管理）"""
global TIMING_DATA
TIMING_DATA = {'total_start': time.perf_counter()}
# 初始化连接池
await mysql_client.initialize_pool()
with open(sqlfile, 'r', encoding='utf-8') as f:
sql = f.read()
all_items = await mysql_client.execute_query(sql)
TIMING_DATA['total_items'] = len(all_items)
# 分批处理并计时
for i in range(0, len(all_items), BATCH_SIZE):
batch = all_items[i:i + BATCH_SIZE]
await process_requirement_batch(batch)
TIMING_DATA['total_end'] = time.perf_counter()
TIMING_DATA['total_duration'] = TIMING_DATA['total_end'] - \
TIMING_DATA['total_start']
# 打印详细耗时报告
print("\n===== 并发执行耗时报告 =====")
print(f"总处理项: {TIMING_DATA['total_items']}")
print(f"总耗时: {TIMING_DATA['total_duration']:.4f}秒")
# 连接池使用统计
if mysql_client.pool:
pool_stats = {
'size': mysql_client.pool.size,
'free': mysql_client.pool.freesize,
'used': mysql_client.pool.size - mysql_client.pool.freesize
}
print(
f"连接池状态: 总数={pool_stats['size']} 使用中={pool_stats['used']} 空闲={pool_stats['free']}")
# 关闭连接池
await mysql_client.close_pool()
async def insert_product_requirement_pool():
"""入口函数（含连接池管理）"""
await get_zt_flow_hcrequirement_item()
if __name__ == '__main__':
# 完整执行流程
start_time = time.time()
print("任务开始执行...")
run_async(insert_product_requirement_pool())
print(f"任务总执行时间: {time.time() - start_time:.2f}秒")
优化这个脚本 MySqlClient 不用
本文介绍Python中datetime类型转换的方法，重点讲解如何使用strptime函数将字符串转换为datetime对象，并列出常用的格式化指令。
扫一扫
2084
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
