import sqlite3 import json import sys import os from datetime import date DB_PATH = "production.db" CONFIG_PATH = "config.json" def create_config_tables(cursor): cursor.executescript(""" CREATE TABLE IF NOT EXISTS MeasurementPoints ( Id INTEGER PRIMARY KEY AUTOINCREMENT, PointName TEXT NOT NULL, FieldUnit TEXT DEFAULT '', SortOrder INTEGER DEFAULT 0 ); CREATE TABLE IF NOT EXISTS PLCs ( Id INTEGER PRIMARY KEY AUTOINCREMENT, PLCName TEXT NOT NULL, IPAddress TEXT NOT NULL, DBNumber INTEGER NOT NULL, StationCount INTEGER NOT NULL, SortOrder INTEGER DEFAULT 0 ); CREATE TABLE IF NOT EXISTS Stations ( Id INTEGER PRIMARY KEY AUTOINCREMENT, PLCId INTEGER NOT NULL REFERENCES PLCs(Id), StationIndex INTEGER NOT NULL, StationName TEXT NOT NULL, PointId INTEGER REFERENCES MeasurementPoints(Id), SortOrder INTEGER DEFAULT 0 ); """) def create_mp_data_tables(cursor, date_str: str): cursor.execute("SELECT Id, PointName FROM MeasurementPoints ORDER BY Id") points = cursor.fetchall() if not points: print(" [WARN] 没有注册检测点,跳过建表") return for pid, pname in points: table = f"MPData_P{pid}_{date_str}" cursor.execute(f""" CREATE TABLE IF NOT EXISTS {table} ( Id INTEGER PRIMARY KEY AUTOINCREMENT, ProductID TEXT NOT NULL UNIQUE, RecordTime DATETIME, IsQualified INTEGER, InspectionCount INTEGER DEFAULT 0, UpperLimit REAL, LowerLimit REAL, MeasuredValue REAL ); """) print(f" [OK] {table}") def clear_config_data(cursor): cursor.executescript(""" DELETE FROM Stations; DELETE FROM MeasurementPoints; DELETE FROM PLCs; """) def init_from_config(config_path: str, db_path: str, date_str: str): if not os.path.exists(config_path): print(f"[ERROR] 配置文件不存在: {config_path}") sys.exit(1) with open(config_path, "r", encoding="utf-8") as f: config = json.load(f) conn = sqlite3.connect(db_path) cursor = conn.cursor() print(">>> 创建配置表...") create_config_tables(cursor) print(">>> 清空旧配置数据...") clear_config_data(cursor) print(">>> 写入全局检测点...") point_name_map = {} for mp in config["measurement_points"]: cursor.execute( """INSERT INTO MeasurementPoints (PointName, FieldUnit, SortOrder) VALUES (?, ?, ?)""", (mp["point_name"], mp.get("field_unit", ""), mp["sort_order"]), ) point_name_map[mp["point_name"]] = cursor.lastrowid print(">>> 写入 PLC 和工序配置...") for plc in config["plcs"]: cursor.execute( """INSERT INTO PLCs (PLCName, IPAddress, DBNumber, StationCount, SortOrder) VALUES (?, ?, ?, ?, ?)""", (plc["plc_name"], plc["ip_address"], plc["db_number"], plc["station_count"], plc["sort_order"]), ) plc_id = cursor.lastrowid for st in plc["stations"]: point_id = point_name_map.get(st["point_name"]) if point_id is None: print(f" [WARN] 检测点 '{st['point_name']}' 未定义,跳过") continue cursor.execute( """INSERT INTO Stations (PLCId, StationIndex, StationName, PointId, SortOrder) VALUES (?, ?, ?, ?, ?)""", (plc_id, st["station_index"], st["station_name"], point_id, st["sort_order"]), ) print(">>> 创建检测点数据表...") create_mp_data_tables(cursor, date_str) conn.commit() conn.close() print(f"\n[DONE] 数据库已初始化: {db_path}") def show_summary(db_path: str, date_str: str): conn = sqlite3.connect(db_path) cursor = conn.cursor() print("\n=== 全局检测点 ===") cursor.execute("SELECT Id, PointName, FieldUnit FROM MeasurementPoints ORDER BY SortOrder") for mp in cursor.fetchall(): unit = f" ({mp[2]})" if mp[2] else "" print(f" P{mp[0]}: {mp[1]}{unit}") print("\n=== 产线配置 ===") cursor.execute("SELECT Id, PLCName, IPAddress, DBNumber, StationCount FROM PLCs ORDER BY SortOrder") for plc in cursor.fetchall(): print(f"\nPLC: {plc[1]} ({plc[2]}, DB{plc[3]}, {plc[4]} 工序)") cursor.execute(""" SELECT s.Id, s.StationName, s.StationIndex, s.PointId, mp.PointName FROM Stations s LEFT JOIN MeasurementPoints mp ON mp.Id = s.PointId WHERE s.PLCId=? ORDER BY s.SortOrder """, (plc[0],)) for st in cursor.fetchall(): print(f" ├─ {st[1]} (idx={st[2]})") print(f" │ └─ P{st[3]}: {st[4]}") print(f"\n=== 数据表 ===") cursor.execute("SELECT Id, PointName FROM MeasurementPoints ORDER BY Id") for pid, pname in cursor.fetchall(): table = f"MPData_P{pid}_{date_str}" cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", (table,)) if cursor.fetchone(): cursor.execute(f"PRAGMA table_info({table})") cols = cursor.fetchall() print(f" [OK] {table} ({len(cols)} 列) <- {pname}") else: print(f" [MISSING] {table}") conn.close() if __name__ == "__main__": force = "-f" in sys.argv or "--force" in sys.argv args = [a for a in sys.argv[1:] if not a.startswith("-")] config_path = args[0] if len(args) > 0 else CONFIG_PATH db_path = args[1] if len(args) > 1 else DB_PATH date_str = date.today().strftime("%Y_%m_%d") if os.path.exists(db_path): if not force: ans = input(f"数据库 {db_path} 已存在,重新初始化将清空数据。[y/N] ") if ans.lower() != "y": print("已取消") sys.exit(0) os.remove(db_path) init_from_config(config_path, db_path, date_str) show_summary(db_path, date_str)