TST: Run tests using a database explicitly

[datapythonista]

At the moment, our tests accessing a database (in pandas/tests/io/test_sql.py) are being skipped if the module to connect to the database are not installed (e.g. pymysql or psycopg2), or if the connection to the database doesn't succeed.

This has two implications:

• It's not easy in the CI to know which builds are running the db tests (we need to check for the dependencies, and also check if a db server is set up in the host)
• If there is an issue with the db, the tests will be skipped silently (we'll log that they are skipped, but I guess nobody will notice)

I think a better approach would be:

• Mark those tests in the cateogry db (i.e. @pytest.mark.db)
• Remove the skips for when the the connection fails (and possibly the one for when the db modules are not installed)
• In the CI add -m "not db" or -m "db" depending on whether we want to run the tests or not (this will be done with the env PATTERNS when TST/CI: Unify CI test scripts (single and multi) and simplify the system #23924 is merged)

@jreback is this ok?

[h-vetinari]

IMO, this applies verbatim to the tests for boto as well - it's hard to see where it's tested (actually only one build is testing them), and in case of errors, these get silently skipped in the s3_resource (except two tests that aren't using the fixture yet, which will be done as a side benefit of #23731).

This is especially critical since the boto tests will all be skipped once the environment starts pulling in a newer boto, due to #23754 (that's the reason I've held off on incorporating the feedback for #23731 so far).

[varadgunjal]

I'd like to take this up if the approach is confirmed.

[datapythonista]

@jreback are you ok with marking the db tests in pytest, and run them explicitly instead of skipping them when the connection fails (which will make them fail silently if the db fails)?

[jreback]

sure as long as they r skipped for users running pd.test() (iow treated like a slow test)