To save Datatable to mysql database in Dash Multipage app. But raise db is not defined error

Dash Python
1

nameerror: name ‘db’ is not defined mysql

When code apply this line (df.to_sql("Your_Sales_Data", con=db.engine, if_exists='replace', index=False)) It raise error. Please Can you help me to solve this. This code is on 3rd page and I defined engine on app.py

from flask_sqlalchemy import SQLAlchemy
from flask import Flask
import pymysql


server = Flask(__name__)
app = dash.Dash(__name__, server=server,plugins=[dl.plugins.pages], external_stylesheets=[dbc.themes.BOOTSTRAP, FONT_AWESOME],
                suppress_callback_exceptions=True)
app.server.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.server.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:xxxx@localhost/searchdb"
db = SQLAlchemy(app.server)

def df_to_csv(n_clicks, n_intervals, dataset, s):
    output = html.Plaintext("The data has been saved to your database.",
                            style={'color': 'black', 'font-weight': 'bold', 'font-size': 'Large','margin': "5px"})
    no_output = html.Plaintext("", style={'margin': "0px"})

    input_triggered = dash.callback_context.triggered[0]["prop_id"].split(".")[0]

    if input_triggered == "save-table" and n_clicks > 0:
        s = 2
        df = pd.DataFrame(dataset)
        df.to_sql("Your_Sales_Data", con=db.engine, if_exists='replace', index=False)
        return output, s
    elif input_triggered == 'interval' and s > 0:
        s = s-1
        if s > 0:
            return output, s
        else:
            return no_output, s
    elif s == 0:
        return no_output, s
2

@Sanan009,

If it is all on the same page does it work? Is this the code from your third page?

3

@jinnyzor thank you for your answer. only df_to_csv function from 3rd page. others from app.py script. db doesn’t recognized on 3rd page.

4

Are you importing the db reference from the app?

Something like this:

from app import db
5

@jinnyzor

from app import db

I did this option but this gave me other error and other web pages turned to 404.

6

After I applied from app import db

Results:

KeyError: <weakref at 0x000001C6F5250C20; to ‘Flask’ at 0x000001C6EF7C3B20>

And I want to mention than I didn’t instantiate class to create table in mysql database. Is this error for that reason.

7

@Sanan009,

Have you loaded anything into that db?

Something like this?

from yourapplication import db
db.create_all()

https://flask-sqlalchemy.palletsprojects.com/en/2.x/quickstart/

8

@jinnyzor

I did it but result:

raise RuntimeError(unbound_message) from None

RuntimeError: Working outside of application context.

This typically means that you attempted to use functionality that needed
the current application. To solve this, set up an application context
with app.app_context(). See the documentation for more information.

9

This is my top of app.py script

FONT_AWESOME = “https://use.fontawesome.com/releases/v5.7.2/css/all.css”

server = Flask(name)

app = dash.Dash(name, server=server,plugins=[dl.plugins.pages], external_stylesheets=[dbc.themes.BOOTSTRAP, FONT_AWESOME],
suppress_callback_exceptions=True)

app.server.config[‘SQLALCHEMY_TRACK_MODIFICATIONS’] = False

app.server.config[“SQLALCHEMY_DATABASE_URI”] = “mysql+pymysql://root:test12345@localhost/searchdb”

db = SQLAlchemy(app.server)

10

@Sanan009,

Ok, you were using an older version of the instructions.

Here is an example of how to get it working.

yourapp.py

from dash import Dash, html, dcc, Output, Input
import dash_bootstrap_components as dbc
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import pandas as pd
from datetime import datetime

from flask_sqlalchemy import SQLAlchemy
from flask import Flask
import pymysql


server = Flask(__name__)
app = dash.Dash(__name__, server=server,plugins=[dl.plugins.pages], external_stylesheets=[dbc.themes.BOOTSTRAP, FONT_AWESOME],
                suppress_callback_exceptions=True)
app.server.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.server.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:xxxx@localhost/searchdb"
db = SQLAlchemy()
db.init_app(app.server)

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

Run this after you’ve set up your app:

from yourapp import db

from yourapp import app

with app.server.app_context():
    db.create_all()

I tested with this:

from dash import Dash, html, dcc, Output, Input
import dash_bootstrap_components as dbc
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import pandas as pd
from datetime import datetime

server = Flask(__name__)
FONT_AWESOME = "https://use.fontawesome.com/releases/v5.7.2/css/all.css"

app = Dash(__name__, server=server, external_stylesheets=[dbc.themes.BOOTSTRAP, FONT_AWESOME],)


db = SQLAlchemy()
app.server.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:///project.db"
db.init_app(app.server)

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


app.layout = html.Div([html.Button('loadusers', id='loadusers', n_clicks=0),html.Div(id='page-content')], id='div-app')

