7. Adding interactive Data Viz

7. Adding interactive Data Viz#

Now that we have a nice reporting data set, lets get this into a chart. There are obviously many ways to serve data into BI or data apps, but in the the spirit of using python, we will keep it pythonic with ploty. Of course, we can then create a webpage and spin that up into a webserver with dash.

Passing data from MotherDuck into plotly#

We can use DuckDB’s python APIs to create a dataframe. This means we can simply use SQL to pass data into the front end.

import os

# Get the secret and load it into python
load_dotenv()
MOTHERDUCK_TOKEN = os.getenv('MOTHERDUCK_TOKEN')

# Connect to DuckDB using the MotherDuck token
conn = duckdb.connect(f'md:?MOTHERDUCK_TOKEN={MOTHERDUCK_TOKEN}')

# query your database from python and return a data frame
market_cap_df = conn.execute(f'''
    SELECT Date, market_cap
    FROM stocks_dev.main.market_cap_by_day
    WHERE symbol = 'AAPL'
''').df()

Since the SQL interface is somewhat trivial, the rest of the work in python is to define the plots and then shape it into a little bit of HTML.

An example script can be found here.

I’ve built out a end-to-end repo that extracts, loads, de-duplicates, and models this dataset over here.

Extra Credit

Ship the result chart using GitHub pages. Then set up a GitHub action to run every 5 minutes and update with the latest stock price and publish the changes to your MotherDuck database.

Contents