End-side data sensing and whole-process automatic acquisition method for tunnel safety monitoring

Abstract

The invention belongs to the technical field of tunnel safety monitoring, and relates to an end-side data perception and full-process automatic acquisition method for tunnel safety monitoring, which comprises a local timing automatic data exporting step and a data storage, integration and transmission step. In the local timed automatic data exporting step, a Python script and a windows task plan program are used, data exporting, integration, storage and transmission are achieved through the Python script, timed tasks are configured in combination with the windows task plan program, the Python script is executed regularly, and whole-process unmanned end side data collection is achieved; in the data storage, integration and transmission step, the exported Excel data file of each channel is subjected to standardization processing and persistent storage and transmission; according to the method, the Python automatic script is adopted to replace the traditional manual channel-by-channel export operation; timing triggering is achieved in combination with a Windows task planning program, 1-24-hour user-defined interval export is supported, manual on-duty starting is not needed, and data missing caused by manual operation errors is avoided.

Description

Technical Field

[0001] This invention belongs to the field of tunnel safety monitoring technology, specifically relating to a method for end-side data sensing and full-process automatic acquisition in tunnel safety monitoring. Background Technology

[0002] Tunnel safety monitoring is a technical method that uses sensors, data processing technology, and other means to monitor and assess the structure, environment, and operational status of tunnels in real time. Its core purpose is to identify potential safety hazards within the tunnel so that preventive and control measures can be taken in a timely manner to ensure the safety and stability of the tunnel during construction and operation.

[0003] In the tunnel safety monitoring system, data acquisition and processing must follow a closed-loop process of "sensing → collection → transmission → analysis". The data acquisition instrument, acting as the data hub, periodically collects data from various sensors and then uploads the collected data to the cloud via a 4G network. Alternatively, it can be connected to a host computer via a serial port. The host computer software can then be launched to manually export data from each channel of the data acquisition instrument. Each exported data entry is saved locally as a separate .xlsx file.

[0004] However, in scenarios with poor network environments inside tunnels, the data collected by the data acquisition device cannot be uploaded to the cloud. It is necessary to use host computer software to directly export the data. However, manual data export is inefficient. Each exported channel's data exists as an independent xlsx file, resulting in data fragmentation, which is not conducive to subsequent transmission using communication methods such as LoRa. Furthermore, there is no local structured database management.

[0005] Therefore, there is a need for a method or device that does not rely on manual data export and can improve the data export efficiency of tunnel safety monitoring, so as to solve the technical problems of complex and inefficient operation of existing tunnel safety monitoring. Summary of the Invention

[0006] This invention provides the following technical solution: a method for end-side data perception and full-process automatic acquisition of tunnel safety monitoring, including a local timed automatic data export step and a data storage, integration, and transmission step.

[0007] The local scheduled automatic data export process uses a solution combining Python scripts and Windows Task Scheduler. The Python scripts are used to export, integrate, store, and transmit data, while the Windows Task Scheduler is used to configure scheduled tasks to execute the Python scripts periodically, achieving fully unmanned edge data collection.

[0008] The data storage, integration, and transmission steps involve standardizing, persistently storing, and transmitting the exported Excel data files from each channel.

[0009] Preferably, in the local timed automatic data export step, the data export part uses a Python-based automated script to batch export sensor data from the H960GC data acquisition software, and realizes a fully automated data export process by simulating user interface operation.

[0010] More preferably, the Python-based automation script controls the H960GC application through the pywinauto library; first, it locates and activates the target application window by the window title, ensuring that the window is maximized, and then establishes a connection with the application to obtain the window object for subsequent control operations.

[0011] More preferably, the control timing of the Python-based automation script is as follows: Timing 1: The script calculates the time parameter settings for the query time range, using the current time as the end time and the start time as the current time minus the preset time increment; input the time parameters in the application interface to ensure the accuracy of the data query range.

[0012] Sequence 2: Process channel groups 1-8, performing the following sequence for each channel: precisely locate and check the checkbox for the current channel using the control's auto_id; click the "Read Data" button to start the data export process; wait for a period of time to ensure the data is completely exported; use pyautogui to simulate pressing the Enter key to close the completion prompt dialog box; finally, uncheck the current channel to prepare for processing the next channel.

