How to close a SQLAlchemy session?

There's a central confusion here over the word "session". I'm not sure here, but it appears like you may be confusing the SQLAlchemy Session with a MySQL @@session, which refers to the scope of when you first make a connection to MySQL and when you disconnect.

These two concepts are not the same. A SQLAlchemy Session generally represents the scope of one or more transactions, upon a particular database connection.

Therefore, the answer to your question as literally asked, is to call session.close(), that is, "how to properly close a SQLAlchemy session".

However, the rest of your question indicates you'd like some functionality whereby when a particular Session is closed, you'd like the actual DBAPI connection to be closed as well.

What this basically means is that you wish to disable connection pooling. Which as other answers mention, easy enough, use NullPool.

session.close() will give the connection back to the connection pool of Engine and doesn't close the connection.

engine.dispose() will close all connections of the connection pool.

Engine will not use connection pool if you set poolclass=NullPool. So the connection (SQLAlchemy session) will close directly after session.close().