Generate DataTable from MySQL query

Dash Python
1

Hello, I’m fairly new to python and very new to Dash but I’ve go a couple of bar and line charts working where they query a database at set intervals and display the data. I’m trying to do the same with a data table but I’m unable to, I was wondering if anyone could help me out? Thanks in advance.

EDIT: here’s my code for trying to get just the table working, there are extra imports because I just kept all the ones from the main project.

from __future__ import division
import dash
from dash.dependencies import Output, Event, Input
import dash_core_components as dcc
import dash_html_components as html
import dash_table_experiments as dt
from datetime import datetime as dtt
import csv
import pymysql
import pandas as pd
import plotly.plotly as py
import plotly.graph_objs as go
import random
from collections import deque


conn = pymysql.connect(
    host='',
    user='',
    password='',
    db='')


app = dash.Dash()
app.layout = html.Div([
    html.Div([
        html.H2('Table'),
        html.Div(id='Table'),
    ]),
],
)


def generate_table(a, max_rows=100):
     return html.Table(className="table",
                      children=[
                          html.Thead(
                              html.Tr(
                                  children=[
                                      html.Th(col.title()) for col in df.columns.values]
                                  )
                              ),
                          html.Tbody(
                              [
                              html.Tr(
                                  children=[
                                      html.Td(data) for data in d]
                                  )
                               for d in df.values.tolist()])
                          ]
    )



@app.callback(Output('Table', 'children'))

def update_recent_data():
  a = conn.cursor()
  a.execute("Query that returns x amount of columns and a variable amount of rows")
  a = a[['column1','column2',,,'columnx']]

  return generate_table(a, max_rows=100)

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

Hi,
i am looking for same answer. i am able to create table with MySQL data, but not able to make it refreshing. i am using dash_table_experiments module. you can check it here


maybe it will help. but i hope somebody will provide better solution :slight_smile:

3

Is it usage-callback there that’s similar to connecting to MySQL yeah?

4

here is my example :

import dash
import dash_core_components as dcc
import dash_html_components as html
import plotly.graph_objs as go
import mysql.connector
from dash.dependencies import Event, Output
import plotly
import requests
import dash_table_experiments as dt
import pandas as pd  

def generate_table():
    cnx = mysql.connector.connect(data for connection)
    query = "SELECT.... "
    cursor = cnx.cursor()
    cursor.execute(query)
    for data in cursor:
        data_name.append(data[0])
        data_port.append(data[1])
    DF_SIMPLE = pd.DataFrame({
        "name" : data_name,
        "port" : data_port
    })
    table = dt.DataTable(
        rows = DF_SIMPLE.to_dict('records'),
        columns = DF_SIMPLE.columns,
        id = 'sample-table'
    )
    return table
app = dash.Dash()
app.layout = html.Div([generate_table(),
    dcc.Interval(
        id='interval-component',
        interval=5 * 1000,  # in milliseconds
    ),])


@app.callback(Output('sample-table', 'rows'), events=[Event('interval-component', 'interval')])
def generate_table():
    cnx = mysql.connector.connect(data for connection)
    query = "SELECT.... "
    cursor = cnx.cursor()
    cursor.execute(query)
    for data in cursor:
        data_name.append(data[0])
        data_port.append(data[1])
    DF_SIMPLE = pd.DataFrame({
        "name" : data_name,
        "port" : data_port
    })
    rows = DF_SIMPLE.to_dict('records')
    return rows

this is simplified but working for me. i am quite new in python so please don’t be mad on me :slight_smile:
EDIT : for sure i have added imports :slight_smile:

1 Like
5

Cheers man I’ll try similar on mine and if I can get the interval bit working I’ll let you know. Dont worry I’m the exact same :slight_smile: we all gotta start somewhere

6

I got mine updating if you’re interested? Only thing is my columns are numbered not titled but I don’t think it’s related

7

Hi, do u have a updated version of this code using input and not events?
I cant seem to get mine to work!