import os
import sqlite3
import shutil
import tempfile
import time
from datetime import datetime, timedelta
from pathlib import Path
import mysql.connector
from ping3 import ping
# ========================
# 配置区域
# ========================
# 全局最小时间门槛（不会早于这个时间同步）
MIN_TIME = '2025-09-01 00:00:00'
# 分播机配置：IP -> (LineNumber, 数据库UNC路径或映射路径)
SORTERS = {
'192.168.110.56': (1, r'Y:\sorting.db'), # 示例：也可写成 r'\\192.168.110.56\share\sorting.db'
'192.168.110.60': (9, r'Z:\sorting.db'),
}
# MySQL 配置
MYSQL_CONFIG = {
'host': 'localhost',
'user': 'admin',
'password': 'gapinyc',
'database': 'board_db',
'auth_plugin': 'mysql_native_password',
'charset': 'utf8mb4'
}
# 要同步的表名列表
TABLES_TO_SYNC = [
'AppSortingTasks', # 最顶层（无外部依赖）
'AppOperationLogs', # 可能依赖 Tasks，也可能独立
'AppReportRecords', # 通常独立或依赖 Tasks
'AppSortingTaskItems', # 依赖 Tasks
'AppSortingTaskImages', # 通常依赖 Items 或 Tasks
'AppSortingTaskSkus', # 依赖 Items
'AppSortingTaskBoxes' # 可能依赖 Items 或 Tasks
]
# ========================
# 工具函数
# ========================
def is_valid_sqlite_db(file_path):
"""检查文件是否为有效的 SQLite 数据库"""
try:
with open(file_path, 'rb') as f:
header = f.read(16)
return header == b'SQLite format 3\x00'
except Exception as e:
print(f"[CHECK ERROR] 无法读取文件头 {file_path}: {e}")
return False
def is_file_accessible(path_str):
"""检测共享路径上的数据库文件是否存在且可读"""
try:
db_path = Path(path_str)
if not db_path.exists():
print(f"[PATH ERROR] 文件不存在: {db_path}")
return False
if not db_path.is_file():
print(f"[PATH ERROR] 路径不是文件: {db_path}")
return False
if db_path.stat().st_size == 0:
print(f"[PATH ERROR] 文件大小为0字节: {db_path}")
return False
return True
except Exception as e:
print(f"[PATH ERROR] 访问路径异常 {path_str}: {e}")
return False
def connect_sqlite_remote_safely(db_path):
"""
安全连接远程 SQLite 数据库：先复制到本地临时文件再打开
返回: (connection, temp_db_path) 元组 或 (None, None)
"""
temp_db = None
try:
# 创建临时文件
with tempfile.NamedTemporaryFile(suffix='.db', delete=False) as tmp:
temp_db = tmp.name
print(f"📥 正在复制数据库: {db_path} → {temp_db}")
shutil.copy2(db_path, temp_db)
# 验证副本是否为有效 SQLite 文件
if not is_valid_sqlite_db(temp_db):
print(f"[ERROR] 复制后的数据库无效: {temp_db}")
os.unlink(temp_db)
return None, None
# 尝试连接
conn = sqlite3.connect(f'file:{temp_db}?mode=ro', uri=True, timeout=10.0)
conn.row_factory = sqlite3.Row # 支持按列名访问
print(f"✅ 成功加载数据库副本: {temp_db}")
return conn, temp_db # 成功则返回 conn 和路径
except Exception as e:
print(f"[ERROR] 复制或连接数据库失败 {db_path}: {e}")
# 清理临时文件（如果存在）
if temp_db and os.path.exists(temp_db):
try:
os.unlink(temp_db)
print(f"🗑️ 清理失败的临时数据库: {temp_db}")
except Exception as clean_e:
print(f"[WARN] 无法删除临时文件 {temp_db}: {clean_e}")
return None, None
def close_sqlite_connection(conn, temp_db_path):
"""安全关闭 SQLite 连接并删除临时文件"""
if conn:
try:
conn.close()
print("🔒 SQLite 连接已关闭")
except Exception as e:
print(f"[WARN] 关闭 SQLite 连接时出错: {e}")
# 删除临时文件
if temp_db_path and os.path.exists(temp_db_path):
try:
os.unlink(temp_db_path)
print(f"🗑️ 已删除临时数据库: {temp_db_path}")
except Exception as e:
print(f"[WARN] 无法删除临时文件 {temp_db_path}: {e}")
def sync_table_from_sqlite_to_mysql(sorter_ip, line_number, sqlite_path, mysql_conn, table_name):
sqlite_conn = None
temp_db = None
mysql_cursor = None
try:
if not is_file_accessible(sqlite_path):
print(f"[FAIL] 源文件不可访问: {sqlite_path}")
return False
# ✅ 新式解包返回值
sqlite_conn, temp_db = connect_sqlite_remote_safely(sqlite_path)
if not sqlite_conn:
print(f"[FAIL] 无法安全打开数据库: {sqlite_path}")
return False
mysql_cursor = mysql_conn.cursor()
target_table = f"AI_{table_name}"
twelve_hours_ago = (datetime.now() - timedelta(hours=12)).strftime('%Y-%m-%d %H:%M:%S')
filter_time = max(MIN_TIME, twelve_hours_ago)
print(f" 🕒 同步时间窗口起点: {filter_time}")
query = f"SELECT * FROM `{table_name}` WHERE creationtime >= ?"
try:
rows = list(sqlite_conn.execute(query, (filter_time,)))
except sqlite3.Error as e:
print(f"[ERROR] 查询表 {table_name} 出错: {e}")
return False
if not rows:
print(f" ⚠️ 表 {table_name} 无符合 creationtime >= '{filter_time}' 的数据")
return True
print(f" 📥 从 {table_name} 读取到 {len(rows)} 条数据")
# === DEBUG: 打印原始 keys ===
raw_keys = rows[0].keys()
print(f"🔍 原始列名列表: {raw_keys}")
# 检查是否有非法列名
for k in raw_keys:
if not isinstance(k, str):
print(f"[WARN] 非字符串列名: {k} (类型: {type(k)})")
if k.strip() == 'I':
print(f"[CRITICAL] 发现危险列名: '{k}' ← 这会导致 SQL 错误！")
if not k.isidentifier():
print(f"[WARN] 非法标识符列名: '{k}'")
columns = [k for k in raw_keys]
all_columns_with_lineno = columns + ['LineNumber']
placeholders = ', '.join(['%s'] * len(all_columns_with_lineno))
insert_columns = ', '.join([f"`{col}`" for col in all_columns_with_lineno])
update_assignments = ', '.join([f"`{col}` = VALUES(`{col}`)" for col in all_columns_with_lineno])
insert_sql = f"""
INSERT INTO {target_table} ({insert_columns})
VALUES ({placeholders})
ON DUPLICATE KEY UPDATE {update_assignments}
"""
data_to_insert = [tuple(list(row) + [line_number]) for row in rows]
mysql_cursor.execute("START TRANSACTION")
three_hours_ago = (datetime.now() - timedelta(hours=3)).strftime('%Y-%m-%d %H:%M:%S')
delete_sql = f"DELETE FROM {target_table} WHERE creationtime >= %s"
mysql_cursor.execute(delete_sql, (three_hours_ago,))
deleted_count = mysql_cursor.rowcount
if deleted_count > 0:
print(f" [TRUNCATE] 删除 {target_table} 中 {deleted_count} 条旧记录 (>= {three_hours_ago})")
mysql_cursor.executemany(insert_sql, data_to_insert)
inserted_count = mysql_cursor.rowcount
mysql_conn.commit()
print(f" ✅ 成功向 {target_table} 插入/更新 {inserted_count} 条数据")
return True
except Exception as e:
print(f"[ERROR] 同步表 {table_name} 时发生异常: {e}")
if mysql_conn:
mysql_conn.rollback()
return False
finally:
close_sqlite_connection(sqlite_conn, temp_db) # ✅ 显式传递 temp_db
if mysql_cursor:
mysql_cursor.close()
def check_sorter_online(ip):
"""检查 IP 是否可达"""
try:
delay = ping(ip, timeout=2)
return delay is not None
except:
return False
def sync_single_sorter(ip, line_number, sqlite_path):
"""同步一台分播机的所有表"""
print(f"\n🔍 开始检查分播机 Line{line_number} ({ip})...")
if not check_sorter_online(ip):
print(f"🔴 分播机 Line{line_number} ({ip}) 网络不通")
return False
print(f"✅ 分播机 Line{line_number} ({ip}) 网络通畅")
if not is_file_accessible(sqlite_path):
print(f"❌ 分播机数据库文件不可访问: {sqlite_path}")
return False
print(f"✅ 分播机数据库文件正常: {sqlite_path}")
success = True
mysql_conn = None
try:
mysql_conn = mysql.connector.connect(**MYSQL_CONFIG)
if not mysql_conn.is_connected():
print("[FAIL] 无法连接到MySQL")
return False
for table_name in TABLES_TO_SYNC:
result = sync_table_from_sqlite_to_mysql(ip, line_number, sqlite_path, mysql_conn, table_name)
if not result:
print(f"⚠️ 同步表 {table_name} 失败")
success = False
except Exception as e:
print(f"[ERROR] MySQL 操作异常: {e}")
success = False
finally:
if mysql_conn and mysql_conn.is_connected():
mysql_conn.close()
if success:
print(f"🟢 分播机 Line{line_number} 所有表同步成功")
else:
print(f"🔴 分播机 Line{line_number} 部分表同步失败")
return success
def main():
"""主函数：遍历所有分播机进行同步"""
print("🚀 开始执行分播机数据同步任务...")
overall_success = True
for ip, (line_number, db_path) in SORTERS.items():
try:
result = sync_single_sorter(ip, line_number, db_path)
if not result:
overall_success = False
except Exception as e:
print(f"[CRITICAL] 同步分播机 {ip} 时发生未捕获异常: {e}")
overall_success = False
print("\n" + ("✅" if overall_success else "❌") + " 全部同步任务完成")
if __name__ == "__main__":
main()
改造以上代码，解决处理每个表都要重新复制和加载数据库，请提供完整代码
最新发布
博客指出在程序执行中，finally块要早于try块里的return 0执行，这是关于程序执行顺序的重要信息，在编程中需加以注意。
扫一扫
8万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
