如何用Python3实现月度自动下载网页指定PDF并转CSV存入MySQL

Got it, let's walk through building this automated workflow step by step—from downloading the latest recipe PDF, converting it to CSV, storing it in MySQL, to scheduling the whole thing to run monthly. I'll include practical code snippets and key tips to avoid common pitfalls.

Since you need to click a button to trigger the download, a headless browser tool like Selenium is perfect for this job (static HTTP requests won't work here because the download is triggered by user interaction).

Setup & Code #

First, install the required package:

pip install selenium

Then write the download logic (adjust selectors and paths to match your target website):

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
import time

def download_latest_recipe(website_url, download_folder):
    # Configure Chrome to download files without popups
    options = webdriver.ChromeOptions()
    prefs = {"download.default_directory": download_folder}
    options.add_experimental_option("prefs", prefs)
    # Run in headless mode (no visible browser window)
    options.add_argument("--headless=new")
    
    driver = webdriver.Chrome(options=options)
    try:
        driver.get(website_url)
        
        # Wait for the first download button to be clickable (more reliable than sleep)
        first_btn = WebDriverWait(driver, 15).until(
            EC.element_to_be_clickable((By.CSS_SELECTOR, ".recipe-download-button:first-of-type"))
        )
        first_btn.click()
        
        # Wait for download to finish (adjust based on typical PDF size)
        time.sleep(10)
        print("Latest recipe PDF downloaded successfully")
    finally:
        driver.quit()

# Example usage
download_latest_recipe(
    website_url="https://your-recipe-site.com",
    download_folder="/home/you/recipe_downloads"
)

Key Tips #

• Use your browser's developer tools to inspect the download button and get the correct CSS selector (replace .recipe-download-button with the actual class/id from the site).
• If the site requires login, add steps to enter credentials before clicking the download button.
• Replace time.sleep(10) with a check for the downloaded file's existence to avoid unnecessary delays.

Most recipe PDFs are either formatted as tables or structured text. We'll use pdfplumber (for accurate text/table extraction) and pandas (to handle CSV conversion).

Setup & Code #

Install packages:

pip install pdfplumber pandas

Write the conversion function:

import pdfplumber
import pandas as pd
import os

def pdf_to_csv(pdf_folder, csv_output_path):
    # Get the latest downloaded PDF (assuming it's the only/newest file in the folder)
    pdf_files = [f for f in os.listdir(pdf_folder) if f.endswith(".pdf")]
    latest_pdf = max(pdf_files, key=lambda x: os.path.getctime(os.path.join(pdf_folder, x)))
    pdf_path = os.path.join(pdf_folder, latest_pdf)
    
    with pdfplumber.open(pdf_path) as pdf:
        all_data = []
        for page in pdf.pages:
            # Try extracting table first (if the recipe is in table format)
            table = page.extract_table()
            if table:
                all_data.extend(table)
            else:
                # Fallback to extracting text and splitting into rows (adjust delimiter as needed)
                lines = page.extract_text().split("\n")
                all_data.append([line.strip() for line in lines])
        
        # Convert to DataFrame and save as CSV
        df = pd.DataFrame(all_data[1:], columns=all_data[0])  # Skip header row if needed
        df.to_csv(csv_output_path, index=False)
        print(f"Converted PDF to CSV: {csv_output_path}")

# Example usage
pdf_to_csv(
    pdf_folder="/home/you/recipe_downloads",
    csv_output_path="/home/you/recipe_csvs/latest_recipe.csv"
)

Key Tips #

• Test with a sample PDF to adjust the extraction logic—some PDFs use columns or custom formatting that may need extra handling.
• If the PDF is encrypted, add pdf.decrypt("password") before extracting content.

We'll use sqlalchemy with pandas to seamlessly insert CSV data into MySQL. This handles table creation and data type mapping automatically.

Setup & Code #

Install packages:

pip install sqlalchemy pymysql

Write the database insertion function:

from sqlalchemy import create_engine
import pandas as pd

def csv_to_mysql(csv_path, db_config):
    # Create database connection string
    engine = create_engine(
        f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['db_name']}"
    )
    
    # Read CSV and insert into MySQL
    df = pd.read_csv(csv_path)
    # Use `if_exists="append"` to add new recipes monthly, or "replace" to overwrite
    df.to_sql(name="recipes", con=engine, if_exists="append", index=False)
    print("Recipe data inserted into MySQL successfully")

# Example database config
db_config = {
    "host": "localhost",
    "port": 3306,
    "user": "your_mysql_username",
    "password": "your_mysql_password",
    "db_name": "recipe_database"
}

# Example usage
csv_to_mysql(
    csv_path="/home/you/recipe_csvs/latest_recipe.csv",
    db_config=db_config
)

Key Tips #

• Ensure your MySQL user has INSERT permissions for the target database.
• If the recipes table doesn't exist, pandas will create it automatically—you can tweak column types later if needed.
• Add error handling for connection issues (e.g., MySQL service not running) with try-except blocks.

For reliability, use system-level scheduling tools instead of Python's schedule library.

Linux/macOS (Cron) #

1. Open the crontab editor:

crontab -e

2. Add a line to run the script on the 1st of every month at 2 AM:

0 2 1 * * /usr/bin/python3 /home/you/recipe_script.py >> /home/you/recipe_logs/script_log.log 2>&1

• 0 2 1 * * = minute 0, hour 2, day 1, every month, every weekday
• The log file captures output/errors for debugging.

Windows (Task Scheduler) #

1. Open Task Scheduler → Create Basic Task
2. Set a trigger for "Monthly" (choose the 1st day and desired time)
3. For the action, select "Start a program"
4. Point to your Python executable path (e.g., C:\Python311\python.exe) and add your script path as an argument (e.g., C:\Users\You\recipe_script.py)
5. Set the working directory to your script's folder.

• Handle Website Changes: If the target site updates its button selector or layout, your script will break—periodically test the download step to catch changes early.
• Add Error Handling: Wrap each function in try-except blocks to log errors (e.g., failed downloads, PDF extraction issues) instead of crashing silently.
• Use Absolute Paths: Always use full paths in your script (no relative paths) to avoid issues when running via cron/Task Scheduler.

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