title: Python: Updating a CSV file in place
body: <p>Is there a sensible way to update CSV data, in the same file, one row at a time?</p>
  <p>I have a CSV of records to inspect and potentially fix, a one-off task I am solving with an ad-hoc script. The script will require iteration to make it work correctly. It will categorise each record, decide on an action, attempt that action and record the result. I want to append these stages to the CSV:</p>
  <pre><code>id,status,action,result
  123
  234
  345
  456
  </code></pre>
  <p>to</p>
  <pre><code>id,status,action,result
  123,OK,None
  234,INVALID,Fix,OK
  345,INVALID,Fix
  456
  </code></pre>
  <p>Oh no, my script crashed while trying to fix 345 due to a bug! So I'll fix the bug and run the script again, and it will skip the rows where <code>status:OK</code> or <code>result:OK</code>:</p>
  <pre><code>id,status,action,result
  123,OK,None
  234,INVALID,Fix,OK
  345,INVALID,Fix,OK
  456,OK,None
  </code></pre>
  <p>Is this possible?</p>
  <p>One approach is to load the CSV into memory then overwrite the original file:</p>
  <pre class="lang-py prettyprint-override"><code>fieldnames = &quot;id status action result&quot;.split()
  data = []
  
  def process_and_update(record):
      if record[&quot;status&quot;] != &quot;OK&quot;:
          ...
  
  
  with open(&quot;file.csv&quot;, &quot;r&quot;, newline=&quot;&quot;) as input_file:
      reader = csv.DictReader(input_file, fieldnames=fieldnames)
      next(reader) # skip header row
      for r in reader:
          data.append(r)
  
  with open(&quot;file.csv&quot;, &quot;w&quot;, newline=&quot;&quot;) as output_file:
      writer = csv.DictWriter(output_file, fieldnames=fieldnames)
      writer.writeheader()
      for record in data:
          process_and_update(record)
          writer.writerow(record)
  </code></pre>
  <p>This doesn't work because if <code>process_and_update(record)</code> throws an unexpected error then the remaining data is lost.</p>
  <p>The method I'm currently using is to write to a separate file:</p>
  <pre><code>with open(&quot;file1.csv&quot;, &quot;r&quot;, newline=&quot;&quot;) as input_file, \
       open(&quot;file2.csv&quot;, &quot;w&quot;, newline=&quot;&quot;) as output_file:
       
      reader = csv.DictReader(input_file, fieldnames=fieldnames)
      next(reader)
      writer = csv.DictWriter(output_file, fieldnames=fieldnames)
      writer.writeheader()
  
      for record in reader:
          record.update(process_and_update(record))
          writer.writerow(record)
  </code></pre>
  <p>But this requires action to give the script correct filenames each time. If I keep using the original file as input, it will do the status-checking over and over again, which is slow.</p>
  <p>Or should I just put the data into an sqlite file?</p>