TST: use fixtures for some sql database connections
xref #40686

This PR:

1. Create fixtures for all supported SQL connections, including sqlalchemy engine/connection and sqlite3 connection. Setup and cleanup are handled within fixtures.
2. Migrate several common tests to use connection fixtures. They serve as examples for future migrations.

We probably should put all the fixtures in a separate conftest.py file. But since there are unmerged PRs related to this test file, I kept everything in one place for now to avoid conflict.
This pull request is stale because it has been open for thirty days with no activity. Please update or respond to this comment if you're still interested in working on this. Thanks for the PR but appears that it has gone stale. Feel free to reopen if you have time to continue working on it! @fangchenli happyt o take this if you can fixup thanks @fangchenli 