Enable server side cursors
State: closed

This pull request attempts to fix #35689. I read an article by @itamarst and decided to look further into the codebase. After noticing that the fix semed to be simple I decided file a PR. The fix is naive and I'm not sure the true solution is as simple as I imagine it to be. There are probably many things I'm missing as I've not read the codebase in detail so do let me know what else needs to be addressed.

- [x] closes #35689
- [x] tests added / passed
- [x] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [x] whatsnew entry(N.A.)



Labels:
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
Thanks for doing this! You could perhaps only do the stream_results=True conditionally, if chunksize is set.
As context, this is a memory usage performance improvement (in some cases). You can see the article here (https://pythonspeed.com/articles/pandas-sql-chunking/) with some memory-usage graphs.

With streaming mode, rows are only fetched on demand. If you're using chunking, this is what you want, because otherwise you might try to load gigabytes of rows into memory and then dole them out in chunks, potentially running out of memory.

If you're _not_ using chunking, though, streaming is probably a runtime performance hint, because there's a bunch more network latency involved, and you already know you want all the data so why not just get it all in one go? (See #40847 for follow-up work that would use streaming cursors everywhere, but I don't think it should always be on).
Hey @itamarst  and @jreback,

Thanks for the input! Will do, will address the comments and revert shortly.

Update: I note the PEP8 style error below, will address it tomorrow when I add

1. a reference in `sql.py` to the article
2. a test that fails current and passes with the addition of the new parameter
3. A whatsnew note for 1.3 under the perf section
Hello @J0! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:



There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2021-07-02 04:15:17 UTC
This pull request is stale because it has been open for thirty days with no activity. Please update or respond to this comment if you're still interested in working on this.
Yes, I am still interested in working on this and will take a look at this on the weekend
> Yes, I am still interested in working on this and will take a look at this on the weekend

Thanks @J0 . closing as stale. ping when ready



Okay, will do. Sorry -- work got a bit busy. Will try to make time for it in this weekend or over an evening. Thanks for your understanding
Hey! @simonjayhawkins,

I'm working on this right now -- any chance I could get this re-opened?

Have addressed the past issues and would like to see if all the checks are passing.


Thanks!
Oh dear, looks like the coverage tests aren't passing.

Not too sure what's the best way to go about writing a failing this for this since the main change is in the option passed in

Will think about this but just wanted to check if  @itamarst and @jreback had any suggestions
My guess is the failure is unrelated: it timed out after 60 minutes. I would just push a meaningless change and see if it passes this time.
Looks like I need approval to run the workflows D: @simonjayhawkins could I trouble you again for help with requesting for approval to run the branch? 

Thank you so much  -- am aware you probably are quite busy.
Hey, does anyone have suggestions on how I might get the tests to pass? a little clueless at this point.
In a large project like this, it's possible (a) to have intermittent failures (b) for things to break in ways that are unrelated to your code. So general procedure is:

1. Try rerunning with meaningless commit.
2. If you get the exact same failure, and it's clear that it's not related to your code, merge forward or rebase, depending on how the project does it.
Workflows :( any chance for help with enabling the workflows again? @simonjayhawkins 
Thanks for the PR, but appears that this PR has gone stale again. If you're interested in continuing further by merging master and addressing the comment, feel free to let the team know and we can reopen.
@mroeschke apologies for the delay. I now have time to patch this again -- by any chance could we reopen this? Or would it be better if I opened a new PR? 

Lmk!
@J0 appears I cannot reopen this pull request as our master branch was renamed to main recently. I would recommend to open a new PR with the change.
@mroeschke sure, sounds good, will open a new PR! :)
Re-opened at https://github.com/pandas-dev/pandas/pull/46166