如何从指定数据表字段创建字典？实现门店数据导入过滤

Hey there! Sounds like you're tackling a common legacy data migration task—turning static text reports into structured, trackable data. Using a dictionary to filter based on WorkcenterID from tblShop is a smart move for fast lookups, so let's walk through how to build this script effectively.

The key here is leveraging dictionary lookups (which are O(1) time complexity) to quickly validate whether a record belongs to your shop. Instead of querying the database for every single report entry (which would be slow for large datasets), we'll first load all valid WorkcenterIDs into a dictionary once, then use that to filter incoming report data on the fly.

Let's break this into manageable parts:

• Fetch Valid WorkcenterIDs from tblShop: Connect to your legacy database, pull all WorkcenterIDs, and store them in a dictionary. Using dict.fromkeys() is a clean way to create a dictionary where each ID is a key (we don't care about the value—we just need to check if the key exists).
• Parse & Filter the Legacy Text Report: Read through the text report line by line, extract the WorkcenterID from each record, and only keep records where the ID exists in our dictionary.
• Bulk Import Valid Records to Tracking Database: Take the filtered records and insert them into your flexible tracking database. Using bulk insert methods (like executemany in Python) will be way more efficient than inserting one record at a time.

Assuming you're using Python with ODBC for database connections (adjust drivers/connection strings to match your setup):

import pyodbc
import csv  # Use csv module for more robust text parsing

# Get valid WorkcenterIDs from tblShop
def get_valid_workcenters():
    # Update connection string to match your legacy DB
    conn_str = "DRIVER={SQL Server};SERVER=your_legacy_server;DATABASE=legacy_db;UID=your_user;PWD=your_pass"
    with pyodbc.connect(conn_str) as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT WorkcenterID FROM tblShop")
        # Create a dictionary with WorkcenterIDs as keys
        workcenter_map = dict.fromkeys(row[0] for row in cursor.fetchall())
    return workcenter_map

# Parse legacy report and filter valid records
def parse_and_filter_report(report_path, valid_workcenters):
    valid_records = []
    # Use csv reader to handle delimiters/quoting properly
    with open(report_path, 'r', encoding='utf-8-sig') as f:
        reader = csv.reader(f)
        next(reader)  # Skip header row
        for row in reader:
            if not row:
                continue
            # Adjust index to match where WorkcenterID is in your report rows
            record_workcenter = row[1]
            # Convert to same type as DB if needed (e.g., int(record_workcenter))
            if record_workcenter in valid_workcenters:
                valid_records.append(row)
    return valid_records

# Bulk import to tracking database
def import_to_tracking_db(records):
    # Update connection string for your tracking DB
    conn_str = "DRIVER={SQL Server};SERVER=tracking_server;DATABASE=tracking_db;UID=your_user;PWD=your_pass"
    with pyodbc.connect(conn_str) as conn:
        cursor = conn.cursor()
        # Update INSERT query to match your tracking table's schema
        insert_query = """
        INSERT INTO tblTracking (ReportField1, WorkcenterID, ReportField3, ...)
        VALUES (?, ?, ?, ...)
        """
        cursor.executemany(insert_query, records)
        conn.commit()
    print(f"Successfully imported {len(records)} valid records!")

# Main execution
if __name__ == "__main__":
    print("Loading valid WorkcenterIDs from tblShop...")
    valid_workcenters = get_valid_workcenters()
    print(f"Loaded {len(valid_workcenters)} valid IDs")
    
    print("Parsing and filtering legacy report...")
    filtered_records = parse_and_filter_report("legacy_report.txt", valid_workcenters)
    
    print("Importing valid records to tracking database...")
    import_to_tracking_db(filtered_records)

• Type Matching: Make sure the WorkcenterID from your text report matches the data type in tblShop (e.g., if the DB stores integers, convert the parsed string to an int before checking the dictionary).
• Report Parsing: If your text report uses non-standard delimiters (tabs, fixed-width columns), adjust the parsing logic—for fixed-width, you might need to slice strings instead of using csv.reader.
• Error Handling: Add try/except blocks around database connections and file operations to catch issues like missing files or failed DB connections.
• Performance: If tblShop is large or changes frequently, consider caching the dictionary (e.g., save to a local file) to avoid querying the DB every time the script runs.

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