RedHat Linux 6.6环境下，如何用Python连接MySQL删除指定文件？

Hey there! Let's break down how to automate deleting those automated tagged files using Python on your RHEL 6.6 system, pulling the file details from MySQL 5.5. I'll walk you through each step clearly, with safety in mind since deleting files is serious business.

To connect Python to MySQL 5.5 on RHEL 6.6, you'll need the mysql-python library (also called MySQLdb). Here's how to set it up:

• First install system-level dependencies:

  sudo yum install mysql-devel gcc python-devel python-pip
  

• Then install the Python MySQL connector:

  pip install mysql-python
  

Note: If you're using Python 3 instead of the default Python 2.7 on RHEL 6.6, use mysqlclient instead (pip install mysqlclient) since MySQLdb doesn't support Python 3.

Let's build the script step by step, with error handling to avoid surprises.

2.1 Connect to the MySQL Database #

Start by setting up the database connection. Replace the placeholder values with your actual MySQL credentials:

import MySQLdb
import os

# Replace these with your database details
DB_CONFIG = {
    'host': 'localhost',  # Or your DB host IP
    'user': 'your_db_username',
    'passwd': 'your_db_password',
    'db': 'your_database_name',
    'charset': 'utf8'
}

try:
    # Establish connection and create cursor
    conn = MySQLdb.connect(**DB_CONFIG)
    cursor = conn.cursor()
    print("Successfully connected to MySQL database")
except MySQLdb.Error as e:
    print(f"Failed to connect to database: {str(e)}")
    exit(1)

2.2 Fetch Files Marked as automated #

Next, query the database to get all files tagged as automated. I'll assume your table is named file_records with columns hash_filename (the unique hash name), storage_path (where the file lives), and file_type (the tag):

# Query to get automated files
QUERY = """
SELECT hash_filename, storage_path 
FROM file_records 
WHERE file_type = %s;
"""

try:
    # Use parameterized query to avoid SQL injection
    cursor.execute(QUERY, ('automated',))
    files_to_delete = cursor.fetchall()
    print(f"Found {len(files_to_delete)} files marked for deletion")
except MySQLdb.Error as e:
    print(f"Query failed: {str(e)}")
    conn.close()
    exit(1)

2.3 Delete the Files (With Safety Checks!) #

Before deleting anything, always verify the list of files first—comment out the os.remove() line and just print paths to confirm you're targeting the right files. Once you're sure, uncomment the deletion line:

# Iterate through files and delete
for hash_name, storage_path in files_to_delete:
    full_file_path = os.path.join(storage_path, hash_name)
    
    try:
        if os.path.exists(full_file_path):
            # Uncomment the line below once you've verified the paths!
            # os.remove(full_file_path)
            print(f"Deleted: {full_file_path}")
        else:
            print(f"File not found, skipping: {full_file_path}")
    except PermissionError:
        print(f"Permission denied: Cannot delete {full_file_path}")
    except Exception as e:
        print(f"Error deleting {full_file_path}: {str(e)}")

2.4 Clean Up the Database Connection #

Don't forget to close the connection when you're done:

# Close cursor and connection
cursor.close()
conn.close()
print("Database connection closed successfully")

• Test First: Always run the script with deletion commented out to confirm the file paths are correct. Accidental deletion is easy to avoid with a quick check!
• Permissions: Make sure the user running the script has:
  • Read access to the MySQL database (to fetch the file records)
  • Write/delete access to the storage_path directories on your RHEL system
• Database Updates: If you want to mark files as deleted in the database after removal, add an UPDATE query after deleting each file (or batch update at the end):

  # Example batch update (run after deleting all files)
  UPDATE_QUERY = "UPDATE file_records SET status = 'deleted' WHERE file_type = %s;"
  cursor.execute(UPDATE_QUERY, ('automated',))
  conn.commit()
  

• Backup: Consider backing up the file_records table before running the script, just in case you need to recover any data later.

内容的提问来源于stack exchange，提问作者Lawrence