@app.callback(
    Output('page-content', 'children'),
    Input('loadusers','n_clicks')
              )
def loadusers(n1):
    if n1 > 0:
        df = pd.read_sql_query("SELECT name FROM sqlite_schema WHERE type ='table';",
                               con=db.engine)
        return df.to_json()

if __name__ == '__main__':
    app.run(debug=True)
2 Likes
11

@jinnyzor
First of All thank you this wide explanation.

I want to know should I run these code line in terminal or 3rd page

from yourapp import db

from yourapp import app

with app.server.app_context():
db.create_all()

12

You can run it in a script in the same file location, or in a python terminal from the same location.

13

OK, I write this code in terminal. But If I wrote just db.create_all() It gave me this error:

RuntimeError: Working outside of application context.

This typically means that you attempted to use functionality that needed
the current application. To solve this, set up an application context
with app.app_context(). See the documentation for more information.

14

@jinnyzor
In my script I didn’t instantiate the class is it important?

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

[/quote]

15

I don’t know what your db structure will be, but I believe that that is utilized in order to setup individual user access to the db.

18

@Sanan009,

When giving code examples, you can use the pre-formatted text in order to maintain its structure.

19

Ok thanks

Please let me fix this

20

This is my app.py

FONT_AWESOME = "https://use.fontawesome.com/releases/v5.7.2/css/all.css"
server = Flask(__name__)


app = dash.Dash(__name__, server=server, plugins=[dl.plugins.pages], external_stylesheets=[dbc.themes.BOOTSTRAP, FONT_AWESOME],
                suppress_callback_exceptions=True)


app.server.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False


app.server.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:test12345@localhost/searchdb"



db = SQLAlchemy(app.server)


 db.init_app(app.server)





    

class Product(db.Model):

    __tablename__ = 'productlist'

    Phone = db.Column(db.String(40), nullable=False, primary_key=True)
    Version = db.Column(db.String(40), nullable=False)
    Price = db.Column(db.Integer, nullable=False)
    Sales = db.Column(db.Integer, nullable=False)

    def __init__(self, phone, version, price, sales):
        self.Phone = phone
        self.Version = version
        self.Price = price
        self.Sales = sales



    offcanvas =

    html.Div(

    [

        dbc.Button("Explore", id="open-offcanvas", n_clicks=0),

        dbc.Offcanvas(

      dbc.Nav(
            [
                dbc.NavLink(
                    [
                        html.Div(page["name"], className="ms-2"),
                    ],
                    href=page["path"],
                    active="exact", style={"color": "black", "padding": '10px 10px'}
                )
                for page in dash.page_registry.values() if page["module"] != "pages.not_found_404"
            ],
            vertical=True,
            pills=True,
            style={"padding": "10px 60px 30px 0px"}
        ),



            id="offcanvas",
            title= "Şəxsin Analitik Araşdırma Platforması",
            is_open=False,
            placement="top"
                    ),
    ],
    className="my-3")









app.layout = dbc.Container(
    [
       dbc.Row(
          [
            dbc.Col(
                [
                    dcc.Store(id='side_click'),
                    dcc.Store(id='store-data-d'),
                    dcc.Store(id='store-data-frame',storage_type="session"),
                    dcc.Store(id="store-interval", data=0),
                    offcanvas
                ], xs=0, sm=0, md=0, lg=0, xl=0, xxl=0)]),
       dbc.Row([
            dbc.Col(
                [
                    dl.plugins.page_container
                ], xs=12, sm=12, md=12, lg=12, xl=12, xxl=12)
          ]
       )
    ], fluid=True

)



@app.callback(
    Output("offcanvas", "is_open"),
    Input("open-offcanvas", "n_clicks"),
    [State("offcanvas", "is_open")],
)
def toggle_offcanvas(n1, is_open):
    if n1:
        return not is_open
    return is_open



if __name__ == "__main__":
    app.run_server(debug=True)
21

And this is my 3rd page:

pg3.py

from dash.dependencies import Input, Output, State
import warnings
import dash
import dash_bootstrap_components as dbc
import pandas as pd
from dash import dash_table
from dash import dcc, callback
from dash import html
from dash.dependencies import Input, Output, State
from dash_labs.plugins import register_page
from app import app
from app import db
warnings.simplefilter("ignore")

# To create meta tag for each page, define the title, image, and description.
register_page(__name__,
              path='/pg3',  # '/' is home page and it represents the url
              name='Network Analysis',  # name of page, commonly used as name of link
              title='Network',  # title that appears on browser's tab
              # image='pg1.png',  # image in the assets folder
              # description='Histograms are the new bar charts.'
              )

