It is not clear whether database connection need to be closed manually after calling pd.read_sql()

[FukoH]

The document does not mention it.

[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?

[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

[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.

[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?

[CalmDownKarm]

@verascity are you planning on submitting a PR for this?

[verascity]

I'd like to, yes! Just wanted to get it pinned down before I do it.

[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.

[emattiza]

I'll take a shot at getting this one documented!

[cruzzoe]

#29363

[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.