文件转存循环代码异常：记录重复及正确数据未插入第二表求助

Hey there, let’s break down these two problems step by step—they’re super common when working with file I/O and database operations, so we’ll get to the bottom of it!

1. Why Your New File Has Duplicate Records #

This almost always boils down to reusing the same data reference/object across your loop or a mistake in how you’re reading from the source file. Here’s what to check:

Common Culprits & Fixes #

• You’re modifying the same record object instead of creating a new one:
  If you define a record variable (like a dictionary or class instance) outside your loop, every iteration just overwrites its values—and when you add it to a list or write it to a file, you’re actually saving references to the same object. By the end of the loop, all entries point to the last set of values.

  # ❌ Wrong: Reusing the same dictionary
  record = {}
  records = []
  with open("old_file.txt", "r") as f:
      for line in f:
          parts = line.split(",")
          record["id"] = parts[0]
          record["value"] = parts[1]
          records.append(record)  # All entries are the same dict!
  

  # ✅ Correct: Create a new record inside the loop
  records = []
  with open("old_file.txt", "r") as f:
      for line in f:
          record = {}  # Fresh dict every time
          parts = line.split(",")
          record["id"] = parts[0]
          record["value"] = parts[1]
          records.append(record)
  

• Your file reading logic is stuck on the same record:
  If you’re using readlines() and then accidentally referencing the last index every loop (e.g., lines[-1]), or forgetting to reset the file pointer with seek(0) if you’re re-reading the file, you’ll write the same line repeatedly. Always iterate directly over the file object (like for line in f) to ensure you read each line in order.
• Debug with print statements:
  Add a print inside your loop to log the record you’re about to write. If the print shows different values but the file has duplicates, the issue is in your writing logic (e.g., accidentally appending the entire list every time instead of individual lines).

2. Why Correct Records Aren’t Inserting Into the Second Table #

Database insert failures often happen silently if you don’t handle errors or transactions properly. Here’s what to investigate:

Common Culprits & Fixes #

• You forgot to commit the transaction:
  Most databases use manual commit by default. If you execute an INSERT but never call commit() on your connection, the changes will roll back when you close the connection.

  # ❌ Wrong: No commit
  conn = sqlite3.connect("mydb.db")
  cursor = conn.cursor()
  cursor.execute("INSERT INTO table2 (value) VALUES (?)", ("correct_value",))
  conn.close()  # Changes are lost!
  

  # ✅ Correct: Commit after inserts
  conn = sqlite3.connect("mydb.db")
  cursor = conn.cursor()
  try:
      cursor.execute("INSERT INTO table2 (value) VALUES (?)", ("correct_value",))
      conn.commit()  # Save changes
  except Exception as e:
      conn.rollback()  # Undo on error
      print(f"Insert failed: {e}")
  finally:
      conn.close()
  

• Your filter logic is excluding valid records:
  Double-check any if statements that decide whether to insert a record. For example, if you have if value != "correct_value": instead of if value == "correct_value":, you’ll skip the exact records you want to insert.
• There’s an uncaught error:
  If your insert throws an exception (like a primary key conflict, data type mismatch, or missing column), and you don’t have a try-catch block, the program might crash mid-loop or silently skip the insert. Always wrap database operations in error handling to log what’s going wrong.
• Verify your SQL manually:
  Print the full SQL query you’re executing (e.g., print(cursor.mogrify("INSERT ...", (value,)))) and run it directly in your database client. If it fails there, you’ll know the issue is with the query itself (not your code logic).

Quick Debug Checklist #

1. For file duplication:
   • Print each record immediately after reading it from the source file.
   • Ensure record variables are initialized inside the loop.
2. For database inserts:
   • Add error handling to catch and log exceptions.
   • Confirm you’re calling commit() after successful inserts.
   • Test your generated SQL queries directly in the database.

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