How to properly create sql connector in multi-user environment

Dash Python
1

I am wondering how I would properly create connector/s to my mysql database in an app that is used by multiple users.

When developing my app (where I am the only user) I create the connector in a separate .py-file and import it into my app.py where I use it in different callbacks:

# dataprep.py

username='user123'
password='supersecretpw'
host='1.2.3.4'
port='4567'
database='mydb'

cnx = create_engine('mysql+pymysql://' + str(username) + '@' + str(host) + '/' + str(database) + '')

# app.py

from dataprep import cnx

When it comes to multiple users (which I have not yet tried) I have the following thoughts/questions:

  • Do I need more than one set of credentials for accessing the database? If so, how would I make my app use a different set of username and password for each user of my app?
  • Given the case all app users use the same database connector: if one user would make a big query on the database how would this influence the app performance for others users of my app which also query the database? Do the queries using the same database connector run in parallel or one after the other?
  • Should I keep creating the connector in a separate file or should this be done in the app itself, maybe even inside every callback?

Thank you

2

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 :slight_smile:

3

Hey @martin2097, thank you so much for your comprehensive answer. Looks straightforward to me. I am currently in the process of building my database and will need a little more time before I can implement your proposed solution. I will definitly have some questions in the near future and will come back to you!

1 Like