Connecting database to Flask/dash app using flask_sqlalchemy best practices

Dash Python
1

I have a fairly large multi page app, and a postgres database, both hosted on azure. I am currently connecting to my database using sqlalchemy in a data_connections.py file somewhat like this:

engine = sqlalchemy.create_engine(new_db_url, connect_args={"sslmode": "require"})
Session = sqlalchemy.orm.sessionmaker(bind=engine)

and everywhere else in my app I connect to my database I import either engine or Session and connect.

However, I have read a lot about connecting my postgres database directly to the Flask server using flask_sqlalchemy.

I have been able to test connecting the database and server in a separate repo, using code that looks like this:

app, server = create_app()

# connect database to app
postgresURL = os.environ.get("GEORGIA_POSTGRES_URL")
app.server.config["SQLALCHEMY_DATABASE_URI"] = postgresURL
# necessary to suppress warning when using flask_sqlalchemy
app.server.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app.server)

if __name__ == "__main__":
    # Initialise app and server
    app.run(debug=True)
    server = app.server

This appears to be connecting to the database correctly, but I am struggling to find more literature about connection best practices after this.
Should I import db.engine/db.session from this module in every module I will be connecting to my db in? If I import db everywhere I need, do I still gain much by doing all of this? Is it worth moving away from the connection method that I currently have, and if so why and what are the best practices for doing so?

Thank you!

2

Sorry to hijack your post, but were you able to successfully run the web application? I am doing something similar, pulling data from a SQL database. I have not been able to use dash_table.DataTable to make a simple plot of the data. I was able to pull the data, but not plot it using Dash…

3

Hello @chozillla,

I use sql all the time, the easiest thing is to convert it into a pandas dataframe and then pass that to the charts.

4

Hi, now I’ll show you how I use the Flask + Flask-SQLAlchemy + Dash bundle:

First you need to create such a file structure:

app/
—venv/
—app/
------init.py
------dashboard.py
------models.py
------server.py
run.py

Let’s start with the file server.py . Here we create a Flask application, connect to SQL (And upload data) and create a dash application.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import pandas as pd

class Config(object):
    SQLALCHEMY_DATABASE_URI = 'sqlite:///app.db'
    SQLALCHEMY_TRACK_MODIFICATIONS = False

db = SQLAlchemy()


def create_app():
    server = Flask(__name__)
    server.config.from_object(Config)
    db.init_app(server)
    
    with server.app_context():
        df = pd.read_csv('https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv')
        df.to_sql('gapminder2007', con=db.engine, if_exists='replace')

    from .dashboard import create_dashapp
    dash_app = create_dashapp(server)
    return server # or dash app if you use debug mode in dash

Now consider the file models.py . It stores sqlalchemy models, read more about them in the sqlalchemy documentation

from .server import db


class gapminder(db.Model):
    __tablename__ = "gapminder2007"

    id = db.Column(db.Integer, primary_key=True)
    country = db.Column(db.String)
    pop = db.Column(db.Integer)
    continent = db.Column(db.String)
    lifeExp = db.Column(db.Float)
    gdpPercap = db.Column(db.Float)

Finally, let’s move on to creating a dashboard, here you can see how to get data from sql:

import sqlalchemy as sa
import plotly.graph_objects as go

from .server import db
from .models import gapminder
from dash import Dash, html, dcc, Input, Output


def create_dashapp(server):
    # Initialize the app
    app = Dash(__name__, server=server)    

    # App layout
    app.layout = html.Div([
        html.Div(children='My First App with Dash & SqlAlchemy'),
        html.Hr(),
        dcc.RadioItems(options=['pop', 'lifeExp', 'gdpPercap'], value='lifeExp', id='controls'),
        dcc.Graph(id='first-graph')
    ])

    @app.callback(
        Output(component_id='first-graph', component_property='figure'),
        Input(component_id='controls', component_property='value')
    )
    def update_graph(col_chosen):     
        query = db.session.query(
            gapminder.continent, 
            sa.func.avg(getattr(gapminder, col_chosen)).label('measure'))\
                .group_by(gapminder.continent).all()
        
        x = [q.continent for q in query]
        y = [q.measure for q in query]
        fig = go.Figure([go.Bar(x=x, y=y)])
        return fig

    return app

In the init file, we simply import the application creation function from server.py

from .server import create_app

And in the run.py launching the application

from app import create_app

# Run the app
if __name__ == '__main__':
    app = create_app()
    app.run(debug=True)

English is not my native language, if I made a mistake in this message or on the documentation site, do not be afraid to correct me

3 Likes
5

Great answer @stpnvKirill !

I ended up figuring it out as well, the key for me was using flask’s server.app_context().
My issue was I was getting circular import issues from creating my db variable in my app.py file, where all my page apps were getting imported into, then being unable to export the db variable back out.

My solution was importing my page apps with server.app_context(), then I was able to create a db variable that I can just import across my page apps, without needing to use the app_context() from there on out.

Your solution accomplishes the same things in a slightly different way, and is very thorough and easy to follow!

6

Thanks for the explanation @stpnvKirill ! I was able to get this code working on my end.

I have some follow up questions: Where would the pages/ directory live in this file structure? What would be the proper way to import db from within the pages/ directory?

Thanks again, appreciate it!

7

I am evaluating your code.
I got these errors when I tried to run:

File "/home/user/dash_test/run.py", line 1, in <module>
    from app import create_app
ImportError: cannot import name 'create_app' from 'app' (unknown location)
8

By renaming the file “init.py” to “__init__.py”, I was able to fix the above errors.

So my file structure is:

app/
—venv/
—app/
------__init__ .py
------dashboard.py
------models.py
------server.py
run.py

The “__” that CMS interprets as bold is, in my opinion, the source of the misunderstanding.

Thanks.