layout = dbc.Container([


    dbc.Row([dbc.Col([

    html.Div(children=[

        dbc.ButtonGroup([
            dbc.Button("Table", id="table", n_clicks=0), dbc.Button("Save", id="save-table", n_clicks=0)
            ])
                      ])


                         ], width= 1),
    dbc.Col([ html.Div(id='placeholder', children=[]),
        dcc.Interval(id='interval', interval=1000)], width = 0 )]),

    dbc.Row([dbc.Col([ dash_table.DataTable(id="datatable_id", data=[{'Product': 'Iphone', 'Version': '6a', 'Price': 799, 'Sales': 2813},
              {'Product': 'Iphone', 'Version': '9', 'Price': 900, 'Sales': 5401},
              {'Product': 'Iphone', 'Version': '7', 'Price': 799, 'Sales': 2513},
              {'Product': 'Iphone', 'Version': '8', 'Price': 850, 'Sales': 5401},
              {'Product': 'Galaxy', 'Version': 'S9', 'Price': 900, 'Sales': 6084},
              {'Product': 'Galaxy', 'Version': 'S10', 'Price': 1000, 'Sales': 7084},
              {'Product': 'Galaxy', 'Version': 'S20', 'Price': 1200, 'Sales': 9084},
              {'Product': 'Pixel', 'Version': '1', 'Price': 400, 'Sales': 2084},
              {'Product': 'Pixel', 'Version': '2', 'Price': 500, 'Sales': 3033},
              {'Product': 'Pixel', 'Version': '3', 'Price': 600, 'Sales': 6000}], columns=[{'name': 'Product', 'id': 'Product', 'deletable': False, 'renamable': False},
                 {'name': 'Version', 'id': 'Version', 'deletable': True, 'renamable': True},
                 {'name': 'Price', 'id': 'Price', 'deletable': True, 'renamable': True},
                 {'name': 'Sales', 'id': 'Sales', 'deletable': False, 'renamable': False}], filter_action="native",
                                     sort_action="native",
                                     sort_mode="multi",
                                     row_selectable="multi",
                                     editable=True,
                                     row_deletable=False,
                                     selected_rows=[],
                                     page_action="native",
                                     page_size=10,
                                     # fixed_columns={ 'headers': True, 'data': 0 },
                                     style_cell={'textAlign': 'center', "font-size": 12},
                                     style_header={'height': 40,'backgroundColor': '#2338a1','color': 'white', "font-size": 14},
                                     style_table={'overflowX': 'auto'}
                                     ) ])])
], fluid = True)

# @callback([
#     Output('datatable_id', 'data'),
#     Output('datatable_id', 'columns')],
#
#     [
#         Input('store-data-frame', 'data'),
#         Input('table', 'n_clicks')],
#     prevent_initial_call=True)
# def table(data, n_clicks):
#     print(n_clicks)
#     if n_clicks == 1:
#         for k, v in data.items():
#             if "df" in k:
#                 dff = pd.DataFrame.from_dict(v)
#                 data = dff.to_dict("records")
#                 columns = [{'name': i, 'id': i, "hideable": True} for i in dff.columns]
#                 return data, columns
#     elif n_clicks > 1:
#         dff = pd.read_sql_table('Your_Sales_Data', con=db.engine)
#         data = dff.to_dict("records")
#         columns = [{'name': i, 'id': i, "hideable": True} for i in dff.columns]
#         return data, columns




@callback(
    [Output('placeholder', 'children'),
     Output("store-interval", "data")],
    [Input('save-table', 'n_clicks'),
     Input("interval", "n_intervals")],
    [State('datatable_id', 'data'),
     State('store-interval', 'data')],
    prevent_initial_call=True
)
def df_to_csv(n_clicks, n_intervals, dataset, s):
    output = html.Plaintext("The data has been saved to your database.",
                            style={'color': 'black', 'font-weight': 'bold', 'font-size': 'Large','margin': "5px"})
    no_output = html.Plaintext("", style={'margin': "0px"})

    input_triggered = dash.callback_context.triggered[0]["prop_id"].split(".")[0]

    if input_triggered == "save-table" and n_clicks > 0:
        s = 2
        df = pd.DataFrame(dataset)
        df.to_sql("Your_Sales_Data", con=db.engine, if_exists='replace', index=False)
        return output, s
    elif input_triggered == 'interval' and s > 0:
        s = s-1
        if s > 0:
            return output, s
        else:
            return no_output, s
    elif s == 0:
        return no_output, s
22

I simplified my code to fix this easily.

If I run just this code in terminal :

from app import app
from app import db

db.create_all()

I got this result :

RuntimeError: Working outside of application context.

This typically means that you attempted to use functionality that needed
the current application. To solve this, set up an application context
with app.app_context(). See the documentation for more information.