API/BUG: make .at raise same exceptions as .loc
- [x] closes #31722
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

This also (very) indirectly addresses #31683 which in turn will let us get rid of `CategoricalIndex.get_value` altogether.
> Can you check some timings for at? (as this is supposed to be the fast one)

Yah we take a hit

```
In [2]: ser = pd.Series(range(10000))                                           
In [3]: %timeit ser.at[501]                                                     
4.54 µs ± 139 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)  # <-- master
7.8 µs ± 75.2 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)  # <-- PR

In [4]: df = ser.to_frame("A")                                                  
In [5]: %timeit df.at[501, "A"]                                                 
3.9 µs ± 25.4 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)  # <-- master
7.71 µs ± 99.4 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)  # <-- PR
```

We might get some of that back from #31709. > How does this indirectly also address #31683 ? And if it does, shouldn't there be tests involving categorical / a whatsnew?

With this in place, all the places from which index.get_value are called are preceeded by maybe_cast_scalar_indexer (except one in which the key is a tuple).  That makes the call inside CategoricalIndex.get_value unnecessary, which addresses the motivating issue for #31683.  The "indexing on CategoricalIndex is hard" problem is still a problem. thanks @jbrockmendel  @jreback maybe you didn't notice, but *this time* I had a "request changes" open, as you asked previous time to indicate that you shouldn't merge without me having the chance to answer back. 
If there is still discussion going on, please give it some time. @jbrockmendel can you do a follow up with:

- fixing the performance regression
- fixing the usability regression in the error message > fixing the usability regression in the error message

Looking back at it, it's actually mainly the long fully qualified class name that is annoying, so that is an easy fix -> https://github.com/pandas-dev/pandas/pull/31769