In [1]:
from dist_time import *
import pandas as pd
from collections.abc import Iterator, Iterable, Generator
from collections import defaultdict
import re
from sqlalchemy import create_engine
from sqlalchemy.types import Integer, Text, DateTime, Float, VARCHAR
from sqlalchemy import Table, MetaData, Column
import itertools as it
MACHINE_MAP = {'PreSort': 'm', 'Unload': 'r', 'Secondary': 'c', 'Primary': 'm', 'Export':'e', 'Reload': 'RE'}
MACHINE_MAP_ID = {'PreSort': 'm', 'Unload': 'a', 'Secondary': 'c', 'Primary': 'm', 'Export':'e', 'Reload': 'RE'}
class MySQLConfig:
HOST = '10.0.149.62'
USER = "root"
PASS = "root123"
DB = "cm_erzhou_v1_1"
CHARSET = 'utf8'
engine = create_engine(
f'mysql+pymysql://{USER}:{PASS}@{HOST}/{DB}?charset={CHARSET}',
isolation_level="READ UNCOMMITTED")
def creat_table():
db_eng = MetaData(bind=MySQLConfig.engine)
machine_table_sche = \
Table(
"i_queue_io",
db_eng,
Column("equipment_port_last", VARCHAR(length=32, )),
Column("equipment_port_next", VARCHAR(length=32, )),
Column("equipment_area_last", VARCHAR(length=32, )),
Column("equipment_area_next", VARCHAR(length=32, )),
Column("parameter_name", VARCHAR(length=64, )),
)
machine_table_sche.create(checkfirst=True)
def to_mysql(table_name: str, data: pd.DataFrame):
try:
data.to_sql(name=table_name,
con=MySQLConfig.engine,
if_exists='append',
index=0)
print(f"mysql write table {table_name} succeed!")
except Exception as exc:
print(f"mysql write table {table_name} failed, error: {exc}.")
raise Exception
def dict_to_dataframe(pd: pd, *, parameter_list: list=None):
data = defaultdict(list)
for paramer in parameter_list:
data['process_time'].extend(list(paramer.values()))
data ['parameter_name'].extend(list(paramer.keys()))
return pd.DataFrame(data)
def df_to_excel(sheet, df):
file_name = "d:/Git/git.jupyter/jupyter_app/ipython/py36_erzhou_input/dist_time/out/outdata_queue_io.xlsx"
df.to_excel(file_name, sheet_name=sheet, index=False)
def check_dic_value(data: dict=None):
type_dic_value = set()
if data is None:
return
else:
for _,v in data.items():
if isinstance(v, list):
type_dic_value.add('L')
elif isinstance(v, str):
type_dic_value.add('S')
elif isinstance(v, int):
type_dic_value.add('I')
else:
raise ValueError('字典值类型不是字符或列表形式！')
if len(type_dic_value) !=1:
raise ValueError('字典值类型既有字符也有列表！')
return {type_dic_value.pop(): data}
def dict_list_to_dataframe(pd: pd.DataFrame, parameter_list: list=None):
if parameter_list:
re_list = []
for data in parameter_list:
# print(data[1])
ckeck_data = check_dic_value(data[0])
for k, _ in ckeck_data.items():
if k == 'L': # L 表示字典存储值类型为列表
re_data = it.chain(
*[i for i in map(
lambda x, y : [[x, v, data[1]] for v in y if isinstance(y, list)],
ckeck_data[k].keys(), ckeck_data[k].values())]
)
re_list.extend(re_data)
# return pd.DataFrame(list(re_data), columns=['equipment_port_last', 'equipment_port_next'])
elif k == 'S': # S表示字典存储值类型为字符串
re_data = [[k, v, data[1]] for k, v in ckeck_data[k].items()]
re_list.extend(re_data)
return pd.DataFrame(re_list, columns=['equipment_port_last', 'equipment_port_next', 'parameter_name'])
else:
return parameter_list
In [13]:
QUEUE_IO_DICT = {
'R': [
(R_Dock_Unload, 'R_Dock_Unload'), # 卸货口Dock与卸货区对应关系
(R_Dock_R_PreSort_rule, 'R_Dock_R_PreSort_rule'), # 卸货区与初分拣机对应关系
(R_PreSort_infeed_rule, 'R_PreSort_infeed_rule'), # 初分拣机入口与初分拣机对应关系
(R_PreSort_D_Primary_rule, 'R_PreSort_D_Primary_rule'), # 初分拣机与国内初分拣机链接信息
(R_PreSort_S_Primary_rule, 'R_PreSort_S_Primary_rule'), # 初分拣机与小件初分拣机链接信息
(R_PreSort_I_Export_rule, 'R_PreSort_I_Export_rule'), # 初分拣机与海关机器入口链接信息
(R_Secondary_infeed_rule, 'R_Secondary_infeed_rule'), # 终分拣机入口信息
(R_Secondary_R_Reload_rule, 'R_Secondary_R_Reload_rule'), # 终分拣机出口与装货区出口对应信息
],
'D': [
(D_Dock_Unload, 'D_Dock_Unload'), # 卸货口Dock与卸货区对应关系
(D_Dock_D_Primary_rule, 'D_Dock_D_Primary_rule'), # 卸货区与初分拣机对应关系
(D_Dock_S_Primary_rule, 'D_Dock_S_Primary_rule'), # 初分拣机与小件初分拣机链接信息
(D_Primary_infeed_rule, 'D_Primary_infeed_rule'), # 初分拣机入口与初分拣机对应关系
(D_Secondary_infeed_rule, 'D_Secondary_infeed_rule'), # 初分拣机入口与初分拣机对应关系
(D_Secondary_D_Reload_rule, 'D_Secondary_D_Reload_rule'), # 终分拣机出口与装货区出口链接信息
],
'I': [
(I_Dock_Unload, 'I_Dock_Unload'), # 卸货口Dock与卸货区对应关系
(I_Dock_I_PreSort_rule, 'I_Dock_I_PreSort_rule'), # 卸货区与初分拣机对应关系
(I_PreSort_infeed_rule, 'I_PreSort_infeed_rule'), # 初分拣机入口与初分拣机对应关系
(I_PreSort_D_Primary_rule, 'I_PreSort_D_Primary_rule'), # 国际初分拣与国内初分拣机对应关系
(I_PreSort_S_Primary_rule, 'I_PreSort_S_Primary_rule'), # 国际初分拣与小件初分拣机对应关系
(I_Export_infeed_rule, 'I_Export_infeed_rule'), # 海关机入口与海关机器对应关系
(I_Export_I_Export_rule, 'I_Export_I_Export_rule'), # 海关区与海关机对应关系
(I_Secondary_infeed_rule, 'I_Secondary_infeed_rule'), # 终分拣机入口与终分拣机对应关系
(I_Secondary_I_Reload_rule, 'I_Secondary_I_Reload_rule') # 终分拣机出口与装货区出口链接信息
],
'S': [
(S_Secondary_infeed_rule, 'S_Secondary_infeed_rule'), # 小件分拣机入口与小件分拣机对应关系
(S_Secondary_D_Primary_rule, 'S_Secondary_D_Primary_rule'), # 小件终分拣机与国内初分拣机对应关系
(S_Secondary_I_PreSort_rule, 'S_Secondary_I_PreSort_rule'), # 小件终分拣机与国际初分拣机对应关系
(S_Secondary_S_Secondary_Pack_rule, 'S_Secondary_S_Secondary_Pack_rule'), # 小件终分拣出口与打包机对应关系
(S_Primary_infeed_rule, 'S_Primary_infeed_rule') # 小件初分拣机入口与初分拣机对应关系
]
}
QUEUE_RELOAD_DICT = {
'D': [
(R_Secondary_rule, 'R_PreSort_infeed_rule'),
(R_Reload_R_Secondary, 'R_PreSort_infeed_rule'),
]
}
# COUNT_DICT = {
# 'dock':[
# # (R_Dock_Unload, 'R_Dock_Unload'), # 卸货口Dock与卸货区对应关系
# (D_Dock_Unload, 'D_Dock_Unload'), # 卸货口Dock与卸货区对应关系
# # (I_Dock_Unload, 'I_Dock_Unload'), # 卸货口Dock与卸货区对应关系
# ]
# }
In [18]:
i_queue_df = dict_list_to_dataframe(pd, parameter_list=COUNT_DICT['dock'])
# i_queue_df.equipment_port_last = i_queue_df.equipment_port_last.where(
# i_queue_df.equipment_port_last.str.contains('^[A-Z]_'),
# 'I_'+i_queue_df.equipment_port_last)
# i_queue_df['equipment_area_last'], i_queue_df['equipment_port_last'] = i_queue_df.equipment_port_last.str.split('_', 1).str
# i_queue_df.equipment_port_next = i_queue_df.equipment_port_next.where(
# i_queue_df.equipment_port_next.str.contains('^[A-Z]_'),
# 'I_'+i_queue_df.equipment_port_next)
# i_queue_df['equipment_area_next'], i_queue_df['equipment_port_next'] = i_queue_df.equipment_port_next.str.split('_', 1).str
# i_queue_data = i_queue_df.loc[:,[
# 'equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'parameter_name']]
# i_queue_data.sample(10)
# # [i_queue_data.parameter_name.str.startswith('I_Dock_Unload')]
Out[18]: