0

I've run into an issue writing a PyShiny app that I can't seem to find the answer to and I'm hoping someone here might be able to help. The app I'm writing needs to stream data from a SQL database (I'm using pandas.read_sql for this) but the database path will be specified by the user as a UI input (ui.input_file). I'd like to do this using invalidateLater.

The code below is a simple demonstration of the same issue without using my app's specific code. In this bit of code, I'm looking to initiate the process with a single click of an action button, then periodically recalculate the shape of the database every 5 seconds.

import pandas as pd
import sqlite3

from shiny import App, render, ui, reactive

app_ui = ui.page_fluid(
    ui.input_file("file1", "Choose a file to upload:", multiple=False),
    ui.input_action_button(id="go", label="Go", class_="btn-warning"),
    ui.output_text("output_txt"),
)


def server(input, output, session):
    def get_data():
        if input.go() > 0:
            reactive.invalidate_later(5)
            cnx = sqlite3.connect(input.file1()[0]["datapath"])
            df = pd.read_sql("SELECT * FROM table1", cnx)
            cnx.close()
            return df

    @output(id="output_txt")
    @render.text
    def output():
        d = get_data()
        print(d.shape)
        return d.shape


app = App(app_ui, server)

Instead, the app seems to be getting the same original shape of the database (the shape first calculated upon clicking the action button), even if I add or remove rows from the database while its running... Why is this happening? I'm fairly experienced with Python but am relatively new to Shiny so I haven't fully grasped the idea of reactively - I'd really appreciate any tips. Thank you!

Tristan Kleyn
  • 131
  • 5

1 Answers1

0
  1. It seems when inputting the sqlite file into the shiny app, the file itself is static so whatever changes you made outside of that file seems to not affect the internal data when you upload the file (I am not exactly sure if this is right but please correct me if I am wrong)
  2. Whenever you click on input.go(), yes it does increment by 1 every time but a more effective way would be to add reactive.event(input.go) as a decorator which tells the function that every time the input.go is clicked on, run this function. This is preferred over if input.go() > 0 because then the render.text decorator function you have below won't return an error message before clicking on the go button

Here are the small changes I adjusted:

def server(input, output, session):
    @reactive.event(input.go)
    def get_data():
        reactive.invalidate_later(5)
        cnx = sqlite3.connect(input.file1()[0]["datapath"])
        df = pd.read_sql("SELECT * FROM table1", cnx)
        cnx.close()
        return df
jesliu
  • 11
  • 1