arrow

GH-39958: [Python][CI] Remove upper pin on pytest (#40487)

### Rationale for this change

The latest version of pytest (`8.0.0`) is breaking our CI:
- S3 fixture from out test suite fails
- `doctest-cython` check fails

### What changes are included in this PR?

- added `allow_bucket_creation=True` to the `s3_example_fs` fixture
- removed the pin on pytest, except for the doc builds

### Are these changes tested?

Yes.

### Are there any user-facing changes?
No

Closes:

- Closes https://github.com/apache/arrow/issues/39958
- Closes https://github.com/apache/arrow/issues/39957

* GitHub Issue: #39958

Lead-authored-by: AlenkaF <frim.alenka@gmail.com>
Co-authored-by: Joris Van den Bossche <jorisvandenbossche@gmail.com>
Signed-off-by: Joris Van den Bossche <jorisvandenbossche@gmail.com>