Parallelize test_sql.py - Issue #60378
- [ ] Closes #60378
- [ ] Builds off of https://github.com/pandas-dev/pandas/pull/60595
- [ ] Extends @UmbertoFasci's per-table UUID work
- [ ] Solves https://github.com/pandas-dev/pandas/pull/60595 concurrency issue

~~- [x] Parallelizes test_sql.py via per-worker_DBs (approach superseded due to complexity)~~

Thanks for the feedback @mroeschke!

Having now run this through CI, I agree! I come from a production-oriented environment, so I initially underestimated the architecture/OS complexity. 

I looked into per-table UUIDs as per @UmbertoFasci's PR, and was concerned it might impact future test writing, and it still had some parallelization issues. I thought that using a different approach could solve these problems, but I see the value of this approach. 

I will revise this to use UUIDs on a per-test basis (building off of @UmbertoFasci's work). I'm not ready for review yet. 

I added the connect_and_uuid fixture, this was the most parsimonious way I found to manage the sqlalchemy connection objects, as well as sharing the same UUIDs between tests (which create views and tables) and fixtures (which tear down views and tables). Working to extend this fixture to the rest of the tests. Thanks for your efforts here so far, but personally the scale of these changes are too large to review. I would suggest breaking this into smaller PRs such as

1. A PR to ensure each test that creates a table also remove the table.
2. Update a fixture that impacts ~5 tests to return the unique table name in the fixture so it can used directly in the test if needed. This may involve changing functions that load data into pytest fixtures
3. Repeat 2 for ~10 tests at a time

 Hello @mroeschke 

That sounds like a plan to me! I'll continue working here to make sure that these changes (and by extension all subsequent PR) pass CI, and then I'll write one PR with just the new fixtures.

After the PR with the new fixtures gets approved, I will make a set of PRs. These will all be boilerplate changes, almost all tests are parallelized in the exact same way so this should be easy to review. 

There are a couple tests which do things like "close the connection and then reopen it", so since these tests are different I'll put them in their own PR.

Feel free to tell me if there are any issues with this plan. This pull request is stale because it has been open for thirty days with no activity. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) and respond to this comment if you're still interested in working on this.