[C++][Python] Converting string to timestamp silently overflows

### Description:

When using the `pa.compute.cast` function to cast a string representation of a timestamp to a timestamp data type with a specified unit (e.g., "ns"), the function does not raise an overflow exception when the input string represents a timestamp that is out of bounds.

### Steps to Reproduce:

```python
import pyarrow as pa
result = pa.compute.cast("0001-01-01T00:00:00.0000000", pa.timestamp("ns"))
print(result)
```

### Expected Behavior:

I expect the pa.compute.cast function to raise an overflow exception when attempting to parse a timestamp that is out of bounds, indicating that the provided timestamp is invalid.

### Actual Behavior:

The pa.compute.cast function does not raise an overflow exception and returns a TimestampScalar object with a value that appears to be incorrect: Timestamp('1754-08-30 22:43:41.128654848').

### Additional Information:

Python version: 3.11.2
pyarrow version: 13.0.0
Operating System: Windows 11

### Component(s)

Python