[0013] In sequence 3, after processing the 1-8 channel group, the script performs a tab switching operation. By locating the tab control and selecting the "Host Channel: 9-16" tab, it switches to the 9-16 channel group interface. After the interface is fully loaded, the 9-16 channel group is processed according to the same operation sequence as in sequence 2.

[0014] Timing 4: After processing channel group 9-16, switch back to channel group 1-8 to prepare for the next script run.

[0015] Preferably, the data storage, integration, and transmission steps include a data storage sub-step, a data integration sub-step, and a file transmission sub-step.

[0016] Even better, the data storage is performed step-by-step, persisting the original Excel data to a MySQL database. For different types of sensor data, corresponding database tables are created in the database, and the script provides dedicated storage functions to adapt to the specific data types and formats of each sensor.

[0017] More preferably, the data integration involves scanning Excel files in a specified directory step by step, automatically identifying the sensor type based on the file name pattern, and extracting the sensor type identifier from a specific location in the file name; for each file, reading the Excel spreadsheet data, extracting the device ID as the unique identifier for the sensor, and then converting each row of data into a dictionary format; finally, grouping the data according to the sensor type and device ID to construct a unified JSON data structure, ensuring the standardization and consistency of data organization.

[0018] More preferably, the file transfer is performed in steps by directly connecting the industrial control computer and the Raspberry Pi with a network cable, configuring a static IP, establishing an SSH connection through a script, and transferring the file to the designated container through Docker commands. Subsequently, the Raspberry Pi transfers the data to the outside world through LoRa. The transfer process uses a pipeline method, passing the local file content to the tee command in the container through standard input, thereby realizing the remote creation and saving of files.

[0019] Even better, the data storage, integration, and transmission steps are equipped with error handling mechanisms, and each processing step includes exception capture to ensure that the failure of a single file processing will not affect the entire process; database operations adopt transaction management and support rollback operations to ensure data consistency; the system also provides detailed processing logs, including information such as file processing status, number of database records inserted, file deletion status, and transmission results, which facilitates monitoring and troubleshooting.

[0020] The beneficial effects of this invention are: 1. This invention replaces the traditional manual channel-by-channel export operation with an automated Python script; it combines with Windows Task Scheduler to achieve timed triggering, supports custom intervals of 1-24 hours for export, requires no manual intervention, and achieves 100% data export integrity rate after 24 hours of trial operation, avoiding data loss caused by human error (such as missed channel selection or incorrect time input); the script supports batch export of all channels and automatic log recording, leaving real-time traces of operation steps for easy subsequent investigation; therefore, this invention improves the data export efficiency of tunnel safety monitoring and significantly reduces the reliance on manual labor in tunnel safety monitoring.

[0021] 2. This invention establishes a local MySQL database and designs dedicated structured data tables for different types of sensors, replacing traditional scattered Excel file storage. This significantly improves data storage stability and avoids the risk of file loss or corruption. This invention supports rapid retrieval by multiple dimensions such as "sensor type, time range, and channel number," shortening historical data query time and reducing the time cost of subsequent data tracing and analysis. Therefore, this invention can realize local structured storage of tunnel safety monitoring data, ensuring the security and convenient retrieval of tunnel safety monitoring data.

[0022] 3. This invention uses Python scripts to automatically integrate scattered Excel data from various channels into a unified JSON format file, standardizing field naming (including common fields such as acquisition time, channel number, and device ID, as well as specific monitoring fields for each sensor), thus solving the problem of inconsistent data formats and chaotic fields in traditional sensors. Therefore, this invention achieves standardized integration of tunnel safety monitoring data files, eliminating the problem of fragmented formats in tunnel safety monitoring.

