80 lines
2.7 KiB
Python
80 lines
2.7 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""
|
||
資料庫遷移腳本：新增 notification_emails 欄位到 ts_temp_spec 表
|
||
執行方式：python migrate_add_email_column.py
|
||
"""
|
||
|
||
import os
|
||
import sys
|
||
from sqlalchemy import create_engine, text
|
||
from config import Config
|
||
|
||
def migrate_database():
|
||
"""執行資料庫遷移，新增 notification_emails 欄位"""
|
||
|
||
# 使用 config.py 中的資料庫配置
|
||
engine = create_engine(Config.SQLALCHEMY_DATABASE_URI)
|
||
|
||
try:
|
||
with engine.connect() as conn:
|
||
# 檢查欄位是否已存在
|
||
result = conn.execute(text("""
|
||
SELECT COLUMN_NAME
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_NAME = 'ts_temp_spec'
|
||
AND COLUMN_NAME = 'notification_emails'
|
||
AND TABLE_SCHEMA = DATABASE()
|
||
"""))
|
||
|
||
if result.fetchone():
|
||
print("✓ notification_emails 欄位已存在，無需遷移")
|
||
return True
|
||
|
||
# 新增欄位
|
||
print("正在新增 notification_emails 欄位到 ts_temp_spec 表...")
|
||
conn.execute(text("""
|
||
ALTER TABLE ts_temp_spec
|
||
ADD COLUMN notification_emails TEXT DEFAULT NULL
|
||
COMMENT '通知郵件清單，以分號分隔'
|
||
"""))
|
||
conn.commit()
|
||
|
||
print("✓ 成功新增 notification_emails 欄位")
|
||
|
||
# 驗證欄位是否成功新增
|
||
result = conn.execute(text("""
|
||
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_NAME = 'ts_temp_spec'
|
||
AND COLUMN_NAME = 'notification_emails'
|
||
AND TABLE_SCHEMA = DATABASE()
|
||
"""))
|
||
|
||
column_info = result.fetchone()
|
||
if column_info:
|
||
print(f"✓ 欄位資訊：")
|
||
print(f" - 欄位名稱：{column_info[0]}")
|
||
print(f" - 資料類型：{column_info[1]}")
|
||
print(f" - 可否為空：{column_info[2]}")
|
||
return True
|
||
else:
|
||
print("✗ 欄位新增後無法驗證")
|
||
return False
|
||
|
||
except Exception as e:
|
||
print(f"✗ 遷移失敗：{str(e)}")
|
||
return False
|
||
|
||
if __name__ == "__main__":
|
||
print("=" * 50)
|
||
print("資料庫遷移腳本")
|
||
print("=" * 50)
|
||
|
||
success = migrate_database()
|
||
|
||
if success:
|
||
print("\n✓ 資料庫遷移完成！")
|
||
print(" 現在可以使用新的郵件通知功能了。")
|
||
else:
|
||
print("\n✗ 資料庫遷移失敗，請檢查錯誤訊息。")
|
||
sys.exit(1) |