[CI][Python] Re-enable -W error on CI for Python jobs

[raulcd]

Describe the enhancement requested

Follow up from: #38239

We decided to deactivate errors on Warnings in order to have a passing CI for the Release and this is a follow up in order to re-enable them. The issue we faced can be seen here : #38238 and here: #38259

There seems to be an issue if we try to have an ignore quote that contains spaces, see:

$ export PYTEST_ARGS="-W error -W 'ignore:Setting custom ClientSession:DeprecationWarning'"
$ PYTHONASYNCIODEBUG=1 pytest python/pyarrow/tests/test_dataset.py::test_open_dataset_from_uri_s3_fsspec $PYTEST_ARGS
ERROR: while parsing the following warning configuration:

  'ignore:Setting

This error occurred:

invalid action: "'ignore"

We tried the approach of removing -W error from PYTEST_ARGS and changing setup.cfg to contain:

[tool:pytest]
addopts = --ignore=scripts
filterwarnings =
    error
    # https://github.com/apache/arrow/issues/38239
    ignore:Setting custom ClientSession:DeprecationWarning

but this configuration doesn't seem to be used as it did not fail if removing the ignore, as suggested here this is probably because we run the installed tests, not in-place (using --pyargs pyarrow)

If we try to use the setup.cfg we probably want to add some jobs with PYTEST_ARGS: -W always

Component(s)

Continuous Integration, Python