112 lines
3.4 KiB
Python
112 lines
3.4 KiB
Python
#!/usr/bin/env python
|
||
# -*- coding: utf-8 -*-
|
||
"""
|
||
数据库迁移脚本：删除work_orders表中的station字段
|
||
"""
|
||
|
||
import sqlite3
|
||
import os
|
||
from datetime import datetime
|
||
|
||
|
||
def migrate_remove_station():
|
||
"""删除work_orders表中的station字段"""
|
||
db_path = "wrench.db"
|
||
|
||
if not os.path.exists(db_path):
|
||
print(f"数据库文件不存在: {db_path}")
|
||
return
|
||
|
||
print("="*60)
|
||
print("数据库迁移：删除station字段")
|
||
print("="*60)
|
||
|
||
conn = sqlite3.connect(db_path)
|
||
cursor = conn.cursor()
|
||
|
||
try:
|
||
# 检查表是否存在station字段
|
||
cursor.execute("PRAGMA table_info(work_orders)")
|
||
columns = cursor.fetchall()
|
||
column_names = [col[1] for col in columns]
|
||
|
||
if 'station' not in column_names:
|
||
print("[OK] station字段不存在，无需迁移")
|
||
conn.close()
|
||
return
|
||
|
||
print(f"检测到station字段，开始迁移...")
|
||
print(f"当前字段: {column_names}")
|
||
|
||
# SQLite不支持直接删除列，需要重建表
|
||
# 1. 创建新表（不包含station字段）
|
||
print("\n1. 创建新表结构...")
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS work_orders_new (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
trace_id TEXT NOT NULL,
|
||
process_id TEXT NOT NULL,
|
||
process_name TEXT,
|
||
product_name TEXT,
|
||
operator TEXT,
|
||
status TEXT DEFAULT 'pending',
|
||
bolts TEXT NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(trace_id, process_id)
|
||
)
|
||
''')
|
||
|
||
# 2. 复制数据（排除station字段）
|
||
print("2. 复制数据...")
|
||
cursor.execute('''
|
||
INSERT INTO work_orders_new
|
||
(id, trace_id, process_id, process_name, product_name, operator, status, bolts, created_at, updated_at)
|
||
SELECT
|
||
id, trace_id, process_id, process_name, product_name, operator, status, bolts, created_at, updated_at
|
||
FROM work_orders
|
||
''')
|
||
|
||
# 3. 删除旧表
|
||
print("3. 删除旧表...")
|
||
cursor.execute('DROP TABLE work_orders')
|
||
|
||
# 4. 重命名新表
|
||
print("4. 重命名新表...")
|
||
cursor.execute('ALTER TABLE work_orders_new RENAME TO work_orders')
|
||
|
||
conn.commit()
|
||
|
||
# 验证
|
||
cursor.execute("PRAGMA table_info(work_orders)")
|
||
new_columns = cursor.fetchall()
|
||
new_column_names = [col[1] for col in new_columns]
|
||
|
||
print(f"\n[OK] 迁移完成！")
|
||
print(f"新表字段: {new_column_names}")
|
||
|
||
if 'station' in new_column_names:
|
||
print("[WARN] 警告：station字段仍然存在")
|
||
else:
|
||
print("[OK] station字段已成功删除")
|
||
|
||
# 统计数据
|
||
cursor.execute("SELECT COUNT(*) FROM work_orders")
|
||
count = cursor.fetchone()[0]
|
||
print(f"[OK] 工单数量: {count}")
|
||
|
||
except Exception as e:
|
||
conn.rollback()
|
||
print(f"[FAIL] 迁移失败: {e}")
|
||
import traceback
|
||
traceback.print_exc()
|
||
finally:
|
||
conn.close()
|
||
|
||
print("="*60)
|
||
|
||
|
||
if __name__ == "__main__":
|
||
migrate_remove_station()
|
||
|