[Python] Provide more informative error message for certain numpy -> arrow conversions #51678

When converting a numpy array to pyarrow with a specified type, for certain cases we have a good error message, such as:

>>> pa.array(np.array([1, 2], dtype="int64"), pa.string())
..
ArrowTypeError: Expected a string or bytes dtype, got int64

>>> pa.array(np.array([1.5, 2.5], dtype="float64"), pa.int64())
..
ArrowInvalid: Float value 1.500000 was truncated converting to int64

But some others have a less informative error:

>>> pa.array(np.array(["a", "b"], dtype="U"), pa.float64())
...
ArrowNotImplementedError: NumPy type not implemented: unrecognized type (19) in GetNumPyTypeName

(while the type is actually recognized, it converts fine without the type specifier)

Triggered by #51157 (comment), which adds a custom check to avoid such an error in case of the numpy StringDType, but this could use a more general solution (or expanded to other dtypes)