Do we really need to open a new db connection every time we insert a new post?

[KilianB]

If sqllite for py isn't entirely different than every other db lib I have worked with:

https://github.com/xXAligatorXx/RepostCheckerBot/blob/b11b397baed745df82ae439aacba0ff9bd430af8/database.py#L709-L719

We can reuse the connection, and it definitely should be closed once we are done with it. Closing the cursor is NOT! enough and you'll eventually run out of allowed connections we can establish

[A1igator]

more research: https://stackoverflow.com/questions/9561832/what-if-i-dont-close-the-database-connection-in-python-sqlite . Using contextlib appears to be the best method.

[KilianB]

connecting to a db usually is a pretty expensive operation. Keeping a connection alive isn't as awful as it sounds as long as it is handled properly in between threads (e.g. I have db connections open for upwards of a few weeks).
If you want to do some further research take a look at connection pools.

[A1igator]

Alright so to make sure I get this right. You are saying I should close the connection everytime I do a database operation, but use connection pools, which will cache connections to make opening them again fast?

[KilianB]

I may have to rephrase my answer

You are saying I should close the connection everytime I do a database operation

In this case we have to be a bit careful about terminology.

Opening and closing db connections adds overhead to your application. You absolutely do not want to establish a new connection for every request! If you are working with a fixed (and reasonable) number of threads it is reasonable to keep a connection alive for each, throughout the entire lifetime of your application. In this project you are good to go and use them this way.

Now imagine a webserver, a new user requires access to a db. In this case, once again, creating a new connection simply is to expensive. Best practice is to cache connections (the connection pool), retrieving and giving them back to the pool as required. This may or may not be the same syntax as closing the connection but think about it as just returning the object to the pool. The connections themselves are kept alive and served to the next requester as desired.

A nice explanation can be found here: https://pynative.com/python-database-connection-pooling-with-mysql/