如何使用Python的ftplib库从FTP服务器每日文件生成表格

Hey there! Let's walk through how to complete this daily FTP file extraction and table generation tool. Your existing code is a great start—here's how to build it out step by step:

1. Fix & Strengthen FTP Connection + File Filtering #

First, let's polish your FTP setup to handle connections reliably and pick only today's files. Keep in mind: FTP servers often use UTC timestamps, so you might need to adjust for timezone differences depending on your location.

Code Snippet to Extend Your Base Code #

import ftplib
from datetime import datetime, timedelta
import os
import logging

# Set up basic logging to track runs (super helpful for debugging!)
logging.basicConfig(filename='ftp_daily_run.log', level=logging.INFO,
                    format='%(asctime)s - %(levelname)s - %(message)s')

# Connection config
server = 'ftp0.micasa.es'
username = 'anonymous'
password = '---------'  # Anonymous logins often accept a valid email here
target_directory = '/expl/publ/your-full-target-path'  # Fill in the actual directory
local_save_dir = './ftp_downloads'

# Create local directory if it doesn't exist
os.makedirs(local_save_dir, exist_ok=True)

def connect_to_ftp():
    """Establish and return an FTP connection, or None on failure"""
    try:
        ftp = ftplib.FTP(server, timeout=30)
        ftp.login(username, password)
        ftp.cwd(target_directory)
        logging.info(f"Successfully connected to FTP directory: {target_directory}")
        print(f"Connected to {target_directory}")
        return ftp
    except ftplib.all_errors as e:
        error_msg = f"FTP connection failed: {str(e)}"
        logging.error(error_msg)
        print(error_msg)
        return None

def get_today_s_files(ftp):
    """Yield filenames in the FTP directory that were modified today"""
    today = datetime.today().date()
    file_list = []
    # Use FTP's dir command with -t to get sorted, detailed file info
    ftp.dir('-t', lambda line: file_list.append(line))
    
    for line in file_list:
        parts = line.split()
        if len(parts) < 7:
            continue  # Skip malformed lines
        
        # Parse timestamp from FTP dir output (format varies slightly)
        file_month, file_day = parts[5], parts[6]
        # Skip old files that show a year instead of time
        if len(file_day) == 4:
            continue
        
        # Convert to a date object (use current year for this check)
        try:
            file_date = datetime.strptime(f"{today.year} {file_month} {file_day}", "%Y %b %d").date()
        except ValueError:
            logging.warning(f"Could not parse date for line: {line}")
            continue
        
        if file_date == today:
            filename = parts[-1]
            yield filename

2. Download Filtered Files #

Once you have today's filenames, download them to your local directory:

def download_ftp_file(ftp, filename):
    """Download a single file from FTP to local save directory"""
    local_file_path = os.path.join(local_save_dir, filename)
    try:
        with open(local_file_path, 'wb') as local_file:
            ftp.retrbinary(f'RETR {filename}', local_file.write)
        logging.info(f"Successfully downloaded: {filename}")
        print(f"Downloaded {filename} to {local_save_dir}")
        return local_file_path
    except ftplib.all_errors as e:
        error_msg = f"Failed to download {filename}: {str(e)}"
        logging.error(error_msg)
        print(error_msg)
        return None

3. Generate Structured Tables #

Assuming your files are text-based (CSV, TSV, etc.), use pandas to quickly turn them into clean Excel/CSV tables. If you're working with other formats (XML, JSON), swap in the appropriate parser:

import pandas as pd

def create_daily_table(local_file_path):
    """Convert downloaded file data into a formatted table (Excel example)"""
    try:
        today = datetime.today().date()
        # Adjust sep parameter to match your file's delimiter (comma, tab, etc.)
        df = pd.read_csv(local_file_path, sep=',')
        
        # Create a timestamped output filename to avoid overwrites
        output_filename = f"daily_report_{today.strftime('%Y%m%d')}.xlsx"
        df.to_excel(output_filename, index=False)
        
        logging.info(f"Successfully generated table: {output_filename}")
        print(f"Created table: {output_filename}")
    except Exception as e:
        error_msg = f"Failed to generate table: {str(e)}"
        logging.error(error_msg)
        print(error_msg)

4. Automate Daily Execution #

To make this run automatically every day:

• Windows: Use Task Scheduler to create a daily task that runs your Python script
• Linux/macOS: Add a cron job (run crontab -e and add a line like 0 1 * * * /usr/bin/python3 /path/to/your/script.py to run at 1 AM daily)
• Python-only: Use the schedule library to run the script in a loop (good if you need it to run as a persistent service)

Key Pro Tips #

• Timezone Check: If the FTP server uses UTC, adjust your today variable to UTC date with datetime.utcnow().date() to avoid missing files
• Error Handling: We added logging to track issues—check the log file if something fails unexpectedly
• Anonymous Login: Many FTP servers require a valid email as the anonymous password (try using your own email if the current one isn't working)

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