[0023] 4. This invention designs a wired direct connection transmission scheme of "industrial control computer → Raspberry Pi → LoRa" for scenarios without WiFi or with weak network, such as tunnels. Reliable data transmission from the industrial control computer to the Raspberry Pi is achieved in network-disconnected environments through static IP configuration and SSH commands. The Raspberry Pi acts as an intermediate storage node. After receiving and integrating the JSON data, it can achieve long-distance, low-power secondary transmission via the LoRa module (adapting to long-distance tunnels and complex electromagnetic environments), forming a network-disconnection fault-tolerant mechanism of "local storage + hierarchical transmission." Even if the backbone network is interrupted, the data can be temporarily stored on the Raspberry Pi and automatically retransmitted after the network is restored, ensuring uninterrupted data transmission. Therefore, this invention constructs a network-disconnect-compatible hierarchical transmission system, ensuring the continuity of data transmission for tunnel safety monitoring. Attached Figure Description

[0024] Figure 1 This is a flowchart of the data export method for an end-side data sensing and fully automated acquisition method for tunnel safety monitoring according to the present invention. Figure 2 This is a flowchart of the data storage, integration, and transmission method of the present invention. Detailed Implementation

[0025] The related technologies of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of the present invention.

[0026] like Figures 1-2 As shown, this implementation uses a Python script and Windows Task Scheduler to achieve local, timed, automatic data export. The Python script handles data export, integration, storage, and transmission, while the Windows Task Scheduler is used to configure scheduled tasks to execute the Python script periodically, enabling fully unmanned edge data acquisition.

[0027] The data export section is an automated Python script specifically designed for batch exporting sensor data from the H960GC data acquisition software. This script automates the entire data export process by simulating user interface operations.

[0028] The script adopts a modular design and mainly includes the following functional modules: The initialization configuration module is responsible for setting basic parameters, including the query time range (default is the most recent hour, which needs to be consistent with the script execution interval) and channel grouping configuration. Channels are divided into two groups: channel groups 1-8 and channel groups 9-16. A complete logging system is also established to record the operation process and exception information.

[0029] The application control module is the core of the script, using the pywinauto library to achieve precise control over the H960GC application. First, it locates and activates the target application window using the window title, ensuring the window is maximized. Then, it establishes a connection with the application and obtains the window object for subsequent control operations.

[0030] The data export process follows strict timing control: 1. During the time parameter setting phase, the script calculates the query time range, using the current time as the end time and the start time as the current time minus a preset time increment. These time parameters are then accurately entered into the application interface to ensure the accuracy of the data query range.

[0031] 2. First, process channel groups 1-8, performing the following sequence for each channel: precisely locate and check the checkbox for the current channel using the control's auto_id; click the "Read Data" button to start the data export process; wait for a period of time (e.g., 60 seconds) to ensure the data is completely exported; use pyautogui to simulate pressing the Enter key to close the completion prompt dialog box; finally, uncheck the current channel to prepare for processing the next channel.

[0032] 3. After processing channels 1-8, the script performs a tab switching operation. By locating the tab control and selecting the "Host Channels: 9-16" tab, it switches to the channel 9-16 group interface. After waiting for a period of time (e.g., 20 seconds) to ensure the interface is fully loaded, it processes channel 9-16 group according to the same operation sequence.

[0033] 4. After processing channel group 9-16, switch back to channel group 1-8 to prepare for the next script run.

[0034] The script has a robust exception handling mechanism, with exception handling included in each critical operation step to ensure that the failure of a single channel does not affect the entire process. Furthermore, after completing all operations, the script automatically switches back to the 1-8 channel tabs, restoring the application to its initial state and preparing for the next automated run.

[0035] This design allows the data export process to be completely automated without human intervention, enabling stable and efficient batch export of multi-channel data and greatly improving the efficiency of data collection.

[0036] The example code for this implementation is shown below: from pywinauto import Application import pyautogui import time from datetime import datetime, timedelta import logging import os # Time period length TIME_DELTA = timedelta(hours=1) # Required channels - divided into two groups CHANNELS_1_8 = [2, 4, 5, 6, 8]# Channel number within the range of 1~8 CHANNELS_9_16 =

