BUG: read_sql with chunksize fails due to connection already closed

[cdcadman]

I'm creating this issue to document a bug which exists on the main branch. The bug was created by #49531, and I have an open PR to fix it: #49967. The bug does not exist in the latest release, so I did not use the issue template.

Example:

from pathlib import Path
from sqlalchemy import create_engine
from pandas import DataFrame, read_sql_query

db_file = Path(r"C:\Temp\test.db")
db_file.unlink(missing_ok=True)
db_uri = "sqlite:///" + str(db_file)
engine = create_engine(db_uri)
DataFrame({'a': [2, 4], 'b': [3, 6]}).to_sql('test_table', engine)
for _ in read_sql_query('select * from test_table', engine, chunksize=1):
    pass

Traceback:

Error closing cursor
Traceback (most recent call last):
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\cursor.py", line 981, in fetchmany
    l = dbapi_cursor.fetchmany(size)
sqlite3.ProgrammingError: Cannot operate on a closed database.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1995, in _safe_close_cursor
    cursor.close()
sqlite3.ProgrammingError: Cannot operate on a closed database.
Traceback (most recent call last):
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\cursor.py", line 981, in fetchmany
    l = dbapi_cursor.fetchmany(size)
sqlite3.ProgrammingError: Cannot operate on a closed database.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Github\pandas\read_sql_bug.py", line 10, in <module>
    for _ in read_sql_query('select * from test_table', engine, chunksize=1):
  File "C:\Github\pandas\pandas\io\sql.py", line 1534, in _query_iterator
    data = result.fetchmany(chunksize)
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\result.py", line 1115, in fetchmany
    return self._manyrow_getter(self, size)
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\result.py", line 542, in manyrows
    rows = self._fetchmany_impl(num)
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\cursor.py", line 1822, in _fetchmany_impl
    return self.cursor_strategy.fetchmany(self, self.cursor, size)
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\cursor.py", line 987, in fetchmany
    self.handle_exception(result, dbapi_cursor, e)
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\cursor.py", line 955, in handle_exception
    result.connection._handle_dbapi_exception(
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\cursor.py", line 981, in fetchmany
    l = dbapi_cursor.fetchmany(size)
sqlalchemy.exc.ProgrammingError: (sqlite3.ProgrammingError) Cannot operate on a closed database.
(Background on this error at: https://sqlalche.me/e/14/f405)