Update MySQL data in dash app

Dash Python
1

Hello, this is my first forum post to this site and I am a new dash user. I am trying to create a dash app that takes user input (in this case data entry) and updates the existing data which is in a MySQL database. Keep in mind that there will be multiple people entering data simultaneously. I have a little experience creating dash apps and hosting them on Heroku, but they are typically just to take user input, quickly calculate something, and then graph it.

Is it possible to create a dash app that connects to a MySQL database, updates the data in there via user input, and then does some logic in the back end?

I am in the infancy of this project as of right now, but am interested in how a more experienced user may approach this task.

Thank you in advance.

2

Hey @aengland, welcome! That’s definitely possible. I would recommend using flask-sqlalchemy in conjunction with Dash. SQLAlchemy is a Python library for interacting with SQL databases. Dash is built on top of flask, and flask-sqlalchemy is an extension of flask that adds support for SQLAlchemy.

The flask-sqlalchemy docs, and the sqlalchemy docs are both very comprehensive, though consequently also maybe a little overwhelming.

The main thing you need to know when reading flask-sqlalchemy examples and porting them to Dash is to replace instances of app (which will be a Flask app in the examples) with app.server, which is the Flask app running your Dash app.

Here’s an example to get you started that I based on the flask-sqlalchemy minimal application from their docs. It uses a SQLite database saved to /tmp/. To get this working with your MySQL database you would just need to change the URI. I didn’t want to distract from the database logic, so the app itself is pretty ugly.

import dash
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output, State
from flask_sqlalchemy import SQLAlchemy

app = dash.Dash()
app.server.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:////tmp/test.db"
db = SQLAlchemy(app.server)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return "<User %r>" % self.username


app.layout = html.Div(
    [
        html.H4("username"),
        dcc.Input(id="username", placeholder="enter username", type="text"),
        html.H4("email"),
        dcc.Input(id="email", placeholder="enter email", type="email"),
        html.Button("add user", id="add-button"),
        html.Hr(),
        html.H3("users"),
        html.Div(id="users"),
    ]
)


@app.callback(
    Output("users", "children"),
    [Input("add-button", "n_clicks")],
    [State("username", "value"), State("email", "value")],
)
def add_and_show_users(n, username, email):
    if n is not None:
        # if button clicked, add user
        db.session.add(User(username=username, email=email))
        db.session.commit()

    # get all users in database
    users = db.session.query(User).all()
    return [
        html.Div(
            [
                html.Span([html.H5("Username: "), u.username]),
                html.Span([html.H5("Email: "), u.email]),
            ]
        )
        for u in users
    ]


if __name__ == "__main__":
    db.create_all()
    app.run_server()
2 Likes
3

many thanks @tcbegley that is exactly what I’m searching for. Do you have an idea to add a button that prints a report as PDF?

4

hi,
does the app create database in each run of the app?

5

No, it will just connect to it. The create_all method by default only creates any tables that you’ve created a model for that aren’t found in the database you just connected to.

With the code above

app.server.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:////tmp/test.db"

SQLite will create a new database if the file /tmp/test.db is not found, but in general if you put a connection string and SQLAlchemy can’t find the database it should just throw an error and quit. If it finds the database it will use the existing contents.

1 Like