Automating Database Monitoring and File Conversion with Python: A Comprehensive Guide

Introduction

Automation is a crucial component of modern technology, enabling businesses to streamline repetitive tasks, save time, and improve efficiency. In this blog, we explore how you can automate your database health checks using Python and JMeter, along with converting .plain files to Excel format using Pandas and OpenPyXL. This guide will walk you through a powerful Python script designed to perform these tasks automatically during specific operational hours.

---

Why Automate Database Monitoring and File Conversion?

As businesses grow, managing routine checks on databases and processing data files manually can become tedious and error-prone. By automating these processes, you can:

• Reduce human error and manual effort.
• Ensure timely execution of health checks and data conversions.
• Improve productivity and resource management.

This blog post demonstrates a practical automation solution using Python, which can be scheduled to run tasks like database checks and file conversions at regular intervals.

import os
import schedule
import time
from datetime import datetime
import pandas as pd
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo


# Function to check if the current time is within the operational hours
def within_operational_hours():
    # Get the current time and day of the week
    current_time = datetime.now().time()
    current_day = datetime.now().weekday()
    
    # Check if the current time is between 09:00 and 18:00, and it's a weekday (Mon-Fri)
    return (current_time >= datetime.strptime('09:00', '%H:%M').time() and
            current_time <= datetime.strptime('18:00', '%H:%M').time() and
            current_day < 5)


# First Script: Function to run the JMeter task
def run_jmeter():
    # Only run if within operational hours
    if within_operational_hours():
        # Command to run JMeter with the specified .jmx file
        jmeter_command = r"C:\apache-jmeter-3.2\apache-jmeter-3.2\bin\jmeter.bat -n -t C:\apache-jmeter-3.2\apache-jmeter-3.2\bin\examples\CHECK_DATA_TABLE.jmx"
        
        # Execute the JMeter command
        os.system(jmeter_command)
        
        # Log the execution time to a file
        with open("jmeter_log.txt", "a") as log_file:
            log_file.write(f"JMeter task executed at {datetime.now()}.\n")
        print("JMeter task executed.")


# Second Script: Function to convert .plain files to Excel
def convert_plain_to_excel():
    # Define file paths for the input .plain files and output Excel files
    plain_file_paths = [
        r"C:\Plain file\Table1.plain",
        r"C:\Plain file\Table2.plain",
        r"C:\Plain file\Table3.plain"
    ]

    excel_file_paths = [
        r"C:\Plain to Excel\Table1.xlsx",
        r"C:\Plain to Excel\Table2.xlsx",
        r"C:\Plain to Excel\Table3.xlsx"
    ]

    # Define the name of the worksheet where data will be written
    sheet_name = 'DataTable'

    # Loop through each pair of input and output files
    for plain_file_path, excel_file_path in zip(plain_file_paths, excel_file_paths):
        # Read data from the .plain file into a DataFrame
        df = read_plain_file(plain_file_path)
        
        # Write the DataFrame to an Excel file and format it as a table
        write_to_excel(df, excel_file_path, sheet_name)
        print(f"Data from {plain_file_path} has been written to {excel_file_path} in sheet {sheet_name} and formatted as a table.")


# Function to read data from a .plain file and convert it to a DataFrame
def read_plain_file(file_path):
    # Open the .plain file and read all lines
    with open(file_path, 'r') as file:
        lines = file.readlines()
    
    # Extract headers from the first line
    headers = lines[0].strip().split()
    
    # Extract data from the remaining lines
    data = [line.strip().split() for line in lines[1:]]
    
    # Return a DataFrame with the extracted headers and data
    return pd.DataFrame(data, columns=headers)


# Function to write a DataFrame to an Excel file and format the data as a table
def write_to_excel(df, excel_file_path, sheet_name):
    # Write DataFrame to the specified Excel sheet
    df.to_excel(excel_file_path, sheet_name=sheet_name, index=False)
    
    # Load the workbook and select the sheet
    wb = load_workbook(excel_file_path)
    ws = wb[sheet_name]
    
    # Define the range of the table (e.g., A1:C10 based on the DataFrame size)
    table_range = f"A1:{chr(65 + len(df.columns) - 1)}{len(df) + 1}"
    
    # Create a table with a specific style
    tab = Table(displayName="DataTable", ref=table_range)
    style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                           showLastColumn=False, showRowStripes=True, showColumnStripes=True)
    tab.tableStyleInfo = style
    
    # Add the table to the worksheet
    ws.add_table(tab)
    
    # Save the workbook with the formatted table
    wb.save(excel_file_path)


# Schedule the JMeter task to run every 15 minutes
schedule.every(15).minutes.do(run_jmeter)

# Schedule the conversion of .plain files to Excel to run every 16 minutes
schedule.every(16).minutes.do(convert_plain_to_excel)


# Keep the script running to execute scheduled tasks
while True:
    # Check if any scheduled tasks are due to run
    schedule.run_pending()
    
    # Wait for 30 seconds before checking again
    time.sleep(30)

Step-By-Step Breakdown of the Python Script

1. Checking Operational Hours

To ensure that tasks are only executed during business hours (Monday to Friday, 9 AM to 6 PM), the script uses Python’s datetime module to check the current day and time.

2. Running JMeter for Database Health Checks

Using JMeter, the script executes a .jmx file to check the health of your database. The command is run only during operational hours to avoid unnecessary execution.

3. Converting .plain Files to Excel

The script reads data from .plain files and converts them into Excel format using the Pandas and OpenPyXL libraries. This helps convert raw data into a structured format that can be easily analyzed.

4. Scheduling the Automation

The schedule library is used to automate the execution of these tasks at specific intervals (every 15 minutes for JMeter, every 16 minutes for file conversion).

Additional Applications to Integrate into the Script

Beyond database checks and file conversions, there are numerous other tasks you can automate with Python to enhance your operational efficiency:

1. Automated Email Notifications:
   • Send alerts to stakeholders if the health checks fail or if specific conditions are met in your data files.
2. Data Backup and Archiving:
   • Schedule daily backups of critical files to secure locations, ensuring data integrity and disaster recovery.
3. Web Scraping:
   • Integrate a web scraping module to fetch the latest data or reports from online sources automatically.
4. API Integration:
   • Extend the script to integrate with APIs of monitoring tools to fetch or post data directly to your dashboards.
5. Slack/Teams Notifications:
   • Use Python libraries like slack_sdk or pytchat to send status updates directly to your team’s communication channels.

Conclusion

In this guide, we’ve shown you how to set up a Python script to automate routine database checks and data conversion tasks. This automation solution saves time, reduces human error, and ensures that your reports are always up to date.

Feel free to modify the script to suit your specific needs, and consider integrating additional applications to further optimize your workflows. Happy automating!

---

I hope you found this guide helpful. Please feel free to share your feedback or ask questions in the comments below!