Hello @Max3 !
I do not have direct experience with mysql database because we use oracle database but I believe this is a more general question about “best practice” for database connections.
Basically what your are describing is called sessions mangement. Each user in each database has some number of sessions available. It can be one or even fifty. Number depends on the set up, more sessions demands more powerful db hw but if you are an owner of the database you should be able to set up this number of sessions available.
For your application you can create just one “application user” in database and assign enough sessions to it. What “enough” means depends on length of your queries and on amount of these queries running at the same time. When your application runs out of sessions the query will not run and user will have to try it again later when some session will be available.
Last but not least you should do everything possible not to waste sessions by your application. Basically the session is occupied from the moment you open your connector until you close it. So it should be opened only when your application accesses the data. There is very clever pythonistic way of dealing with it and it is called a context manager. You can find more about it here: Context Managers and Python's with Statement – Real Python or anywhere else.
My custom context manager for the oracle db connection looks like this:
import oracledb
import contextlib
@contextlib.contextmanager
def database():
# set up database connection
db = oracledb.connect(user='login', password='passw', dsn='database')
yield db
# tear down database connection
db.close()
You can see the basic structure. What you just need to do is replace my connection to oracle db with your connection to any other database. What this context manager does is that it sets up the connection but if anything goes wrong (applications looses connection etc) your session won’t be hanging there inaccessible but it is closed and available for another use.
What is important now, you can access the data using with statement and your connection like this:
with database() as my_db:
query = """
select * from my table
"""
df_vintage_caps = pd.read_sql(query, con=my_db)
This is just basic example, you can do anything you want in the with statement. What is important if anything goes wrong your session won’t be hanging in the void awaiting termination.
I hope this answer helps you, if you have any additional answers I will try to help 