REGR: to_stata tried to remove file before closing it
State: closed

Spin-off from #39047:

If an error occurred during `to_stata`, the code tried to first remove the file and then close the file handle:

1. removing the file failed on Windows (it is still opened)
2. It created an empty file when closing the handle


Labels:
name: IO Stata
description: read_stata, to_stata

Comments:
this is a regression (i think so?). put a note on 1.2.1
yes, you are right! I extended a test to make sure that the file doesn't exists and added a whatsnew entry.
The failing test will not fail in 'real life': the test uses `tm.ensure_clean()` to ensure that the file will not exist once the test is done. Unfortunately, this means that we have an open file handle that `to_stata` cannot close. I will look into re-writing `ensure_clean()`.
also some failures 
I think `to_stata` is the only `to_*` method that tries to remove created files in case of an error. Is that something other methods should also try to do?

I'm not sure but I could imagine that `__exit__` in `IOHandles` might be able to determine whether an exception from within pandas's io part was raised: if yes, try to delete the files. This would probably only cover local files (no fsspec strings) and only work for `to_*` methods that use `with get_handles(...)` (some methods have to use it without the context manager and explicitly call `close` - `IOHandles` wouldn't know whether an exception occurred.
we should remove it if it's corrupt - however this might be hard to tell

so ok leaving it

@jreback green'ish

The new `ensure_clean` has the drawback (I see it as an advantage) that the code using it needs to make sure that no file handle references the temporary file. If an open file handle exists, the test on windows will fail. That's why I had to fix a few tests in this PR.
@jreback  green'ish, failed tests seem to be unrelated
> failed test seem to be unrelated

yep, known flaky test and pre-commit issue
one question before this is merged. How public-ish is tm.ensure_clean?
> one question before this is merged. How public-ish is tm.ensure_clean?

its not
thanks @twoertwein 
@meeseeksdev backport 1.2.x
Owee, I'm MrMeeseeks, Look at me.

There seem to be a conflict, please backport manually. Here are approximate instructions:

1. Checkout backport branch and update it.

```
$ git checkout 1.2.x
$ git pull
```

2. Cherry pick the first parent branch of the this PR on top of the older branch:
```
$ git cherry-pick -m1 6ff2e7c602123787c3b0061466ab5bb8663eae81
```

3. You will likely have some merge/cherry-pick conflict here, fix them and commit:

```
$ git commit -am 'Backport PR #39202: REGR: to_stata tried to remove file before closing it'
```

4. Push to a named branch :

```
git push YOURFORK 1.2.x:auto-backport-of-pr-39202-on-1.2.x
```

5. Create a PR against branch 1.2.x, I would have named this PR:

> "Backport PR #39202 on branch 1.2.x"

And apply the correct labels and milestones.

Congratulation you did some good work ! Hopefully your backport PR will be tested by the continuous integration and merged soon!

If these instruction are inaccurate, feel free to [suggest an improvement](https://github.com/MeeseeksBox/MeeseeksDev).
                
@twoertwein would you mind putting up the backport PR