1 import os
2 import sys
3 import json
4 import logging
5 import win32serviceutil
6 import win32service
7 import win32event
8 import servicemanager
9 import time
10 import threading
11 # import platform
12 import struct
13 import datetime
14 import math
15 import mysql.connector
16 import pyodbc
17 import sqlite3
18 import traceback
19 # import pymssql
20 # import psycopg2
21 from watchdog.observers import Observer
22 from watchdog.events import FileSystemEventHandler
23
24
25 class ConfigChangeHandler(FileSystemEventHandler):
26 """配置文件变更处理器"""
27
28 def __init__(self, callback, logger):
29 super().__init__()
30 self.callback = callback
31 self.logger = logger
32
33 def on_modified(self, event):
34 if not event.is_directory and event.src_path.endswith('.json'):
35 self.logger.info(f"检测到配置文件变更: {event.src_path}")
36 self.callback()
37
38
39 class DatabaseService(win32serviceutil.ServiceFramework):
40 _svc_name_ = "EnhancedDatabaseService"
41 _svc_display_name_ = "Enhanced Database Service"
42 _svc_description_ = "支持多数据库查询、结果导出至MySql数据库和热重载配置的服务"
43
44 def __init__(self, args):
45 win32serviceutil.ServiceFramework.__init__(self, args)
46 self.hWaitStop = win32event.CreateEvent(None, 0, 0, None)
47 self.config = {}
48 self.output_db_config = {}
49 self.config_path = "C:\\ServiceConfig\\db_config.json"
50 self.logger = None
51 self.last_timestamp = None
52 self.last_datetime = None
53 self.last_date = None
54 self.timestamp_file = "C:\\ServiceData\\last_timestamp.txt"
55 self.last_config_mtime = 0
56 self.observer = None
57 self.config_lock = threading.Lock()
58 self.output_conn = None # 输出数据库连接
59
60 def _setup_logger(self):
61 """配置日志记录器"""
62 try:
63 # 确保目录存在
64 os.makedirs("C:\\ServiceData", exist_ok=True)
65
66 logger = logging.getLogger(self._svc_name_)
67 logger.setLevel(logging.INFO)
68
69 # 使用绝对路径
70 handler = logging.FileHandler("C:\\ServiceData\\service_log.log")
71 formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
72 handler.setFormatter(formatter)
73 logger.addHandler(handler)
74 return logger
75 except Exception as e:
76 # 如果日志不可用，尝试基础日志
77 logging.basicConfig(level=logging.INFO,
78 filename="C:\\ServiceData\\service_fallback.log",
79 format='%(asctime)s - %(levelname)s - %(message)s')
80 logger = logging.getLogger(self._svc_name_)
81 logger.error(f"无法创建日志文件: {str(e)}")
82 return logger
83
84 def SvcStop(self):
85 """停止服务"""
86 self.ReportServiceStatus(win32service.SERVICE_STOP_PENDING)
87 self.logger.info("接收到停止请求")
88 if self.observer:
89 self.observer.stop()
90 # 关闭输出数据库连接
91 if self.output_conn and self.output_conn.is_connected():
92 self.output_conn.close()
93 self.logger.info("已关闭输出数据库连接")
94 win32event.SetEvent(self.hWaitStop)
95
96 def SvcDoRun(self):
97 """服务主运行逻辑"""
98 try:
99 # 确保目录存在
100 os.makedirs("C:\\ServiceData", exist_ok=True)
101 os.makedirs("C:\\ServiceConfig", exist_ok=True)
102
103 # 初始化日志
104 self.logger = self._setup_logger()
105 self.logger.info("===== 服务启动开始 =====")
106 self.logger.info(f"系统架构: {self.get_system_architecture()}")
107
108 # 通知服务管理器服务已启动
109 servicemanager.LogMsg(
110 servicemanager.EVENTLOG_INFORMATION_TYPE,
111 servicemanager.PYS_SERVICE_STARTED,
112 (self._svc_name_, '')
113 )
114
115 # 加载配置和时间戳
116 self.logger.info("加载配置...")
117 self._load_config()
118 self.logger.info("配置文件验证通过")
119 self.logger.info("初始化输出数据库连接...")
120 self._init_output_db()
121 self.logger.info("输出数据库连接成功")
122 self.logger.info("加载时间戳...")
123 self._load_timestamp()
124
125 # 设置配置文件监控
126 self._setup_config_watcher()
127
128 # 进入主循环
129 self.logger.info("进入主循环...")
130 self._main_loop()
131
132 except Exception as e:
133 error_msg = f"服务启动失败: {str(e)}\n{traceback.format_exc()}"
134 if hasattr(self, 'logger') and self.logger:
135 self.logger.error(error_msg)
136 else:
137 # 如果日志不可用，尝试写入文件
138 with open("C:\\ServiceData\\service_error.log", "a") as f:
139 f.write(f"{datetime.datetime.now()}: {error_msg}\n")
140
141 # 记录服务启动失败
142 servicemanager.LogMsg(
143 servicemanager.EVENTLOG_ERROR_TYPE,
144 0, # 自定义错误代码
145 (f"服务 {self._svc_name_} 启动失败: {str(e)}",)
146 )
147 # 确保服务报告失败状态
148 self.ReportServiceStatus(win32service.SERVICE_STOPPED)
149
150 def _init_output_db(self):
151 """初始化输出数据库连接"""
152 if not self.output_db_config:
153 raise ValueError("配置文件中缺少输出数据库配置")
154
155 db_type = self.output_db_config.get("database_type", "mysql").lower()
156 if db_type != "mysql":
157 raise ValueError(f"输出数据库只支持MySQL，当前配置为: {db_type}")
158
159 try:
160 self.output_conn = mysql.connector.connect(
161 host=self.output_db_config.get("host"),
162 port=self.output_db_config.get("port", 3306),
163 user=self.output_db_config.get("username"),
164 password=self.output_db_config.get("password"),
165 database=self.output_db_config.get("database")
166 )
167 self.logger.info(
168 f"成功连接到输出数据库: {self.output_db_config.get('host')}/{self.output_db_config.get('database')}")
169 except Exception as e:
170 self.logger.error(f"输出数据库连接失败: {str(e)}")
171 raise
172
173 def _setup_config_watcher(self):
174 """设置配置文件监控"""
175 try:
176 self.observer = Observer()
177 event_handler = ConfigChangeHandler(self._load_config, self.logger)
178 config_dir = os.path.dirname(self.config_path)
179 self.observer.schedule(event_handler, config_dir, recursive=False)
180 self.observer.start()
181 self.logger.info("配置文件监听器已启动")
182 except Exception as e:
183 self.logger.error(f"启动配置文件监听器失败: {str(e)}")
184
185 def get_system_architecture(self):
186 """返回系统架构信息"""
187 try:
188 # 方法1：使用struct判断位数
189 if struct.calcsize("P") * 8 == 64:
190 return "x64"
191 return "x86"
192 except:
193 return "unknown"
194
195 def _load_config(self):
196 """加载或重载配置文件"""
197 try:
198 self.logger.info(f"尝试加载配置文件: {self.config_path}")
199 if not os.path.exists(self.config_path):
200 raise FileNotFoundError(f"配置文件不存在: {self.config_path}")
201
202 current_mtime = os.path.getmtime(self.config_path)
203 if self.last_config_mtime and current_mtime <= self.last_config_mtime:
204 self.logger.info("配置文件未修改，跳过加载")
205 return
206
207 with self.config_lock:
208 with open(self.config_path, 'r', encoding='utf-8') as f:
209 new_config = json.load(f)
210
211 # 验证配置结构
212 required_keys = ['output_database', 'database_list']
213 for key in required_keys:
214 if key not in new_config:
215 raise ValueError(f"配置文件缺少必要字段: {key}")
216
217 # 验证输出数据库配置
218 output_keys = ['host', 'username', 'password', 'database']
219 for key in output_keys:
220 if key not in new_config['output_database']:
221 raise ValueError(f"输出数据库配置缺少必要字段: {key}")
222
223 # 验证数据库列表
224 if not isinstance(new_config['database_list'], list) or len(new_config['database_list']) == 0:
225 raise ValueError("database_list 必须是非空列表")
226
227 self.config = new_config
228 self.output_db_config = new_config['output_database']
229 self.last_config_mtime = current_mtime
230 self.logger.info(f"配置重新加载成功，共 {len(self.config['database_list'])} 个数据库配置")
231 except Exception as e:
232 self.logger.error(f"加载配置失败: {str(e)}")
233 # 保留旧的配置继续运行
234 if not self.config:
235 raise
236
237 def _load_timestamp(self):
238 """加载或创建时间戳文件"""
239 try:
240 if os.path.exists(self.timestamp_file):
241 with open(self.timestamp_file, 'r') as f:
242 self.last_timestamp = float(f.read().strip())
243 self.last_datetime = datetime.datetime.fromtimestamp(self.last_timestamp).strftime('%Y-%m-%d %H:%M:%S')
244 self.last_date = datetime.datetime.fromtimestamp(self.last_timestamp).strftime('%Y-%m-%d')
245 self.logger.info(f"加载历史时间戳: {datetime.datetime.fromtimestamp(self.last_timestamp)}")
246 else:
247 self.last_timestamp = time.time()
248 self.last_datetime = time.strftime('%Y-%m-%d %H:%M:%S')
249 self.last_date = time.strftime('%Y-%m-%d')
250 with open(self.timestamp_file, 'w') as f:
251 f.write(str(self.last_timestamp))
252 self.logger.info(f"创建新时间戳: {datetime.datetime.fromtimestamp(self.last_timestamp)}")
253 except Exception as e:
254 self.logger.error(f"时间戳处理失败: {str(e)}")
255 self.last_timestamp = time.time()
256 self.last_datetime = time.strftime('%Y-%m-%d %H:%M:%S')
257 self.last_date = time.strftime('%Y-%m-%d')
258
259 def _get_access_driver(self):
260 """根据系统位数返回合适的Access驱动名称"""
261 # 判断系统位数
262 is_64bit = self.get_system_architecture() == "x64"
263
264 # 常见Access驱动名称
265 drivers = [
266 "Microsoft Access Driver (*.mdb, *.accdb)",
267 "Microsoft Access Driver (*.mdb)",
268 "Microsoft Access-Treiber (*.mdb, *.accdb)" # 德语系统
269 ]
270
271 # 尝试检测已安装的驱动
272 for driver in drivers:
273 try:
274 test_db = "C:\\Windows\\Temp\\test_access_connection.accdb"
275 conn = pyodbc.connect(f"DRIVER={{{driver}}};DBQ={test_db};")
276 conn.close()
277 self.logger.info(f"检测到可用Access驱动: {driver}")
278 return driver
279 except:
280 continue
281
282 # 如果都失败，返回默认驱动
283 default_driver = "Microsoft Access Driver (*.mdb, *.accdb)"
284 self.logger.warning(f"无法检测Access驱动，使用默认驱动: {default_driver}")
285 return default_driver
286
287 def _get_db_connection(self, config, db_type=None):
288 """获取数据库连接"""
289 if not db_type:
290 db_type = config.get("database_type", "").lower()
291
292 try:
293 if db_type == "mysql":
294 return mysql.connector.connect(
295 host=config.get("host"),
296 port=config.get("port", 3306),
297 user=config.get("username"),
298 password=config.get("password"),
299 database=config.get("database")
300 )
301 elif db_type == "access":
302 # 获取合适的Access驱动
303 driver = config.get("driver") or self._get_access_driver()
304
305 # 构建连接字符串
306 db_path = config.get("db_path")
307 if not os.path.exists(db_path):
308 raise FileNotFoundError(f"Access数据库文件不存在: {db_path}")
309
310 conn_str = (
311 f'DRIVER={{{driver}}};'
312 f'DBQ={db_path};'
313 )
314
315 # 添加密码（如果配置了）
316 if config.get("db_pwd"):
317 conn_str += f'PWD={config.get("db_pwd")};'
318
319 # 32位系统可能需要额外的参数
320 if self.get_system_architecture() == "x86":
321 conn_str += 'ExtendedAnsiSQL=1;'
322
323 self.logger.info(f"使用Access连接字符串: {conn_str}")
324 return pyodbc.connect(conn_str)
325 # elif db_type in ["sqlserver", "mssql"]:
326 # return pymssql.connect(
327 # server=config.get("host"),
328 # port=config.get("port", 1433),
329 # user=config.get("username"),
330 # password=config.get("password"),
331 # database=config.get("database")
332 # )
333 # elif db_type in ["postgresql", "postgres"]:
334 # return psycopg2.connect(
335 # host=config.get("host"),
336 # port=config.get("port", 5432),
337 # user=config.get("username"),
338 # password=config.get("password"),
339 # dbname=config.get("database")
340 # )
341 elif db_type == "sqlite":
342 return sqlite3.connect(config.get("db_path"))
343 else:
344 raise ValueError(f"不支持的数据库类型: {db_type}")
345 except Exception as e:
346 self.logger.error(f"数据库连接失败({db_type}): {str(e)}")
347 raise
348
349 def _write_to_output_db(self, table_name, data, columns):
350 """将数据写入输出数据库，自动创建或更新表结构"""
351 if not data:
352 self.logger.info(f"表 {table_name} 无新数据写入")
353 return 0
354
355 try:
356 cursor = self.output_conn.cursor()
357 # 确保没有未读结果
358 self._consume_unread_results(cursor)
359
360 # 检查表是否存在
361 table_exists = True
362 try:
363 cursor.execute(f"SELECT 1 FROM `{table_name}` LIMIT 0")
364 # 消费结果
365 cursor.fetchall()
366 except Exception:
367 table_exists = False
368
369 # 如果表不存在，创建新表
370 if not table_exists:
371 # 创建表的列定义
372 column_definitions = ["auto_id INT AUTO_INCREMENT PRIMARY KEY"]
373
374 # 添加数据列（全部为TEXT类型）
375 for idx, col in enumerate(columns):
376 # 避免保留列名冲突
377 col_name = col
378 if col.lower() in ["auto_id", "import_timestamp"]:
379 col_name = f"src_{col}"
380 self.logger.warning(f"列名 '{col}' 与保留名冲突，重命名为 '{col_name}'")
381
382 column_definitions.append(f"`{col_name}` TEXT")
383
384 # 添加时间戳列
385 column_definitions.append("import_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
386
387 # 创建表
388 create_table_sql = f"""
389 CREATE TABLE `{table_name}` (
390 {', '.join(column_definitions)}
391 )
392 """
393 cursor.execute(create_table_sql)
394 self.logger.info(f"创建新表: {table_name} 包含 {len(columns)} 列")
395
396 # 更新列映射
397 columns = [col_name.replace("src_", "") if "src_" in col_name else col_name
398 for col_name in columns]
399
400 # 检查并添加缺失列
401 if table_exists:
402 # 获取现有列
403 cursor.execute(f"SHOW COLUMNS FROM `{table_name}`")
404 existing_columns = [col[0].lower() for col in cursor.fetchall()]
405
406 # 添加缺失列
407 for col in columns:
408 col_lower = col.lower()
409 # 避免保留列名冲突
410 if col_lower in ["auto_id", "import_timestamp"]:
411 col = f"src_{col}"
412 col_lower = col.lower()
413
414 # 跳过已存在列
415 if col_lower in existing_columns:
416 continue
417
418 # 添加新列
419 add_col_sql = f"ALTER TABLE `{table_name}` ADD COLUMN `{col}` TEXT"
420 try:
421 cursor.execute(add_col_sql)
422 self.logger.info(f"添加新列: {table_name}.{col}")
423 except Exception as e:
424 self.logger.warning(f"添加列失败 {col}: {str(e)}")
425
426 # 准备插入数据
427 insert_columns = []
428 for col in columns:
429 # 处理列名冲突
430 if col.lower() in ["auto_id", "import_timestamp"]:
431 col = f"src_{col}"
432 insert_columns.append(f"`{col}`")
433
434 # 构建插入SQL
435 placeholders = ', '.join(['%s'] * len(insert_columns))
436 insert_sql = f"""
437 INSERT INTO `{table_name}` ({', '.join(insert_columns)})
438 VALUES ({placeholders})
439 """
440
441 # 执行批量插入
442 batch_size = 1000
443 inserted_count = 0
444 total_rows = len(data)
445
446 for i in range(0, total_rows, batch_size):
447 batch = data[i:i + batch_size]
448 try:
449 # 转换为MySQL可接受的格式
450 converted_batch = []
451 for row in batch:
452 converted_row = []
453 for value in row:
454 # 空值处理
455 if value is None:
456 converted_row.append(None)
457 # 处理datetime对象
458 elif isinstance(value, datetime.datetime):
459 converted_row.append(value.strftime("%Y-%m-%d %H:%M:%S"))
460 # 处理日期对象
461 elif isinstance(value, datetime.date):
462 converted_row.append(value.strftime("%Y-%m-%d"))
463 # 处理时间对象
464 elif isinstance(value, datetime.time):
465 converted_row.append(value.strftime("%H:%M:%S"))
466 # 处理布尔值
467 elif isinstance(value, bool):
468 converted_row.append(1 if value else 0)
469 # 处理浮点数NaN
470 elif isinstance(value, float) and math.isnan(value):
471 converted_row.append(None)
472 # 处理其他类型
473 else:
474 converted_row.append(str(value) if not isinstance(value, (int, float)) else value)
475 converted_batch.append(tuple(converted_row))
476
477 cursor.executemany(insert_sql, converted_batch)
478 inserted_count += len(converted_batch)
479 self.logger.debug(f"插入批次: {i}-{i + len(converted_batch)}/{total_rows}")
480 except Exception as e:
481 self.logger.error(f"批量插入失败: {str(e)}")
482 # 尝试逐行插入以确定问题行
483 for j, row in enumerate(converted_batch if 'converted_batch' in locals() else batch):
484 try:
485 cursor.execute(insert_sql, row)
486 inserted_count += 1
487 except Exception as row_error:
488 self.logger.error(f"行插入失败 (行 {i + j}): {str(row_error)}")
489 # 记录问题行但不包含敏感数据
490 self.logger.debug(f"问题行索引: {i + j}, 数据类型: {[type(v) for v in row]}")
491
492 self.output_conn.commit()
493 self.logger.info(f"成功写入 {inserted_count} 条数据到表 {table_name}")
494 return inserted_count
495
496 except Exception as e:
497 self.logger.error(f"写入输出数据库失败: {str(e)}\n{traceback.format_exc()}")
498 # 发生错误时回滚
499 try:
500 self.output_conn.rollback()
501 except:
502 pass
503 return 0
504 finally:
505 # 确保关闭游标
506 if cursor:
507 cursor.close()
508
509 def _consume_unread_results(self, cursor):
510 try:
511 count = 0
512 while cursor.nextset():
513 count += 1
514 if count > 0:
515 self.logger.debug(f"消费了 {count} 个未读结果集")
516 except Exception as e:
517 self.logger.warning(f"清理未读结果时出错: {str(e)}")
518
519
520 def _execute_query(self, db_config, query_name, query_info):
521 """执行单个查询并处理结果"""
522 conn = None
523 try:
524 sql_template = query_info.get("sql", "")
525 if not sql_template:
526 self.logger.warning(f"查询[{query_name}]的SQL为空，跳过")
527 return False
528
529 # 替换时间戳占位符
530 sql = sql_template.replace("{last_timestamp}", str(self.last_timestamp))
531 sql = sql.replace("{last_datetime}", str(self.last_datetime))
532 sql = sql.replace("{last_date}", str(self.last_date))
533
534 # 获取目标表名
535 target_table = query_info.get("target_table", query_name)
536
537 # 获取数据库连接
538 conn = self._get_db_connection(db_config)
539 cursor = conn.cursor()
540
541 # 执行查询
542 self.logger.info(f"正在执行查询: {query_name}")
543 cursor.execute(sql)
544 results = cursor.fetchall()
545
546 if not results:
547 self.logger.info(f"查询[{query_name}]返回0条记录")
548 return True
549
550 # 获取列名
551 columns = [col[0].strip() for col in cursor.description]
552 columns = [col if col else f"col_{idx}" for idx, col in enumerate(columns)]
553
554 # 写入输出数据库
555 inserted_count = self._write_to_output_db(target_table, results, columns)
556 self.logger.info(f"查询[{query_name}]处理完成，插入{inserted_count}条数据")
557 return inserted_count > 0
558 except Exception as e:
559 self.logger.error(f"查询执行失败 [{query_name}]: {str(e)}\n{traceback.format_exc()}")
560 return False
561 finally:
562 if conn:
563 conn.close()
564
565 def _execute_and_update(self):
566 """执行所有查询并更新时间戳"""
567 if not self.config:
568 self.logger.warning("配置未加载，跳过执行")
569 return False
570
571 database_list = self.config.get("database_list", [])
572 if not database_list:
573 self.logger.warning("未配置任何数据库")
574 return False
575
576 success = True
577 total_inserted = 0
578 total_queries = 0
579 successful_queries = 0
580
581 # 遍历每个数据库配置
582 for db_index, db_config in enumerate(database_list):
583 db_type = db_config.get("database_type", "unknown")
584 db_name = db_config.get("database", f"DB_{db_index}")
585 self.logger.info(f"开始处理数据库 #{db_index + 1} ({db_type}/{db_name})")
586
587 queries = db_config.get("queries", {})
588 if not queries:
589 self.logger.info(f"数据库 #{db_index + 1} 未配置查询，跳过")
590 continue
591
592 # 执行该数据库的所有查询
593 for name, info in queries.items():
594 total_queries += 1
595 if self._execute_query(db_config, name, info):
596 successful_queries += 1
597 else:
598 success = False
599 self.logger.warning(f"查询[{name}]执行失败，继续处理其他查询")
600
601 # 统计结果
602 self.logger.info(f"查询执行完成: {successful_queries}/{total_queries} 个查询成功")
603
604 # 仅当所有查询成功时才更新时间戳
605 if success and total_queries > 0:
606 new_timestamp = time.time()
607 with open(self.timestamp_file, 'w') as f:
608 f.write(str(new_timestamp))
609 self.last_timestamp = new_timestamp
610 self.logger.info(f"更新时间戳: {datetime.datetime.fromtimestamp(new_timestamp)}")
611 return True
612
613 self.logger.warning("部分查询失败，跳过更新时间戳")
614 return False
615
616 def _main_loop(self):
617 """服务主循环"""
618 self.logger.info("服务主循环开始")
619
620 # 报告服务正在运行
621 self.ReportServiceStatus(win32service.SERVICE_RUNNING)
622
623 while True:
624 # 检查停止事件
625 stop_event = win32event.WaitForSingleObject(self.hWaitStop, 5000)
626 if stop_event == win32event.WAIT_OBJECT_0:
627 self.logger.info("服务停止请求接收")
628 break
629
630 try:
631 # 重新加载配置（如果修改过）
632 self._load_config()
633
634 # 如果需要重新初始化输出数据库连接
635 if self.output_conn and not self.output_conn.is_connected():
636 self.logger.warning("输出数据库连接断开，尝试重新连接")
637 self._init_output_db()
638
639 # 执行查询并同步数据
640 self.logger.info("开始执行数据同步...")
641 sync_success = self._execute_and_update()
642
643 if sync_success:
644 self.logger.info("数据同步成功完成")
645 else:
646 self.logger.warning("数据同步过程中出现错误")
647 except Exception as e:
648 self.logger.error(f"主循环错误: {str(e)}\n{traceback.format_exc()}")
649
650 # 按配置间隔等待
651 interval = self.config.get("interval_seconds", 60) if self.config else 60
652 self.logger.info(f"等待 {interval} 秒后进行下一次同步")
653 time.sleep(interval)
654
655 self.logger.info("服务主循环结束")
656 # 报告服务已停止
657 self.ReportServiceStatus(win32service.SERVICE_STOPPED)
658
659
660 if __name__ == '__main__':
661 # 添加启动日志
662 try:
663 os.makedirs("C:\\ServiceData", exist_ok=True)
664 with open("C:\\ServiceData\\service_bootstrap.log", "a") as f:
665 f.write(f"{datetime.datetime.now()}: 服务启动参数: {sys.argv}\n")
666 except Exception as e:
667 print(f"创建启动日志失败: {str(e)}")
668
669 if len(sys.argv) == 1:
670 try:
671 servicemanager.Initialize()
672 servicemanager.PrepareToHostSingle(DatabaseService)
673 servicemanager.StartServiceCtrlDispatcher()
674 except Exception as e:
675 with open("C:\\ServiceData\\service_bootstrap.log", "a") as f:
676 f.write(f"{datetime.datetime.now()}: 服务调度失败: {str(e)}\n{traceback.format_exc()}")
677 raise
678 else:
679 win32serviceutil.HandleCommandLine(DatabaseService)
浙公网安备 33010602011771号