[12] # Channel number within the range of 9~16 # Set log def setup_logging(): # Log Directory log_dir = "D:\Cloud_Edge_End\ExportData\logs" if not os.path.exists(log_dir): os.makedirs(log_dir) # Log file log_filename = f"{log_dir} / data_export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.log" # Configure the logger logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s', handlers=[ logging.FileHandler(log_filename, encoding='utf-8'), logging.StreamHandler() # Also outputs to the console ] ) return logging.getLogger(__name__) logger = setup_logging() # Activate window def activate_target_window(window_title): try: # Get target window windows = pyautogui.getWindowsWithTitle(window_title) If not Windows: logger.error(f"Window with title '{window_title}' not found") return False target_window = windows[0] # Maximize window if not target_window.isMaximized: target_window.maximize() return True except Exception as e: logger.error(f"An error occurred while activating the window: {str(e)}") return False # Switch tabs def switch_tab(window, tab_name): try: tab_control = window.child_window( auto_id="tabControl2", control_type="Tab" ) tab_control.select(tab_name) logger.info(f"Switch to tab: {tab_name}") time.sleep(20) # Wait for the tab to switch to complete return True except Exception as e: logger.error(f"Failed to switch tabs: {str(e)}") return False # Export channel data def process_channels(window, channels, tab_name): """Process export operations for a specified channel group""" logger.info(f"Start processing the channel {tab_name}: {channels}") # Preload all channels checkbox channel_checkboxes = {} for chan in channels: try: channel_checkboxes[chan] = window.child_window(auto_id=f"checkTD{chan}") logger.info(f"Checkbox found for channel {chan}") except Exception as e: logger.error(f"Checkbox not found for channel {chan}: {str(e)}") # If a checkbox for a channel is not found, skip that channel. continue # Get / Read Data Button Control try: button = window.child_window(auto_id="btnReadData", control_type="Button") logger.info("Found the read data button") except Exception as e: logger.error(f"Unable to find the read data button: {str(e)}") return False # Export Data for channel_id in channels: if channel_id not in channel_checkboxes: logger.warning(f"Skip channel {channel_id} because its checkbox was not found") continue logger.info(f"Start processing channel {channel_id}") try: # Select Channel logger.info(f"Channel {channel_id} is selected") channel_checkboxes[channel_id].click() except Exception as e: logger.error(f"Failed to select channel {channel_id}: {str(e)}") continue try: # Click the "Read Data" button logger.info("Click the read data button") button.click() except Exception as e: logger.error(f"Failed to click the read data button: {str(e)}") # Uncheck the current channel try: channel_checkboxes[channel_id].click() except: pass continue time.sleep(60) # Close pop-up window pyautogui.press('enter') try: # Uncheck the channel logger.info(f"Uncheck channel {channel_id}") channel_checkboxes[channel_id].click() except Exception as e: logger.error(f"Failed to uncheck channel {channel_id}: {str(e)}") logger.info(f"Channel {channel_id} processing complete") return True def main(): # Get Time end_time = datetime.now() start_time = end_time - TIME_DELTA if not activate_target_window("H960GC_V1.4.2.3"): logger.error("Unable to activate target window, program terminated") return time.sleep(5) try: # Connect to the target application logger.info("Attempting to connect to the application...") app = Application(backend="uia").connect(title="H960GC_V1.4.2.3") logger.info("Application connection successful") except Exception as e: logger.error(f"Failed to connect to application: {str(e)}") return try: # Connection Window window = app.window(title="H960GC_V1.4.2.3") logger.info("Window object successfully acquired") except Exception as e: logger.error(f"Failed to retrieve window object: {str(e)}") return # Enter start time try: start_time_combo = window.child_window(auto_id="dtStartDate") start_time_combo.type_keys(start_time.strftime("%Y-%m-%d-%H-%M-%S-")) logger.info("Enter start time: " + start_time.strftime("%Y-%m-%d-%H-%M-%S")) except Exception as e: logger.error(f"Failed to enter start time: {str(e)}") return # Enter the end time try: end_time_combo = window.child_window(auto_id="dtEndDate") end_time_combo.type_keys(end_time.strftime("%Y-%m-%d-%H-%M-%S-")) logger.info("Enter end time: " + end_time.strftime("%Y-%m-%d-%H-%M-%S")) except Exception as e: logger.error(f"Failed to input end time: {str(e)}") return # Process channels 1-8 process_channels(window, CHANNELS_1_8, "1-8 channels") # Switch to the 9-16 channel tab if not switch_tab(window, "Host Channel:9-16"): logger.error("Unable to switch to the 9-16 channel tab, skipping 9-16 channel processing") else: # Processing channels 9-16 process_channels(window, CHANNELS_9_16, "9-16 channels") # Switch back to the 1-8 channel tab to prepare for the next run. if not switch_tab(window, "Host Channel:1-8"): logger.warning("Unable to switch back to the 1-8 channel tab") else: logger.info("Switched back to the 1-8 channel tab, ready for the next run") logger.info("All channels have been processed") if __name__ == "__main__": try: main() except Exception as e: logger.error(f"An uncaught exception occurred during program execution: {str(e)}") logger.exception("Detailed exception information:") The data storage, integration, and transmission script in this implementation is responsible for standardizing, persistently storing, and transmitting the Excel data files from each channel exported from the H960GC.

