Dash graph not updating with sqlite query from datepicker range

Dash Python
1

Hi,

I am working with dash code to make graphs based on query. My sqlite database table has 3 columns date/type; datetime, pv/type:int, sp/type:int.

My code is running without any error, but it is not returning anything in graph. Graph is blank.

Here is my code:

“”"

 import dash as dash 
 import sqlite3 
 from dash.dependencies import Output, Input 
 import dash_core_components as dcc 
 import dash_html_components as html 
 import plotly 
 import plotly.graph_objs as go 
 from collections import deque 
 import pandas as pd 
 import pyodbc 
 import flask as flask 
 import datetime as datetime 
 from datetime import datetime as dt

 def connectSQLServer():
 connSQLServer = sqlite3.connect('/home/pi/Documents/datalogger/datalogger.db')
 return connSQLServer 


 name_title = 'Stats from SQL Server'
 server = flask.Flask(__name__)
 app = dash.Dash(__name__)
 server = app.server
 app.config.suppress_callback_exceptions=True
 app.layout =html.Div([ 
 html.H1(children='Temperature Data '),
 html.H3(children='HISTORICAL DATA'),
 html.P(children="Enter number of samples to retrieve"),
 dcc.DatePickerRange(
    id='my-date-picker-range',  # ID to be used for callback
    calendar_orientation='horizontal',  # vertical or horizontal
    day_size=39,  # size of calendar image. Default is 39
    end_date_placeholder_text="Return",  # text that appears when no end date chosen
    with_portal=False,  # if True calendar will open in a full screen overlay portal
    first_day_of_week=0,  # Display of calendar when open (0 = Sunday)
    reopen_calendar_on_clear=True,
    is_RTL=False,  # True or False for direction of calendar
    clearable=True,  # whether or not the user can clear the dropdown
    number_of_months_shown=1,  # number of months shown when calendar is open
    min_date_allowed=dt(2020, 9, 12),  # minimum date allowed on the DatePickerRange component
    max_date_allowed=dt(2020, 9, 14),  # maximum date allowed on the DatePickerRange component
    #initial_visible_month=dt(2020, 5, 1),  # the month initially presented when the user opens the calendar
    start_date=dt(2020, 9, 12).date(),
    end_date=dt(2020, 9, 14).date(),
    #display_format='MMM Do, YY',  # how selected dates are displayed in the DatePickerRange component.
    #month_format='MMMM, YYYY',  # how calendar headers are displayed when the calendar is ope ned.
   # minimum_nights=1,  # minimum number of days between start and end date

     persistence=True,
     persisted_props=['start_date'],
     persistence_type='memory',  # session, local, or memory. Default is 'local'

    updatemode='singledate'  # singledate or bothdates. Determines when callback is triggered
),  
dcc.Graph( id='example-graph'),],)

 
@app.callback(Output('example-graph', 'figure'), 
          [Input('my-date-picker-range', 'start_date'),
           Input('my-date-picker-range', 'end_date')])


def update_graph_scatter(start_date, end_date):

dataSQL = [] #set an empty list
X = deque(maxlen=10)    
Y = deque(maxlen=10)
Y2= deque(maxlen=10)
sql_conn = connectSQLServer() 
cursor = sql_conn.cursor()
cursor.execute("SELECT date, pv, sp FROM temp WHERE date BETWEEN 'str(start_date)' AND 'str(end_date)'")
rows = cursor.fetchall()
for row in rows:
    dataSQL.append(list(row))
    labels = ['date','pv','sp']
    df = pd.DataFrame.from_records(dataSQL, columns=labels)
    X = df['date']
    Y = df['pv']
    Y2= df['sp']

data = plotly.graph_objs.Scatter(
        x=list(X),
        y=list(Y),
        name='pv',
        mode='lines'
        )

data1 = plotly.graph_objs.Scatter(
        x=list(X),
        y=list(Y2),
        name='sp',
        mode='lines')

return {'data': [data,data1],'layout' : go.Layout(xaxis=go.layout.XAxis(range=[start_date, end_date]), yaxis=go.layout.YAxis(range=[start_date,end_date]))}


if __name__ == "__main__":
server.run(host="0.0.0.0",debug=True)

“”"

Can you please help me with this. Sorry for mistakes as I am a newbie here.

Thanks & Regards
Amandeep Singh

2

Hi,

Any help please.