Dash interacting with sqlite and creating a table

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
1 Like

Hi There,

Not sure exactly what you’re asking, but let me try to offer some tips.

If you have your query already, the multi line string you have is the way to go.

I’m not sure what you mean by parameters, but lets assume you’re talking about a common use case: you want users to make selections that change the query going to sqlite. The best way to do that is string formatting. If you simply wanted a user to select a table, you’d use something like the below:

"SELECT * FROM {}".format(tableName)

Which would insert the tableName into the query.

Hope this helps

Yes it helps Thanks.

I was attempting to quote the parameters in the where for the value field.

I was just putting the quotes in the textbox when I type in the UI.

But realized that I could just do the following (using your example).

"SELECT * FROM ‘{}’ ".format(tableName)

This automatically will quote the text

Thanks,

Richard