Cannot Delete a Python Named Temporary File in Windows

The call tempfile.NamedTemporaryFile returns a wrapper around a file. In your case temp_file, which has been returned with the call, has attributes name (the file name) and file (the underlying system file). You can use either temp_file or temp_file.file for I/O operations. But nowhere in you code have you issued a call to close on the file. So when you go to remove the file you get the error you get.

You should either code an explicit temp_file.close() (or temp_file.file.close()) or use a context manager:

with tempfile.NamedTemporaryFile(mode="w+", newline="", suffix=".csv", delete=False) as temp_file:
    with temp_file.file as file:
        ...
    # The file will be closed when we reach here

Also, when you use tempfile.NamedTemporaryFile, the file is already opened for you so you should not issue an explicit open.

Windows won’t let you delete a file while any handle has it open, and in your code you open the temp twice (the NamedTemporaryFile plus open(...,'rb')) so the upload handle stays open and Windows blocks os.remove. (Source)

Fix: close the file you send (use a with open(..., "rb") as f: for the upload) so the handle is closed before os.remove(); also on Windows prefer delete=False and explicitly close the temp before reopening. Example:

with tempfile.NamedTemporaryFile(mode="w", suffix=".csv", delete=False) as tmp:
    tmp.write(csv_text)

with open(tmp.name, "rb") as f:
    files = [('file', (os.path.basename(tmp.name), f, 'text/csv'))]
    resp = requests.put(url, headers=headers, files=files)

os.remove(tmp.name)

Python’s tempfile docs explain the Windows behavior (you must close or use delete=False), and Microsoft’s docs explain you cannot delete files that are open by a process — that’s why this happens and why Windows’ file-locking makes this workflow fiddly. (Source)

Collect all your data and close the file handle by exiting the with block.

Open the file for reading within the requests call using a second with block (to make sure it closes immediately after sending).

Place the os.remove call inside a try...finally block to ensure it happens even if the upload fails, and ensure it is before the return statement.

Corrected Code:

import os
import csv
import pyodbc
import logging
import requests
import tempfile

def update_list(list_info, token, APIKEY):
    logging.info(f"Updating list {list_info['name']} using session token {token}.")
    
    # 1. Create and Write to the file
    # We use delete=False because Windows handles file locks strictly
    temp_path = None
    try:
        with tempfile.NamedTemporaryFile(mode="w+", newline="", suffix=".csv", delete=False) as temp_file:
            temp_path = temp_file.name
            logging.info(f"Using temp file {temp_path}.")

            conn_str = (
                f"DRIVER={{{DRIVER}}};"
                f"SERVER={list_info['server']};"
                f"DATABASE={list_info['database']};"
                "Trusted_Connection=yes;"
                "TrustServerCertificate=yes;"
            )

            with pyodbc.connect(conn_str) as conn:
                cursor = conn.cursor()
                cursor.execute(list_info["query"])
                
                writer = csv.writer(temp_file)
                writer.writerow([column[0] for column in cursor.description]) # Headers
                for row in cursor:
                    writer.writerow(row)
        
        # At this point, the 'with' block has ended. 
        # The file is SAVED and CLOSED. Windows will now allow other operations.

        # 2. Upload the file
        headers = {'X-Gatekeeper-SessionToken': token, 'FastField-API-Key': APIKEY}
        
        # We open the file in read-binary mode inside another 'with' 
        # so it closes immediately after the request is finished
        with open(temp_path, 'rb') as f:
            files = [('file', (os.path.basename(temp_path), f, 'text/csv'))]
            logging.info(f"Sending list csv file to {list_info['url']}.")
            response = requests.put(list_info["url"], headers=headers, files=files)
        
        logging.info(f"Received response {response.text}.")
        result_text = response.text

    finally:
        # 3. Cleanup: This runs regardless of success or errors above
        if temp_path and os.path.exists(temp_path):
            try:
                os.remove(temp_path)
                logging.info(f"Successfully deleted temp file {temp_path}.")
            except Exception as e:
                logging.error(f"Failed to delete temp file: {e}")

    # 4. Return the result AFTER cleanup
    return result_text