122 lines
3.9 KiB
Python
122 lines
3.9 KiB
Python
"""
|
||
初始化 etl_feiqiu 和 zqyy_app 数据库的 DDL 执行脚本。
|
||
通过 psycopg2 直接连接新库并执行 SQL 文件。
|
||
"""
|
||
import os
|
||
import sys
|
||
import psycopg2
|
||
|
||
DB_HOST = "100.64.0.4"
|
||
DB_PORT = 5432
|
||
DB_USER = "local-Python"
|
||
DB_PASSWORD = "Neo-local-1991125"
|
||
|
||
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
|
||
|
||
|
||
def execute_sql_file(conn, filepath, label=""):
|
||
"""读取并执行一个 SQL 文件"""
|
||
full_path = os.path.join(BASE_DIR, filepath)
|
||
if not os.path.exists(full_path):
|
||
print(f" [SKIP] 文件不存在: {filepath}")
|
||
return False
|
||
with open(full_path, "r", encoding="utf-8") as f:
|
||
sql = f.read()
|
||
if not sql.strip():
|
||
print(f" [SKIP] 文件为空: {filepath}")
|
||
return False
|
||
try:
|
||
cur = conn.cursor()
|
||
cur.execute(sql)
|
||
conn.commit()
|
||
print(f" [OK] {label or filepath}")
|
||
return True
|
||
except Exception as e:
|
||
conn.rollback()
|
||
print(f" [FAIL] {label or filepath}: {e}")
|
||
return False
|
||
|
||
|
||
def init_etl_feiqiu():
|
||
"""初始化 etl_feiqiu 数据库：六层 schema DDL + 种子数据"""
|
||
print("\n=== 初始化 etl_feiqiu 数据库 ===")
|
||
conn = psycopg2.connect(
|
||
host=DB_HOST, port=DB_PORT, user=DB_USER,
|
||
password=DB_PASSWORD, dbname="etl_feiqiu"
|
||
)
|
||
conn.autocommit = False
|
||
|
||
# 六层 schema DDL（按依赖顺序）
|
||
schema_files = [
|
||
("db/etl_feiqiu/schemas/meta.sql", "meta schema（调度元数据）"),
|
||
("db/etl_feiqiu/schemas/ods.sql", "ods schema（原始数据）"),
|
||
("db/etl_feiqiu/schemas/dwd.sql", "dwd schema（明细数据）"),
|
||
("db/etl_feiqiu/schemas/core.sql", "core schema（跨门店标准化）"),
|
||
("db/etl_feiqiu/schemas/dws.sql", "dws schema（汇总数据）"),
|
||
("db/etl_feiqiu/schemas/app.sql", "app schema（RLS 视图层）"),
|
||
]
|
||
|
||
success_count = 0
|
||
fail_count = 0
|
||
for filepath, label in schema_files:
|
||
if execute_sql_file(conn, filepath, label):
|
||
success_count += 1
|
||
else:
|
||
fail_count += 1
|
||
|
||
# 种子数据
|
||
seed_files = [
|
||
("db/etl_feiqiu/seeds/seed_ods_tasks.sql", "种子：ODS 任务"),
|
||
("db/etl_feiqiu/seeds/seed_scheduler_tasks.sql", "种子：调度任务"),
|
||
("db/etl_feiqiu/seeds/seed_dws_config.sql", "种子：DWS 配置"),
|
||
("db/etl_feiqiu/seeds/seed_index_parameters.sql", "种子：指数参数"),
|
||
]
|
||
for filepath, label in seed_files:
|
||
if execute_sql_file(conn, filepath, label):
|
||
success_count += 1
|
||
else:
|
||
fail_count += 1
|
||
|
||
conn.close()
|
||
print(f"\netl_feiqiu 完成: {success_count} 成功, {fail_count} 失败")
|
||
return fail_count == 0
|
||
|
||
|
||
def init_zqyy_app():
|
||
"""初始化 zqyy_app 数据库：schema + 迁移 + 种子"""
|
||
print("\n=== 初始化 zqyy_app 数据库 ===")
|
||
conn = psycopg2.connect(
|
||
host=DB_HOST, port=DB_PORT, user=DB_USER,
|
||
password=DB_PASSWORD, dbname="zqyy_app"
|
||
)
|
||
conn.autocommit = False
|
||
|
||
files = [
|
||
("db/zqyy_app/schemas/init.sql", "zqyy_app schema（用户/RBAC）"),
|
||
("db/zqyy_app/migrations/20250715_create_admin_web_tables.sql", "迁移：admin_web 表"),
|
||
("db/zqyy_app/seeds/admin_web_seed.sql", "种子：默认管理员"),
|
||
]
|
||
|
||
success_count = 0
|
||
fail_count = 0
|
||
for filepath, label in files:
|
||
if execute_sql_file(conn, filepath, label):
|
||
success_count += 1
|
||
else:
|
||
fail_count += 1
|
||
|
||
conn.close()
|
||
print(f"\nzqyy_app 完成: {success_count} 成功, {fail_count} 失败")
|
||
return fail_count == 0
|
||
|
||
|
||
if __name__ == "__main__":
|
||
print("开始初始化数据库...")
|
||
r1 = init_etl_feiqiu()
|
||
r2 = init_zqyy_app()
|
||
if r1 and r2:
|
||
print("\n✓ 全部初始化成功")
|
||
else:
|
||
print("\n✗ 部分初始化失败，请检查上方错误信息")
|
||
sys.exit(1)
|