Black Lives Matter. Please consider donating to Black Girls Code today.
Dash HoloViews is now available! Check out the docs.

Button submit df.to_sql - sqlalchemy

I am trying to achieve a type of loader where a user can use the ‘drag and drop’ feature for a csv file upload based on (https://github.com/plotly/dash-recipes/blob/master/dash-upload-simple.py). However I am also wanting a button to be able to submit the uploaded file (as a dataframe) into a table within a database using sqlalchemy with something along the lines of df.to_sql(“dbTableName”, engine, if_exists = ‘replace’) embedded within a function/callback, however i am struggling to find the correct way to do this and was really hoping for some help with defining the function/callback and linking it to the button… Hoping this is actually do-able and someone can point me in the right direction?

Here is my code so far (a cut down version of the version in the link above).

import dash_html_components as html
import dash_core_components as dcc
import dash
import plotly
import dash_table_experiments as dte
from dash.dependencies import Input, Output, State
import pandas as pd
import numpy as np
import json
import datetime
import operator
import os
import base64
import io


engine = sqlalchemy.create_engine("** my connection string **")

app = dash.Dash()
app.scripts.config.serve_locally = True

app.layout = html.Div([


                html.H5("Upload Files"),
                dcc.Upload(
                        id='upload-data',
                        children=html.Div([
                            'Drag and Drop or ',
                            html.A('Select Files')
                        ]),
                        style={
                            'width': '100%',
                            'height': '60px',
                            'lineHeight': '60px',
                            'borderWidth': '1px',
                            'borderStyle': 'dashed',
                            'borderRadius': '5px',
                            'textAlign': 'center',
                            'margin': '10px'
                        },
                        multiple=True),


                html.Br(),
############################## My query ######################################################################################
                html.Button(id='propagate-button', n_clicks=0, children='Upload to database'), 

                # I need to set this button in some way to 'submit'/ call a function which would incoroporate the following:

#                 df = pd.read_excel(filename)
#                 df.to_sql("dbTableName", engine, if_exists = 'replace')
##############################################################################################################################
                html.Br(),
                html.Br(),
                html.H5("Updated Table"),
                html.Div(id='output-data-upload'),
                html.Div(dte.DataTable(rows=[{}], id='table'), style={'display': 'none'})


                ])

# Functions
# file upload function
def parse_contents(contents, filename, date):
    content_type, content_string = contents.split(',')

    decoded = base64.b64decode(content_string)
    try:
        if 'csv' in filename:
            # Assume that the user uploaded a CSV file
            df = pd.read_csv(
                io.StringIO(decoded.decode('utf-8')))
        elif 'xls' in filename:
            # Assume that the user uploaded an excel file
            df = pd.read_excel(io.BytesIO(decoded))

    except Exception as e:
        print(e)
        return html.Div([
            'There was an error processing this file.'
        ])

    return html.Div([
        html.H5(filename),
        html.H6(datetime.datetime.fromtimestamp(date)),

        # Use the DataTable prototype component:
        # github.com/plotly/dash-table-experiments
        dte.DataTable(rows=df.to_dict('records')),

        html.Hr(),  # horizontal line

    ])

# callback table creation
@app.callback(Output('output-data-upload', 'children'),
              [Input('upload-data', 'contents'),
               Input('upload-data', 'filename'),
               Input('upload-data', 'last_modified')])

def update_output(list_of_contents, list_of_names, list_of_dates):

    if list_of_contents is not None:
        children = [
            parse_contents(c, n, d) for c, n, d in
            zip(list_of_contents, list_of_names, list_of_dates)]
        return children


def update_filter_column_options(n_clicks_update, tablerows):

    if n_clicks_update < 1:
        print("df empty")

        return []

    else:
        dff = pd.DataFrame(tablerows) # <- problem! dff stays empty even though table was uploaded

        print("updating... dff empty?:", dff.empty) #result is True, labels stay empty

        return [{'label': i, 'value': i} for i in sorted(list(dff))]

app.css.append_css({
    "external_url": "https://codepen.io/chriddyp/pen/bWLwgP.css"
})

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

Hi,
What is the goal of having button? You can save df to database right after successful upload:

elif ‘xls’ in filename:
# Assume that the user uploaded an excel file
df = pd.read_excel(io.BytesIO(decoded))
=> df.to_sql(‘table1’, conn, if_exists=‘replace’, index=False )

Thanks, yeah i managed to find a resolution to this!