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!