[0037] The system adopts a layered processing architecture, mainly consisting of three core modules: The data storage module is responsible for persisting the raw Excel data to a MySQL database. Corresponding database tables are created for different types of sensor data, and the script provides dedicated storage functions to adapt to the specific data types and formats of each sensor.

[0038] The data integration module enables unified processing of multi-file data. This module scans Excel files in a specified directory, automatically identifies sensor types based on filename patterns, and extracts sensor type identifiers from specific positions within the filenames. For each file, it reads the Excel spreadsheet data, extracts the device ID as a unique sensor identifier, and then converts each row of data into a dictionary format. Finally, the data is grouped according to sensor type and device ID, constructing a unified JSON data structure to ensure the standardization and consistency of data organization.

[0039] The file transfer module uses the SSH protocol to transmit data. An industrial control computer is pre-connected to the Raspberry Pi via a network cable. After configuring a static IP address, an SSH connection is established using a script. Files are then transferred to a designated container using Docker commands. Subsequently, the Raspberry Pi uses LoRa to transfer the data to the outside world. The transfer process employs a piped approach, passing local file contents to the `tee` command within the container via standard input, enabling remote file creation and saving.

[0040] The system has a robust error handling mechanism, with exception handling included in each processing step to ensure that a single file processing failure does not affect the entire workflow. Database operations employ transaction management, supporting rollback operations to guarantee data consistency. The system also provides detailed processing logs, including file processing status, number of database records inserted, file deletion status, and transmission results, facilitating monitoring and troubleshooting.

