Good practices ragarding Engine initialisation and closing

[anselal]

1. In the example at https://github.com/aio-libs/aiomysql#example-of-sqlalchemy-optional-integration you acquire a connection but in the documentation at https://aiomysql.readthedocs.io/en/stable/sa.html you also create a transaction and commit.

2. In both examples you call 2 close functions. What is the reason for this?

3. What is a good practice to init the engine? Shall I init the engine every time I want to run a query and then call the 2 close functions?
   Or shall I init the engine just once and close the engine when my app terminates ?

4. Add more detailed examples

5. The library is very similar to sqlalchemy, I like it. It was not hard to migrate to an async driver.
   Maybe add a migration guide from sqlalchemy to aiomysql.
   Also, some sqlalchemy functions are not available, like one_or_none. But I read that fetchone returns None so I will use that

[anselal]

Sorry about opening an issue. I did it from my mobile and didn't see the discussion tab

[Nothing4You]

no worries, i can just convert them :)

[Nothing4You]

Hi,

to be honest, I haven't spent much time looking into SQLAlchemy yet.
I recently took over maintaining aiomysql and I still need to spend some time on the SQLAlchemy part.

The SQLAlchemy documentation in aiomysql as well as a good part of the general aiomysql documentation still needs a lot of work.
As of right now, aiomysql doesn't even support the most recent SQLAlchemy version 1.4.

To answer your questions:

1. Using transactions

Transactions are an optional feature, allowing you to e.g. group a series of statements to ensure they all are executed or none of them are.
They also allow you to prepare and roll back changes if you determine you don't want to execute them anymore.
You can find more documentation about this here: https://dev.mysql.com/doc/refman/8.0/en/commit.html

Note that you may want to either use autocommit=True or explicitly commit every time outside of transactions as well, which is currently not documented well.
See also #70 and #247.

2. 2 Close functions

engine.close() only tells it to start closing connections, but it doesn't synchronously close them.
Awaiting engine.wait_closed() ensures that the connections are actually closed.
See https://aiomysql.readthedocs.io/en/stable/sa.html#aiomysql.sa.Engine.close and https://aiomysql.readthedocs.io/en/stable/sa.html#aiomysql.sa.Engine.wait_closed.

3. Engine initialization

My understanding of SQLAlchemy is that it maintains a connection pool, so it would be beneficial to keep the same engine across the lifetime of your application.
By reusing the engine instance it allows reusing connections and can therefore improve performance.
If you keep creating new engine instances that would most likely keep closing connections and recreating new ones when you create new engine instances.

4. More detailed examples

See also #475.
As mentioned before, documentation still has a lot of room for improvement.
You may find a bit more in the SA tests: https://github.com/aio-libs/aiomysql/tree/master/tests/sa

5. Migration guide & missing functions

Contributions are welcome :)

[anselal]

Thank you for the detailed reply.
I updated my code to use the same engine and it seems to work fine. But when I restart or terminate my application I get an "Unclosed Client Session" error, also a "RUNTIME ERROR: Event loop is closed".
Not sure how to handle this.

FYI. I am using your async driver in my discord bot

[anselal]

@Nothing4You Regarding the close functions. Since we use a with statement we shouldn't be responsible for closing the connection.
This is the meaning of the with statement, to implement enter and exit