The document does not mention it.
Comment From: verascity
I think this is a good point. Maintainers, correct me if I'm wrong (I'm a new contributor), but I believe it does have to be manually closed. I had to search for a bit to verify that, though.
If confirmed, could I take on clarifying in the docs?
Comment From: CalmDownKarm
If I understand correctly, you shouldn't need to manually close connections, sqlalchemy's create_engine() essentially handles connections through a connection pool automatically. calling dispose() on a create_engine()'d object will just replace it with an empty connection pool. sqlalchemy docs
Comment From: TomAugspurger
The question from @FukoH is a bit unclear though. @CalmDownKarm is correct about sqlalchemy connection pooling. We also support raw connections for sqlite (though that's maybe deprecated?).
Regardless, I don't think pandas closes anything internally. A link to the sqlalchemy docs in read_sql and to_sql may would be nice to have.
Comment From: verascity
The sqlite info is a little ambiguous. On read_sql it seems to imply sqlite is non-preferred but supported:
con : SQLAlchemy connectable(engine/connection) or database string URI or DBAPI2 connection (fallback mode) Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.
But on to_sql, it outright says sqlite is only supported as legacy.
con : sqlalchemy.engine.Engine or sqlite3.Connection Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided for sqlite3.Connection objects.
So maybe the read_sql doc should also be updated to reflect that, and then there can be a link to the SQLAlchemy docs in both?
Comment From: CalmDownKarm
@verascity are you planning on submitting a PR for this?
Comment From: verascity
I'd like to, yes! Just wanted to get it pinned down before I do it.
Comment From: TomAugspurger
I think we're OK for this specific issue: pandas doesn't do anything to open or close your database connections. That's left up to the user / underlying object. A link to the sqlalchemy docs on this subject should be included.
Comment From: emattiza
I'll take a shot at getting this one documented!
Comment From: cruzzoe
https://github.com/pandas-dev/pandas/pull/29363
Comment From: felipedmc
Hey there! Some years later... no explict engine, conection or cursor, and my code goes something like:
lazydummy = pandas.read_sql(sql= 'SELECT * FROM somewhere' , con= 'postgre://blablabla') . . #doing stuff with my dataframe . display(lazydummy.head() #BREAKPOINT HERE! END OF SCRIPT
At the break point, I alt+tab to my pgadmin 4 and the conection was there, opened. Back to my phyton code, finished running it, gone back to pgadmin and the conection was not there anymore on my db dashboard. Guess it just autocloses when the script is over.