[0041] This design enables the system to efficiently process large volumes of sensor data, realizing a complete data processing chain from raw data to standardized storage and remote distribution, providing reliable data management support for sensor monitoring systems. Example code is shown below: import datetime from datetime import pandas as pd import json import glob import os from collections import defaultdict import paramiko import pymysql def get_db_connection(db_user='root', db_password='000000'): """Get database connection""" try: connection = pymysql.connect( host='localhost', user=db_user, password=db_password, database='sensor_monitoring', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor ) return connection except Exception as e: print(f"Database connection failed: {str(e)}") return None def save_earth_pressure_data(file_path, db_user='root', db_password='000000'): """Save earth pressure gauge data""" connection = get_db_connection(db_user, db_password) if not connection: return 0 try: df = pd.read_excel(file_path, sheet_name=0) inserted_count = 0 with connection.cursor() as cursor: for _, row in df.iterrows(): try: sql = """ INSERT INTO earth_pressure_data (sensor_code, channel_number, record_time, pressure_value, frequency_value, temperature) VALUES (%s, %s, %s, %s, %s, %s) """ cursor.execute(sql, ( row['device ID'], row['channel number'], row['collection time'], row['earth pressure value P(Kpa)'], row['modulo value F'], row['Temperature value T (°C)'] )) inserted_count += 1 except pymysql.IntegrityError: print("Violation of database integrity constraints") continue except Exception as e: print(f"Failed to insert earth pressure gauge record: {str(e)}") continue connection.commit() print(f"Earth pressure gauge data: Inserted {inserted_count} records") return inserted_count except Exception as e: print(f"Failed to process earth pressure gauge file {file_path}: {str(e)}") connection.rollback() return 0 finally: connection.close() def save_displacement_data(file_path, db_user='root', db_password='000000'): """Save displacement gauge data""" connection = get_db_connection(db_user, db_password) If not connected: return 0 try: df = pd.read_excel(file_path, sheet_name=0) inserted_count = 0 with connection.cursor() as cursor: for _, row in df.iterrows(): try: sql = """ INSERT INTO displacement_data (sensor_code, channel_number, record_time, displacement_value,frequency_value, temperature) VALUES (%s, %s, %s, %s, %s, %s) """ cursor.execute(sql, ( row['device ID'], row['channel number'], row['collection time'], row['displacement amount lm (mm)'], row['modulo value F'], row['Temperature value T (°C)'] )) inserted_count += 1 except pymysql.IntegrityError: print("Violation of database integrity constraints") continue except Exception as e: print(f"Failed to insert displacement gauge record: {str(e)}") continue connection.commit() print(f"Displacement gauge data: Inserted {inserted_count} records") return inserted_count except Exception as e: print(f"Failed to process displacement gauge file {file_path}: {str(e)}") connection.rollback() return 0 finally: connection.close() def save_water_level_data(file_path, db_user='root', db_password='000000'): """Save water level gauge data""" connection = get_db_connection(db_user, db_password) If not connected: return 0 try: df = pd.read_excel(file_path, sheet_name=0) inserted_count = 0 with connection.cursor() as cursor: for _, row in df.iterrows(): try: sql = """ INSERT INTO water_level_data (sensor_code, channel_number, record_time, water_level, current_value) VALUES (%s, %s, %s, %s, %s) """ cursor.execute(sql, ( row['device ID'], row['channel number'], row['collection time'], row['water level (m)'], row['Current (mA)'] )) inserted_count += 1 except pymysql.IntegrityError: print("Violation of database integrity constraints") continue except Exception as e: print(f"Failed to insert water level gauge record: {str(e)}") continue connection.commit() print(f"Water level gauge data: Inserted {inserted_count} records") return inserted_count except Exception as e: print(f"Failed to process water level gauge file {file_path}: {str(e)}") connection.rollback() return 0 finally: connection.close() def process_sensor_files(excel_dir, file_pattern, db_user='root', db_password='000000'): Process sensor files and save them to the database. full_pattern = os.path.join(excel_dir, file_pattern) file_list = glob.glob(full_pattern) if not file_list: print(f"No file matching pattern '{file_pattern}' was found in directory '{excel_dir}'") return 0 total_inserted = 0 for file_path in file_list: try: file_name = os.path.basename(file_path) print(f"Processing: {file_name}") # Determine sensor type based on filename if 'earth pressure gauge' in file_name: inserted = save_earth_pressure_data(file_path, db_user, db_password) elif 'displacement gauge' in file_name: inserted = save_displacement_data(file_path, db_user, db_password) elif 'water level gauge' in file_name: inserted = save_water_level_data(file_path, db_user, db_password) else: print(f"Unknown sensor type, skipped: {file_name}") inserted = 0 total_inserted += inserted except Exception as e: print(f"File processing failed {file_path}: {str(e)}") return total_inserted def process_excel_files(excel_dir, file_pattern): Process Excel files and convert them into JSON format. full_pattern = os.path.join(excel_dir, file_pattern) file_list = glob.glob(full_pattern) if not file_list: print(f"No file matching pattern '{file_pattern}' was found in directory '{excel_dir}'") return None # Group data by sensor type and ID sensor_data = defaultdict(lambda: defaultdict(list)) for file_path in file_list: try: # Read Excel file df = pd.read_excel(file_path, sheet_name=0) # Extract sensor type from filename file_name = os.path.basename(file_path) parts = file_name.split('_') sensor_type = parts[1] # Retrieve Sensor ID from Data if df.empty: sensor_id = df.iloc[0]['Device ID'] # Convert each line of data into a dictionary and add it to the corresponding sensor. for _, row in df.iterrows(): record = row.to_dict() sensor_data[sensor_type][sensor_id].append(record) print(f"Processed file: {file_name}") except Exception as e: print(f"Error processing file {file_path}: {str(e)}") # Building a JSON structure result = {"sensors": []} for sensor_type, sensors in sensor_data.items(): for sensor_id, data in sensors.items(): result["sensors"].append({ "sensor_type": sensor_type, "sensor_id": sensor_id, "data": data }) return result def save_to_json(data, json_path): """Save data as a JSON file""" output_dir = os.path.dirname(json_path) if output_dir and not os.path.exists(output_dir): os.makedirs(output_dir) with open(json_path, 'w', encoding='utf-8') as f: json.dump(data, f, ensure_ascii=False, indent=2) print(f"Result has been saved to: {json_path}") def delete_excel_files(excel_dir, file_pattern): """Delete Excel files""" full_pattern = os.path.join(excel_dir, file_pattern) file_list = glob.glob(full_pattern) deleted_count = 0 for file_path in file_list: try: os.remove(file_path) deleted_count += 1 print(f"Deleted file: {os.path.basename(file_path)}") except Exception as e: print(f"File deletion failed {file_path}: {str(e)}") if deleted_count>0: print(f"A total of {deleted_count} Excel files were deleted") def transfer_file(local_file, container_name, target_path, remote_user="pi", remote_host="10.10.10.2", password=None): """Transfer files to a remote container via SSH""" try: ssh = paramiko.SSHClient() ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) print("Connecting to Raspberry Pi...") ssh.connect(remote_host, username=remote_user, password=password) # Read file content print(f"Reading local file: {local_file}") with open(local_file, 'rb') as f: file_content = f.read() # Build the target path within the container filename = os.path.basename(local_file) container_full_path = os.path.join(target_path, filename) # Execute docker command to transfer files command = f"docker exec -i {container_name} tee {container_full_path}> / dev / null" print(f"Command executed: {command}") stdin, stdout, stderr = ssh.exec_command(command) stdin.write(file_content) stdin.flush() stdin.channel.shutdown_write() # Check execution status exit_status = stdout.channel.recv_exit_status() if exit_status == 0: print(f"File successfully transferred to container: {container_name}:{container_full_path}") else: error = stderr.read().decode() print(f"Transmission failed: {error}") ssh.close() except Exception as e: print(f"SSH transfer error: {str(e)}") def main(): timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") # Specify path excel_dir = r"D:\Cloud_Edge_End\update_h960-App_v1.4.6.3_b20250224\Data" json_path = fr"D:\Cloud_Edge_End\ExportData\SensorData\sensor_data_{timestamp}.json" file_pattern = "COM5_*.xlsx" # Database parameters db_user = 'root' db_password = '000000' # ssh parameters container_name = "jupyterlab" target_path = " / root / test01 / " ssh_password = "000000" remote_host = "10.10.10.2" print("Starting sensor data processing flow") print(f"Data directory: {excel_dir}") print(f"File pattern: {file_pattern}") # 1. First save to the database print("\n1. Save to database...") total_inserted = process_sensor_files(excel_dir, file_pattern, db_user, db_password) # 2. Process Excel files and integrate them into JSON. print("\n2. Process Excel files and integrate them into JSON...") json_data = process_excel_files(excel_dir, file_pattern) if json_data is not None: # 3. Save as a JSON file print("\n3. Save the JSON file...") save_to_json(json_data, json_path) # 4. Delete Excel file print("\n4. Clean up the file...") delete_excel_files(excel_dir, file_pattern) # 5. SSH Transfer print("\n5. File transfer...") transfer_file( local_file=json_path, container_name=container_name, target_path=target_path, remote_host=remote_host, password=ssh_password ) print(f"\nProcessing complete! Timestamp: {timestamp}") print(f"A total of {total_inserted} records were inserted into the database") else: print("No data file found that can be processed") if __name__ == "__main__": main() In summary, this invention replaces the traditional manual channel-by-channel export operation with an automated Python script; it combines Windows Task Scheduler to achieve timed triggering, supports custom intervals of 1-24 hours for export, and requires no manual intervention to start, thus avoiding data loss due to human error; therefore, this invention improves the data export efficiency of tunnel safety monitoring and significantly reduces the reliance on manual labor in tunnel safety monitoring.

