ENH: enable server side cursors when chunksize is set
Please look at the previous attempts in #40796 and #46166.  A lot of changes have happened since that PR. Since `SQLTable` (or `PandasSQL`) seems polymorphic, I did not want to change the function signature of `execute()` and noticed the use of `self.returns_generator` which can be used to use server-side cursors or not.


- [x] closes #35689
- [x] [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
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [x] 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.

Does postgres support this? It would be nice if we can add a test that asserts this actually happens.

I suppose it would also be limited to only some of the drivers. I know the ADBC postgres driver does not implement this yet, guessing libpq does? > Does postgres support this?

  Yes, postgres supports this. Please take a look at:
  - [Using Server Side Cursors (a.k.a. stream results) - SQLAlchemy](https://docs.sqlalchemy.org/en/20/core/connections.html#using-server-side-cursors-a-k-a-stream-results)

  Also note that what we need here is to avoid fetching all the results into the memory, so the title is a bit misleading. My understanding is that, even if the backend does not support server-side cursors, `stream_results=True` indicates to the dialect that results should be “streamed” and not pre-buffered, if possible.

  > Server side cursors also imply a wider set of features with relational databases, such as the ability to “scroll” a cursor forwards and backwards. SQLAlchemy does not include any explicit support for these behaviors; within SQLAlchemy itself, the general term **“server side cursors” should be considered to mean “unbuffered results” and “client side cursors” means “result rows are buffered into memory before the first row is returned”**.

 Also note that SQLAlchemy by default buffers 1000 rows, which may be less efficient for small results.

 > When a [Result](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Result) object delivered using the [Connection.execution_options.stream_results](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection.execution_options.params.stream_results) option is iterated directly, rows are fetched internally using a default buffering scheme that buffers first a small set of rows, then a larger and larger buffer on each fetch up to a pre-configured limit of 1000 rows. The maximum size of this buffer can be affected using the [Connection.execution_options.max_row_buffer](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection.execution_options.params.max_row_buffer) execution option:

---

>  It would be nice if we can add a test that asserts this actually happens.

Since this is mostly a memory optimization, I am not sure how to test this. I don't want to mock it as much as possible. If you have any ideas, I'm happy to add a test.

> I suppose it would also be limited to only some of the drivers. 

SQLAlchemy docs says server-side cursors is supported for ["mysqlclient, PyMySQL, mariadbconnector dialects and _may also be available in others_"][1] for `mysql` and  ["psycopg2, asyncpg dialects and _may also be available in others_"][2] for postgres. Oracle uses server-side cursors by default.

Streaming may be supported by other databases too (snowflake does as far as I see). [`psycopg` internally buffers everything unless we use server-side cursors][4].

> I know the ADBC postgres driver does not implement this yet, guessing libpq does?

My understanding is that server side cursors (aka named cursors) [in postgres] are created through SQL commands, so I am not sure if this needs libpq support. But I am not familiar with libpq and haven't used ADBC at all, so I maybe wrong here. Please see [Server Side Cursors - psycopg][3].


[1]: https://docs.sqlalchemy.org/en/20/dialects/mysql.html#server-side-cursors
[2]: https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#server-side-cursors
[3]: https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#server-side-cursors
[4]: https://github.com/psycopg/psycopg/discussions/58

 I have added a simple test to check for `execution_options`. > Since this is mostly a memory optimization, I am not sure how to test this. I don't want to mock it as much as possible. If you have any ideas, I'm happy to add a test.

Probably the best way to assert this actually does something is to add a `peakmem` benchmark to `asv_bench/benchmarks/io/sql.py` 
 This pull request is stale because it has been open for thirty days with no activity. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) and respond to this comment if you're still interested in working on this. Thanks for the pull request, but it appears to have gone stale. If interested in continuing, please merge in the main branch, address any review comments and/or failing tests, and we can reopen. I'm so sorry to disappear like this. A lot of things happening both personally and at work that I won't be able to get to it in the short time.

If anyone wants to contribute, the only thing missing from this PR is a _memory_ benchmark (although I don't think it's important). 
