Here is a solution. I used the tables library. How can I easily wrap the parameters in quotes? I had to hard code them to test?
import dash
from dash.dependencies import Output, Input, Event
import dash_core_components as dcc
import dash_html_components as html
import dash_table_experiments as dt
import plotly
import plotly.graph_objs as go
from collections import deque
import pandas as pd
import sqlite3
def run_query_withparms(sql):
conn = sqlite3.connect(“dbSTR.db”)
df = pd.read_sql_query( sql , conn)
return df
app = dash.Dash()
app.layout = html.Div([
html.H4(‘DataTable’),
html.Label(‘Report type:’, style={‘font-weight’: ‘bold’}),
dcc.Input(
id=‘field-dropdown’,
type=‘text’,
value=‘PPM9’
),
dt.DataTable(
# Initialise the rows
rows=[{}],
row_selectable=True,
filterable=True,
sortable=True,
selected_row_indices=[],
id=‘table’
),
html.Div(id=‘selected-indexes’)
], className=‘container’)
@app.callback(Output(‘table’, ‘rows’), [Input(‘field-dropdown’, ‘value’)])
def update_table(user_selection):
“”"
For user selections, return the relevant table
“”"
sql2 = ("select fe.id,fe.seqid,str.start,str.end,avg(str.period),avg(str.length)"
" from"
" strloc str,"
" features fe,"
" attribs at,"
" (select seqid,min(start) start, max(end) end"
" from features fe,"
" attribs at"
" where"
" at.id = fe.id"
" and at.value = 'MMP9' "
" and at.attrib = 'gene_name'"
" group by seqid) minmaxstart"
" where"
" str.chrom = minmaxstart.seqid"
" and str.start >= minmaxstart.start"
" and str.end <= minmaxstart.end"
" and at.id = fe.id"
" and at.value = 'MMP9'"
" and at.attrib = 'gene_name'"
" and fe.seqid = minmaxstart.seqid"
" group by fe.id,fe.seqid,str.start,str.end;").format(user_selection,user_selection)
df = run_query_withparms(sql2)
return df.to_dict('records')
if name == ‘main’:
app.run_server(debug=True)
I am new to dash and maybe I have started with a complex problem.
I am attempting to create a web app that will accept user input and then query a local sqlite database. The result will be a table on the page.
The code starts the server, I receive errors when I type in the resulting text box.
The sql for now is just fixed to generate something.
Please let me know if there is an example that demonstrates callbacks and databases?
There are also native tables in dash?
import dash
from dash.dependencies import Output, Input, Event
import dash_core_components as dcc
import dash_html_components as html
import dash_table_experiments as dt
import plotly
import plotly.graph_objs as go
from collections import deque
import pandas as pd
import sqlite3
def run_query_withparms(sql):
conn = sqlite3.connect(“dbSTR.db”)
df = pd.read_sql_query( sql , conn)
return df
def generate_table(dataframe, max_rows=10):
return html.Table(
# Header
[html.Tr([html.Th(col) for col in dataframe.columns])] +
# Body
[html.Tr([
html.Td(dataframe.iloc[i][col]) for col in dataframe.columns
]) for i in range(min(len(dataframe), max_rows))]
)
app = dash.Dash()
app.layout = html.Div([
html.Label(‘Text Input’),
dcc.Input(id=“text_in”,value=‘MTL’, type=‘text’),
html.Table(id=‘tab_out’)
])
app.callback(Output(‘tab_out’,‘children’), [Input(‘text_in’,‘value’)])
def run_query(value):
sql2 = “SELECT * FROM strloc limit 10;”
return generate_table(run_query_withparms(sql2))
if name == ‘main’:
app.run_server(debug=True)
Errors:
- Serving Flask app “app22” (lazy loading)
- Environment: production
WARNING: Do not use the development server in a production environment.
Use a production WSGI server instead. - Debug mode: on
- Running on http://127.0.0.1:8050/ (Press CTRL+C to quit)
- Restarting with stat
- Debugger is active!
- Debugger PIN: 417-414-378