ENH: support Arrow PyCapsule Interface on Series for export
- [ ] closes #59518 (Replace xxxx with the GitHub issue number)
- [ ] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.

Thanks @MarcoGorelli  Absolutely my mistake for not putting up a "changes requested," but I don't think this was ready to be merged. @jorisvandenbossche has some great points that need to be addressed, and I still want to discuss a bit more about how we feel the pd.Series should be exported through the Arrow C Data interface. 

As mentioned before I am a little wary about having developers write extensions that treat the Series as a stream (when in the vast majority of current use cases it is not) and how that may impact us as both a consumer and producer of Arrow C Data To talk more practically, I am wondering about a scenario where we have a series that holds a chunked array. In this PR, we convert that to a singular array before then exposing it back as a stream, but the other point of view is that we could allow the stream to iterate over each chunk.

But then the question becomes what happens when that same Series gets used in a Dataframe? Does the dataframe iterate its chunks? In most cases, it seems highly unlikely that this is possible (unless all other Series of the Dataframe share the same chunked array size), so you get a rather interesting scenario where iterating by dataframe could be potentially far more expensive than the Series iteration.

The more I think through it I am leaning towards -1 on supporting the stream interface for the Series; I think we should just expose as an array for now Sorry if I merged too soon. Yeah I think generally our ArrowEA should just be backed by a contiguous `pyarrow.array` instead of a `pyarrow.chunked_array`, especially if it makes it easier to decide to implement array over stream.

IIRC there are some ops where we need to `.combine_chunks` beforehand so it would be nice to avoid this copy. I'm not sure if there are any ops in pandas where operating on chunks is more optimal than the whole array ah sorry about this, I should've marked as draft whilst there was still some discussion underway

i'll open up a follow-up shortly and we can iterate on the required changes there (I think that's simpler than reverting and starting again) The fixes for the current implementation can indeed be done in a follow-up PR. 
And let's continue the discussion about stream vs array interface for `Series` in the original issue (I'll reopen it).



> Yeah I think generally our ArrowEA should just be backed by a contiguous `pyarrow.array` instead of a `pyarrow.chunked_array`, especially if it makes it easier to decide to implement array over stream.

_If_ we want to consider changing that, let's open a new dedicated issue about it (we discussed the pros/cons quite extensively in the past, and it has much larger impact beyond this stream interface)