[0042] It should be emphasized that the above are merely preferred embodiments of the present invention and are not intended to limit the present invention in any way. Any simple modifications, equivalent changes and alterations made to the above embodiments based on the technical essence of the present invention shall still fall within the scope of the technical solution of the present invention.

Claims

1. A method for end-side data sensing and full-process automatic acquisition in tunnel safety monitoring, characterized in that, This includes the steps of automatically exporting data locally on a scheduled basis, as well as the steps of data storage, integration, and transmission. The local timed automatic data export step uses a Python script and Windows Task Scheduler. The Python script is used to export, integrate, store and transmit data. The Windows Task Scheduler is used to configure a timed task to execute the Python script regularly, realizing fully unmanned data collection on the edge. The data storage, integration, and transmission steps involve standardizing, persistently storing, and transmitting the exported Excel data files from each channel.

2. The method for end-side data sensing and full-process automatic acquisition of tunnel safety monitoring according to claim 1, characterized in that, In the local timed automatic data export step, the data export part uses a Python-based automated script to export sensor data in batches from the data acquisition software. By simulating user interface operation, the fully automated data export process is realized.

3. The method for end-side data sensing and full-process automatic acquisition of tunnel safety monitoring according to claim 2, characterized in that, The Python-based automation script controls the data acquisition software application through the pywinauto library; it first locates and activates the target application window by the window title, and then establishes a connection with the application.

