I have a RaspberryPi running a database used for attendance tracking. I tried to make a web page so that when the user inputs 2 dates into an HTML form, it will be passed on to a PHP script that will run a Python script that will run the SQL query.
When I run the Python script from the command line, it works completely fine. But when I try from the browser I get this error
An error occurred: __init__() got multiple values for argument 'schema'
Also I’m logging the arguments sent to the script and they seem to have no problem
Executing backup_and_delete.py with start_date: 2024-03-03 and end_date: 2024-03-15 Start date: 2024-03-03, End date: 2024-03-15
Also from what I can tell, is that the form is submitted successfully, the php script runs the command properly and then I think it breaks at the Python code. But again, when I run from the command line it does work.
This is the HTML file
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Backup and Delete Records</title>
<link rel="stylesheet" href="styles/style_backup.css">
</head>
<body>
<h1>Backup and Delete Records</h1>
<form method="post" action="process_records.php">
<label for="start_date">Start Date:</label>
<input type="date" id="start_date" name="start_date" required>
<label for="end_date">End Date:</label>
<input type="date" id="end_date" name="end_date" required>
<button type="submit">Backup and Delete</button>
</form>
<a href="admin.php" class="main-link">MAIN PAGE</a>
</body>
</html>
This is the PHP file
<?php
// Validate dates
$start_date = $_POST['start_date'];
$end_date = $_POST['end_date'];
if (empty($start_date) || empty($end_date)) {
die("Please provide both start and end dates.");
}
// Execute Python script
$command = escapeshellcmd('python3 /var/www/html/backup_and_delete.py ' . $start_date . ' ' . $end_date);
exec($command, $output, $return_var);
echo "<pre>";
print_r($output); // Output Python script execution result for debugging
echo "</pre>";
if ($return_var !== 0) {
die("An error occurred during the backup and deletion process.");
}
// Generate file names based on start_date
$excel_file = "attendance_data_" . $start_date . ".xlsx";
$csv_file = "attendance_data_" . $start_date . ".csv";
// Check if the files exist
if (file_exists("/var/www/html/" . $excel_file) && file_exists("/var/www/html/" . $csv_file)) {
echo "<p>Backup and deletion process completed.</p>";
echo "<p>Download your files:</p>";
echo "<ul>";
echo "<li><a href='{$excel_file}'>Download Excel File</a></li>";
echo "<li><a href='{$csv_file}'>Download CSV File</a></li>";
echo "</ul>";
} else {
echo "<p>Failed to create backup files. Please check the server logs for more details.</p>";
}
?>
This is the Python file
import sys
import mysql.connector
import pandas as pd
from sqlalchemy import create_engine
def format_timedelta(td):
seconds = td.seconds
hours, remainder = divmod(seconds, 3600)
minutes, seconds = divmod(remainder, 60)
return f"{hours:02}:{minutes:02}:{seconds:02}"
# Function to connect to MySQL database and perform operations
def backup_and_delete(start_date, end_date):
try:
# Connect to the MySQL database
mydb = mysql.connector.connect(
host="localhost",
user="pi",
password="sudoupdate",
database="attendance"
)
# Create SQLAlchemy engine from MySQL connection
engine = create_engine('mysql+mysqlconnector://pi:sudoupdate@localhost/attendance')
# Query the database using SQLAlchemy engine
sql = "SELECT * FROM attendance_check WHERE date_id BETWEEN %s AND %s"
df = pd.read_sql(sql, con=engine, params=(start_date, end_date))
# Convert timedelta to formatted string for 'clock_in' and 'clock_out' columns
df['clock_in'] = df['clock_in'].apply(format_timedelta)
df['clock_out'] = df['clock_out'].apply(format_timedelta)
# Specify full paths for saving files in /var/www/html/
excel_file_name = "/var/www/html/attendance_data_" + start_date + ".xlsx"
csv_file_name = "/var/www/html/attendance_data_" + start_date + ".csv"
# Export to Excel using openpyxl, specifying columns to include
df.to_excel(excel_file_name, index=False, engine='openpyxl', columns=['date_id', 'user_id', 'name', 'clock_in', 'clock_out'])
# Export to CSV, specifying columns to include
df.to_csv(csv_file_name, index=False, columns=['date_id', 'user_id', 'name', 'clock_in', 'clock_out'])
# Delete records from the database
cursor = mydb.cursor()
delete_sql = "DELETE FROM attendance_check WHERE date_id BETWEEN %s AND %s"
cursor.execute(delete_sql, (start_date, end_date))
mydb.commit()
cursor.close()
mydb.close()
return f"Backup and deletion successful. Files created: {excel_file_name}, {csv_file_name}"
except Exception as e:
return str(e)
# Main execution when running the script directly
if __name__ == "__main__":
if len(sys.argv) < 3:
print("Usage: python3 backup_and_delete.py start_date end_date")
else:
start_date = sys.argv[1]
end_date = sys.argv[2]
result = backup_and_delete(start_date, end_date)
print(result)
3