os.remove() on Windows fails if the file is already open#

I puzzled over this one for quite a while this morning. I had this test that was failing with Windows on Python 3.11:

1
@pytest.mark.parametrize(
2
    "use_path,file_exists", [(True, True), (True, False), (False, True), (False, False)]
3
)
4
def test_recreate(tmpdir, use_path, file_exists):
5
    filepath = str(tmpdir / "data.db")
6
    if use_path:
7
        filepath = pathlib.Path(filepath)
8
    if file_exists:
9
        Database(filepath)["t1"].insert({"foo": "bar"})
10
        assert ["t1"] == Database(filepath).table_names()
11
    Database(filepath, recreate=True)["t2"].insert({"foo": "bar"})
12
    assert ["t2"] == Database(filepath).table_names()

The test checks that the recreate=True option to my Database() constructor deletes and re-creates the file.

Here’s the implementation of that recreate=True option:

1
elif isinstance(filename_or_conn, (str, pathlib.Path)):
2
    if recreate and os.path.exists(filename_or_conn):
3
        os.remove(filename_or_conn)
4
    self.conn = sqlite3.connect(str(filename_or_conn))

On Windows I was getting the following exception:

1
FAILED tests/test_recreate.py::test_recreate[True-True] -
2
  PermissionError: [WinError 32] The process cannot access the file because it is being used by another process:
3
  'C:\\Users\\runneradmin\\AppData\\Local\\Temp\\pytest-of-runneradmin\\pytest-0\\test_recreate_True_True_0\\data.db'

Eventually I spotted the problem: my call on this line was opening a SQLite connection to the data.db file:

1
Database(filepath)["t1"].insert({"foo": "bar"})

But it wasn’t explicitly closing the SQLite connection. It turns out that leaves the database file open - and since the file is still open Windows raised an exception when os.remove() was called against it.

I fixed the error by closing the SQLite3 connection in my test, like this:

1
db = Database(filepath)
2
db["t1"].insert({"foo": "bar"})
3
assert ["t1"] == db.table_names()
4
db.conn.close()