import pymysql conn = pymysql.connect(host='123.60.180.165', port=3306, user='aidopremote', password='1234567890aiDOP#', database='aidopdev', charset='utf8mb4', autocommit=True) def ensure_columns(cur, table, defs): cur.execute(f"SHOW COLUMNS FROM `{table}`") existing = {r[0] for r in cur.fetchall()} for col, ddl in defs: if col not in existing: cur.execute(f"ALTER TABLE `{table}` ADD COLUMN {ddl}") try: with conn.cursor() as cur: ensure_columns(cur, 'ado_s9_kpi_def', [ ('metric_code', "metric_code varchar(64) NULL COMMENT '指标编码'"), ('metric_name', "metric_name varchar(128) NULL COMMENT '指标名称'"), ('module_code', "module_code varchar(8) NULL COMMENT '模块编码'"), ('metric_level', "metric_level tinyint NULL COMMENT '层级'"), ('unit', "unit varchar(16) NULL COMMENT '单位'"), ('target_value', "target_value decimal(18,4) NULL COMMENT '目标值'"), ('warn_threshold', "warn_threshold decimal(18,4) NULL COMMENT '预警阈值'"), ('danger_threshold', "danger_threshold decimal(18,4) NULL COMMENT '告警阈值'"), ]) for t in ['ado_s9_kpi_value_l1_day','ado_s9_kpi_value_l2_day','ado_s9_kpi_value_l3_day']: ensure_columns(cur, t, [ ('module_code', "module_code varchar(8) NULL COMMENT '模块编码'"), ('metric_code', "metric_code varchar(64) NULL COMMENT '指标编码'"), ('metric_value', "metric_value decimal(18,4) NULL COMMENT '指标值'"), ('target_value', "target_value decimal(18,4) NULL COMMENT '目标值'"), ('status_color', "status_color varchar(16) NULL COMMENT '状态色'"), ('trend_flag', "trend_flag varchar(8) NULL COMMENT '趋势'"), ('calc_time', "calc_time datetime(3) NULL COMMENT '计算时间'"), ]) ensure_columns(cur, 'ado_s8_alert_record', [ ('alert_code', "alert_code varchar(64) NULL COMMENT '预警编码'"), ('rule_code', "rule_code varchar(64) NULL COMMENT '规则编码'"), ('metric_code', "metric_code varchar(64) NULL COMMENT '指标编码'"), ('metric_value', "metric_value decimal(18,4) NULL COMMENT '指标值'"), ('target_value', "target_value decimal(18,4) NULL COMMENT '目标值'"), ('level_code', "level_code varchar(16) NULL COMMENT '预警等级'"), ('alert_time', "alert_time datetime(3) NULL COMMENT '预警时间'"), ('message', "message varchar(500) NULL COMMENT '预警信息'"), ]) cur.execute("DELETE FROM ado_s9_kpi_value_l1_day WHERE tenant_id=1 AND factory_id=1") cur.execute("DELETE FROM ado_s8_alert_record WHERE tenant_id=1 AND factory_id=1") inserts = [ "INSERT INTO ado_s9_kpi_value_l1_day (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,module_code,metric_code,metric_value,target_value,status_color,trend_flag,calc_time) VALUES (500001,1,1,1,1,'OK',CURDATE(),NOW(3),NOW(3),0,1,'S1','S1_OTD',75,95,'red','down',NOW(3))", "INSERT INTO ado_s9_kpi_value_l1_day (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,module_code,metric_code,metric_value,target_value,status_color,trend_flag,calc_time) VALUES (500002,1,1,1,1,'OK',CURDATE(),NOW(3),NOW(3),0,1,'S2','S2_SAT',85,99,'red','down',NOW(3))", "INSERT INTO ado_s9_kpi_value_l1_day (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,module_code,metric_code,metric_value,target_value,status_color,trend_flag,calc_time) VALUES (500003,1,1,1,1,'OK',CURDATE(),NOW(3),NOW(3),0,1,'S3','S3_KIT',75,95,'red','down',NOW(3))", "INSERT INTO ado_s9_kpi_value_l1_day (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,module_code,metric_code,metric_value,target_value,status_color,trend_flag,calc_time) VALUES (500004,1,1,1,1,'OK',CURDATE(),NOW(3),NOW(3),0,1,'S4','S4_ONTIME',99,99,'green','flat',NOW(3))", "INSERT INTO ado_s9_kpi_value_l1_day (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,module_code,metric_code,metric_value,target_value,status_color,trend_flag,calc_time) VALUES (500005,1,1,1,1,'OK',CURDATE(),NOW(3),NOW(3),0,1,'S5','S5_MATCH',99,98,'green','up',NOW(3))", "INSERT INTO ado_s9_kpi_value_l1_day (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,module_code,metric_code,metric_value,target_value,status_color,trend_flag,calc_time) VALUES (500006,1,1,1,1,'OK',CURDATE(),NOW(3),NOW(3),0,1,'S6','S6_FPYY',85,98,'red','down',NOW(3))", "INSERT INTO ado_s9_kpi_value_l1_day (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,module_code,metric_code,metric_value,target_value,status_color,trend_flag,calc_time) VALUES (500007,1,1,1,1,'OK',CURDATE(),NOW(3),NOW(3),0,1,'S7','S7_DEFECT',1.0,0.8,'red','up',NOW(3))", "INSERT INTO ado_s9_kpi_value_l1_day (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,module_code,metric_code,metric_value,target_value,status_color,trend_flag,calc_time) VALUES (500008,1,1,1,1,'OK',CURDATE(),NOW(3),NOW(3),0,1,'S8','S8_ALERT',4,0,'red','up',NOW(3))", "INSERT INTO ado_s9_kpi_value_l1_day (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,module_code,metric_code,metric_value,target_value,status_color,trend_flag,calc_time) VALUES (500009,1,1,1,1,'OK',CURDATE(),NOW(3),NOW(3),0,1,'S9','S9_INDEX',86,95,'yellow','down',NOW(3))", "INSERT INTO ado_s8_alert_record (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,alert_code,rule_code,metric_code,metric_value,target_value,level_code,alert_time,message) VALUES (800001,1,1,1,1,'OPEN',CURDATE(),NOW(3),NOW(3),0,1,'ALARM-S2-001','RULE-S2-OEE','S2_SAT',85,99,'critical',NOW(3),'机台 A-04 停机超时')", "INSERT INTO ado_s8_alert_record (id,tenant_id,org_id,company_id,factory_id,status,biz_date,create_time,update_time,is_deleted,is_active,alert_code,rule_code,metric_code,metric_value,target_value,level_code,alert_time,message) VALUES (800002,1,1,1,1,'OPEN',CURDATE(),NOW(3),NOW(3),0,1,'ALARM-S4-001','RULE-S4-MATL','S4_ONTIME',93.8,99,'high',NOW(3),'物料 P-99 缺料预警')" ] for s in inserts: cur.execute(s) cur.execute("SELECT COUNT(*) FROM ado_s9_kpi_value_l1_day WHERE tenant_id=1 AND factory_id=1") a = cur.fetchone()[0] cur.execute("SELECT COUNT(*) FROM ado_s8_alert_record WHERE tenant_id=1 AND factory_id=1") b = cur.fetchone()[0] print(f"ok: l1={a}, s8_alert={b}") finally: conn.close()