test_recreate failing on Windows Python 3.11

[simonw]

https://github.com/simonw/sqlite-utils/actions/runs/3323672128/jobs/5494726927

Related:

• Fix tests for Python 3.11 #502

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'
FAILED tests/test_recreate.py::test_recreate[False-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_False_True_0\\data.db'

[simonw]

Passes on Windows with other Python versions for some reason.

[simonw]

This is the failing test:

sqlite-utils/tests/test_recreate.py

Lines 21 to 32 in 7b2d1c0

	@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()

I'm going to try a different way of creating the temporary file: https://docs.pytest.org/en/7.1.x/how-to/tmp_path.html

[simonw]

Failed again, but just noticed this: https://github.com/simonw/sqlite-utils/actions/runs/3323932266/jobs/5494890223

>       Database(filepath, recreate=True)["t2"].insert({"foo": "bar"})

tests\test_recreate.py:31: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <[AttributeError("'Database' object has no attribute 'conn'") raised in repr()] Database object at 0x29fc125aa90>

[simonw]

Now mypy is failing:

sqlite_utils/db.py:474: error: Item "None" of "Optional[Any]" has no attribute "execute"
sqlite_utils/db.py:476: error: Item "None" of "Optional[Any]" has no attribute "execute"
sqlite_utils/db.py:486: error: Item "None" of "Optional[Any]" has no attribute "executescript"
sqlite_utils/db.py:603: error: Item "None" of "Optional[Any]" has no attribute "__enter__"
sqlite_utils/db.py:603: error: Item "None" of "Optional[Any]" has no attribute "__exit__"
sqlite_utils/db.py:604: error: Item "None" of "Optional[Any]" has no attribute "execute"
sqlite_utils/db.py:607: error: Item "None" of "Optional[Any]" has no attribute "execute"
sqlite_utils/db.py:1082: error: Item "None" of "Optional[Any]" has no attribute "__enter__"
sqlite_utils/db.py:1082: error: Item "None" of "Optional[Any]" has no attribute "__exit__"
sqlite_utils/db.py:1083: error: Item "None" of "Optional[Any]" has no attribute "cursor"
sqlite_utils/db.py:1155: error: Item "None" of "Optional[Any]" has no attribute "enable_load_extension"
sqlite_utils/db.py:1156: error: Item "None" of "Optional[Any]" has no attribute "load_extension"
Found 12 errors in 1 file (checked 51 source files)

[simonw]

I'm not hugely happy with my fix there:

sqlite-utils/sqlite_utils/db.py

Lines 321 to 328 in c5d7ec1

	if recreate and os.path.exists(filename_or_conn):
	try:
	os.remove(filename_or_conn)
	except OSError:
	# Avoid mypy and __repr__ errors, see:
	# https://github.com/simonw/sqlite-utils/issues/503
	self.conn = sqlite3.connect(":memory:")
	raise

The problem here was that in the case where the os.remove() failed the self.conn property was NOT being set to a valid connection - which caused __repr__ to fail later on.

So now I catch the os.remove() error, set self.conn to a memory connection, then raise the error again.

[simonw]

This test reliably fails on Windows with Python 3.11.

I'm going to skip the test for the moment to get back to green CI... but I'll leave this issue open. This is definitely concerning, I just don't have the right local environment to solve this at the moment.

[simonw]

... or maybe Windows doesn't like attempts to remove a file that the process has opened?

[simonw]

Used psutil to confirm that closing a SQLite connection closes the underlying file: https://til.simonwillison.net/python/too-many-open-files-psutil

>>> import psutil
>>> import sqlite3
>>> for f in psutil.Process().open_files(): print(f)
... 
>>> sqlite3.connect("/tmp/blah.db")
<sqlite3.Connection object at 0x1007264d0>
>>> conn = _
>>> for f in psutil.Process().open_files(): print(f)
... 
popenfile(path='/private/tmp/blah.db', fd=3)
>>> conn.close()
>>> for f in psutil.Process().open_files(): print(f)
... 
>>> 

[simonw]

That fixed it.

[simonw]

TIL about this: https://til.simonwillison.net/python/os-remove-windows