Python中执行多SQL语句：批量更新MySQL数据的代码求助

Hey there! Let's break down the issues in your current code first, then share a polished, optimized version of your batch update logic that's safer, faster, and more reliable.

Key Problems in Your Original Code #

• No Transaction Commit: MySQLdb operates in manual commit mode by default. That means none of your UPDATE changes are actually saved to the database unless you explicitly call db.commit()—right now, all your updates are just rolling back silently.
• SQL Injection Vulnerability: You're directly concatenating values into your SQL string. If id_pl ever has special characters (or someone feeds malicious input), this will break your query or let attackers manipulate your database.
• Wasteful Cursor Handling: Creating and closing a new cursor for every single update adds unnecessary overhead. Reusing a single cursor is much more efficient.
• No Error Safety: If a directory is missing, or the database connection drops mid-process, your script will crash immediately with no cleanup.
• Slow File Counting: Using os.listdir() plus a list comprehension to check for files works, but os.scandir() is faster (it grabs file metadata in one go instead of extra system calls).
• Unclosed Connection: You never close your database connection when you're done, which can leave hanging connections that waste server resources.

Optimized Batch Update Code #

import os
import MySQLdb

def count_files_safely(dir_path):
    """Efficiently count files in a directory (ignores subdirs, handles missing paths)"""
    if not os.path.isdir(dir_path):
        return 0  # Return 0 if the directory doesn't exist
    file_count = 0
    # os.scandir is faster than os.listdir for this task
    with os.scandir(dir_path) as entries:
        for entry in entries:
            if entry.is_file():
                file_count += 1
    return file_count

try:
    # Set up database connection once
    db = MySQLdb.connect("localhost", "root", "", "tuongdata")
    cursor = db.cursor()  # Reuse this cursor for all operations

    # Fetch all id_pl values in one go
    fetch_query = "SELECT id_pl FROM datapl"
    cursor.execute(fetch_query)
    pl_list = [row[0] for row in cursor.fetchall()]

    # Prepare batch update data
    update_query = "UPDATE datapl SET num = %s WHERE id_pl = %s"
    update_records = []
    base_dir = './newsdata/'

    for id_pl in pl_list:
        target_dir = os.path.join(base_dir, str(id_pl))
        file_count = count_files_safely(target_dir)
        update_records.append((file_count, id_pl))

    # Run all updates in a batch (way more efficient than single queries)
    cursor.executemany(update_query, update_records)
    db.commit()  # Save all changes to the database
    print(f"Success! Updated {cursor.rowcount} records.")

except MySQLdb.Error as db_err:
    # Roll back if any database error occurs
    db.rollback()
    print(f"Database error: {db_err}")
except Exception as general_err:
    print(f"Unexpected error: {general_err}")
finally:
    # Always clean up resources, even if something goes wrong
    if 'cursor' in locals():
        cursor.close()
    if 'db' in locals():
        db.close()

What's Better About This Version? #

• Parameterized Queries: Using %s placeholders eliminates SQL injection risks and handles data type conversions automatically (no more worrying about string vs numeric id_pl values).
• Batch Execution: executemany() sends all update statements to the database in one go (or minimal round-trips) instead of one at a time—this drastically speeds up batch operations.
• Safer File Counting: We handle missing directories gracefully, and os.scandir() makes counting files faster.
• Transaction Safety: The try-except block ensures we only commit if everything works, and roll back if anything fails—keeping your database data consistent.
• Clean Resource Management: The finally block guarantees we close cursors and connections even if the script crashes, preventing resource leaks.
• Reusable Cursor: One cursor for all operations cuts down on unnecessary overhead.

内容的提问来源于stack exchange，提问作者Tương Nguyễn Lương