I got it running! I learned, understood, and even mastered it without LVM, using Btrfs subvolumes instead.
Is this the end of my story? Of course not! The next setup is waiting.
Thanks for the help @schnedan & @abmurksi

I finally got testing working the way I wanted it and it turns out, it did not take a lot of lines of code.

in my pytest.ini file, I set an env var with the file path to the sqlite database.

When the app runs, it checks that same env var and will default to the filename production.db if the env var is not set.

If the database name is the non-prod one, it will drop all the tables when the app launches.

The end result is that my test database is empty every time I run pytest and data that should persist in production.db will be preserved between app runs.