AiDOPWarehouse/ai-dop-platform/docs/db/_migrate_and_seed_kanban.py

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()