refactor: use pytest and in-memory db for piccolo example tests (#2086) #6311

Job	Run time
validate	1m 45s
test (3.10, 2) / test	1m 48s
test (3.11, 1) / test	2m 10s
test (3.11, 2) / test	2m 35s
test (3.10, 1) / test	2m 24s
test (3.8, 1) / test	2m 16s
test (3.8, 2) / test	2m 7s
test (3.9, 1) / test	2m 4s
test (3.9, 2) / test	1m 54s
test-platform-compat (windows-latest) / test	3m 1s
test-platform-compat (macos-latest) / test	3m 22s
build-docs	0s
codeql	0s
sonar	0s
snyk	0s
	25m 26s

Provide feedback