4. The method for end-side data sensing and full-process automatic acquisition of tunnel safety monitoring according to claim 2, characterized in that, The control timing of the Python-based automation script is as follows: Timing 1: The script calculates and sets the time parameters for the query time range, using the current time as the end time and the start time as the current time minus the preset time increment; input the time parameters in the application interface. Timing 2: Process channel groups 1-8, performing the following operations for each channel: Locate and select the current channel using the control's auto_id and check the checkbox; Click the "Read Data" button to start the data export process; Use pyautogui to simulate pressing the Enter key to close the completion prompt dialog; finally, uncheck the current channel. Timing 3: After processing channels 1-8, the script performs a tab switching operation: by locating the tab control and selecting the "Host Channels: 9-16" tab, it switches to the interface of channel 9-16; after the interface is fully loaded, it processes channel 9-16 according to the same operation sequence as in Timing 2. Timing 4: After processing channel group 9-16, switch back to channel group 1-8.

5. The method for end-side data sensing and full-process automatic acquisition of tunnel safety monitoring according to claim 1, characterized in that, The data storage, integration, and transmission steps include data storage sub-steps, data integration sub-steps, and file transmission sub-steps.

6. The method for end-side data sensing and full-process automatic acquisition of tunnel safety monitoring according to claim 5, characterized in that, The data storage process involves persisting the original Excel data to a MySQL database in steps, creating corresponding database tables for different types of sensor data, and providing storage functions in the script.

7. The method for end-side data sensing and full-process automatic acquisition of tunnel safety monitoring according to claim 5, characterized in that, The data integration process involves scanning Excel files in a specified directory step by step, identifying sensor types based on filename patterns, and extracting sensor type identifiers from filenames. For each file, the device ID is extracted from the Excel spreadsheet data and used as a unique identifier for the sensor. Each row of data is converted into a dictionary format. Finally, the data is grouped according to sensor type and device ID to construct a unified JSON data structure.

8. The method for end-side data sensing and full-process automatic acquisition of tunnel safety monitoring according to claim 5, characterized in that, The file transfer is performed in steps: the industrial control computer is directly connected to the Raspberry Pi via a network cable, a static IP is configured, an SSH connection is established via a script, and the file is transferred to the specified container via Docker commands. The transfer process uses a pipe method, passing the local file content to the tee command inside the container through standard input.

9. The method for end-side data sensing and full-process automatic acquisition of tunnel safety monitoring according to claim 5, characterized in that, The data storage, integration, and transmission steps are equipped with error handling mechanisms, and each processing step includes exception capture; database operations adopt transaction management and support rollback operations; The system provides the following processing logs: file processing status, number of records inserted into the database, file deletion status, and transmission results.

Images