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:

@pytest.mark.parametrize(
    "use_path,file_exists", [(True, True), (True, False), (False, True), (False, False)]
)
def test_recreate(tmpdir, use_path, file_exists):
    filepath = str(tmpdir / "data.db")
    if use_path:
        filepath = pathlib.Path(filepath)
    if file_exists:
        Database(filepath)["t1"].insert({"foo": "bar"})
        assert ["t1"] == Database(filepath).table_names()
    Database(filepath, recreate=True)["t2"].insert({"foo": "bar"})
    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:

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

On Windows I was getting the following exception:

FAILED tests/test_recreate.py::test_recreate[True-True] - 
  PermissionError: [WinError 32] The process cannot access the file because it is being used by another process:
  '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:

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:

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

Related

• sqlite Saving an in-memory SQLite database to a file in Python - 2023-04-08
• pytest Testing a Click app with streaming input - 2022-01-09
• python Using psutil to investigate "Too many open files" - 2022-10-13
• sqlite Building a specific version of SQLite with pysqlite on macOS/Linux - 2021-08-14
• pytest Mocking subprocess with pytest-subprocess - 2023-03-08
• pytest pytest coverage with context - 2022-03-04
• sqlite SQLite VACUUM: database or disk is full - 2022-08-29
• datasette Remember to commit when using datasette.execute_write_fn() - 2023-08-31
• sqlite Using pysqlite3 on macOS - 2021-07-10
• python Running PyPy on macOS using Homebrew - 2022-09-14

Created 2022-10-25T13:45:31-07:00 · Edit