[Python] Consistent to_pandas_dtype (and to_pandas) behavior for canonical extension types

### Describe the enhancement requested

Tracking / discussion issue spun out of the review on https://github.com/apache/arrow/pull/50145 (which
implements `FixedShapeTensorType.to_pandas_dtype`, GH-49907).

Today all canonical extension types (`bool8`, `json`, `uuid`, `opaque`,
`fixed_shape_tensor`, …) inherit `DataType.to_pandas_dtype`, which raises
`NotImplementedError`. As a result `to_pandas` / `Table.to_pandas` fall back to
converting the storage (often an object/numpy column), and
`Table.to_pandas(split_blocks=True)` raises `KeyError` for these columns.

https://github.com/apache/arrow/pull/50145 returns `pandas.ArrowDtype(self)` from
`FixedShapeTensorType.to_pandas_dtype` — a pandas `ExtensionDtype` implementing
`__from_arrow__` — which fixes the error and yields a faithful, round-trippable
extension column on pandas >= 2.1. This issue tracks extending that approach and
the open questions raised in review.

### Open questions

1. **Which canonical extension types should implement `to_pandas_dtype`, and to
what?** `pd.ArrowDtype(self)` is a sensible generic default, but some types may
map more naturally to a native pandas dtype (e.g. `bool8` → a boolean dtype).
Decide per-type vs. a shared default on `BaseExtensionType` — note a
`BaseExtensionType` default would also change behavior for *user-defined*
extension types, which relates to the `ExtensionScalar.as_py()` fallback in
#33134.

2. **Implications for `to_pandas` / `Table.to_pandas`.** Returning a dtype with
`__from_arrow__` changes conversion from the storage/object fallback to a
faithful extension-typed column. Pros: round-trips preserve the type,
`split_blocks=True` works. Cons: user-facing behavior change (changelog
needed); gated to pandas >= 2.1 (reliable `ArrowDtype` extension blocks,
GH-35821). `types_mapper` continues to take precedence.

3. **Docstring cleanup.** `BaseExtensionType` and its subclasses inherit
`to_pandas_dtype` (and related methods) from `DataType` with no mention of
extension-specific behavior; document this.

### Proposed direction

Keep https://github.com/apache/arrow/pull/50145 scoped to `fixed_shape_tensor`; handle the rest as small
follow-up PRs, each with its own changelog note:

- [ ] `bool8`
- [ ] `uuid`
- [ ] `json`
- [ ] `opaque`
- [ ] Docstring pass over `BaseExtensionType` + subclasses documenting
`to_pandas_dtype` / `to_pandas` behavior

cc @AlenkaF @jorisvandenbossche

### Component(s)

Python