数据库连接信息
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()
sql_query = “”"
select *
FROM ACME.DRB_DATA
“”"
cursor.execute(sql_query)
results = cursor.fetchall()
drbdata = pd.DataFrame(results, columns=columns)
site_columns = [f’SITE{i}_VAL’ for i in range(1, 26)]
melted_data = drbdata.melt(
id_vars=[‘DRB_ID’,‘PARAMETER_MAPPING’, ‘LOTNO’, ‘WAFER_NO’, ‘MEASURE_TIME’, ‘WAFER_TYPE’],
value_vars=site_columns,
var_name=‘SITE’,
value_name=‘VALUE’)
melted_data[‘VALUE’] = melted_data[‘VALUE’].astype(float)
melted_data = melted_data.dropna(subset=[‘VALUE’])
grouped = melted_data.groupby(‘DRB_ID’) #drbid和lotno应该是一一对应的关系
for DRB_ID, lot_group in grouped:
for param in lot_group[‘PARAMETER_MAPPING’].unique():
param_data = lot_group[lot_group[‘PARAMETER_MAPPING’] == param].copy()
conditions = [
param_data[‘WAFER_TYPE’] == ‘DRB_WAFER’,
param_data[‘WAFER_TYPE’] == ‘NONE_DRB_WAFER’
]
choices = [
param_data[‘LOTNO’].astype(str) + ‘-’ + param_data[‘WAFER_NO’].astype(str),
param_data[‘LOTNO’].astype(str) + ‘-’ + param_data[‘WAFER_NO’].astype(str)
]
tick_labels = np.select(conditions, choices,
default=param_data[‘WAFER_TYPE’].astype(str) + ‘-’ + param_data[‘WAFER_NO’].astype(str))
将标签添加到数据中并按标签分组
param_data[‘x_label’] = tick_labels
sorted_data = param_data.sort_values(‘MEASURE_TIME’).copy()
grouped_by_label = sorted_data.groupby(‘x_label’, sort=False)
同时过滤空分组
filtered_groups = [(name, group) for name, group in grouped_by_label if not group[‘VALUE’].empty]
labels = [name for name, _ in filtered_groups]
data = [group[‘VALUE’].values for _, group in filtered_groups]
创建绘图
plt.figure(figsize=(20, 10))
boxplot = plt.boxplot(
data,
tick_labels=labels,
patch_artist=True, # 必须开启才能填充颜色
)
设置图表属性
plt.title(f"DRB_ID: {DRB_ID} - Parameter: {param}")
plt.xticks(rotation=90)
plt.grid(True)
#保存结果
datasave = f"H:/DRB/{DRB_ID}"
if not os.path.exists(datasave):
os.makedirs(datasave)
box_save_dir = f"H:/DRB/{DRB_ID}/{param}.png"
plt.savefig(box_save_dir, bbox_inches=‘tight’,dpi=300)
plt.close()
box_dir = f"H:/DRB/{DRB_ID}"
prs = Presentation()
slide_layout = prs.slide_layouts[6] # 空白版式
try:
box_images = sorted(
[os.path.join(box_dir, f) for f in os.listdir(box_dir) if f.endswith((‘.png’, ‘.jpg’, ‘.jpeg’))])
except FileExistsError:
raise Exception(f"目录不存在：{box_dir}")
total_pages = len(box_images) // 4 + 1
for page in range(total_pages):
slide = prs.slides.add_slide(slide_layout)
_add_images_to_slide(
slide, box_images[page * 4: (page + 1) * 4],
left_start=Inches(1.0), top_start=Inches(0.6),
）
output_path = “H:/DRB”
os.makedirs(os.path.dirname(output_path), exist_ok=True)
if not os.path.exists(output_path):
os.makedirs(output_path)
prs.save(f"{output_path}/{DRB_ID}.pptx")
#确认路径中是否包含{DRB_ID}.pptx,如果包含则update status=1
if contains_file(output_path, f"{DRB_ID}.pptx"):
try:
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")
connection2 = oracledb.connect(user=username, password=password, dsn=dsn)
cursor = connection2.cursor()
sql_query = """
update DRB_INFO_CONFIG
set status = :1
where DRB_ID = :2
"""
cursor.execute(sql_query, (1, f"{DRB_ID}"))
connection2.commit()
finally:
if ‘connection2’ in locals():
connection2.close()
except Exception as e:
print(e)
finally:
cursor.close()
connection.close()
oracledb.exceptions.InterfaceError: DPY-1001: not connected to database
本文探讨了在Oracle 10中IN与EXISTS关键字的实际使用情况，指出两者在大多数情况下并无区别，因为数据库会自动进行查询转换。然而，对于NOT IN与NOT EXISTS则存在明显的差异。
扫一扫
3039
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
