Displaying Database Tables and Query Results

In the example below we use both pandas and ag-Grid to display database tables from an sqlite database.

Warning

For the example to work you need to download the chinook sqlite database and put it in the directory from which you are running the program. The file is a copy of the one found on https://www.sqlitetutorial.net/ which is an excellent resource for learning sqlite.

The program uses pandas ability to load a frame with the result of a SQL query. This is done using read_sql_query.

Once the data is in a pandas frame, we use the JustPy pandas extension to create an AgGrid.

The program also has an additional request handler under the route '/city' which runs the following SQL command per request:

SELECT DISTINCT city, country, customerid from customers ORDER BY country

Again, the result is loaded into a pandas frame and using the pandas extension, an AgGrid is created.

After the tables are displayed, the user can then filter and sort the data further using ag-Grid's capabilities.

import justpy as jp
import sqlite3
import pandas as pd
import os
from jpcore.download import Download

# Download the database file to the $HOME/.justpy directory
# from: https://elimintz.github.io/chinook.db, originally from https://www.sqlitetutorial.net/

dbname = "chinook.db"
url = f"https://elimintz.github.io/{dbname}"
os.makedirs(Download.get_cache_path(), exist_ok=True)
filePath = f"{Download.get_cache_path()}/{dbname}"
Download.download_file(url, dbname, target_directory=Download.get_cache_path())

db_con = sqlite3.connect(filePath)
table_names = [
    "albums", "artists", "customers", "sqlite_sequence", "employees", "genres", "invoices", "invoice_items",
    "media_types", "playlists", "playlist_track", "tracks", "sqlite_stat1",
]

tables = {}
for table_name in table_names:
    tables[table_name] = pd.read_sql_query(f"SELECT * from {table_name}", db_con)


def selected_event(self, msg):
    # Runs when a table name is selected
    # Create a new grid and use its column and row definitions for grid already on page
    new_grid = tables[msg.value].jp.ag_grid(temp=True)
    msg.page.g.options.columnDefs = new_grid.options.columnDefs
    msg.page.g.options.rowData = new_grid.options.rowData


def db_test(request):
    wp = jp.QuasarPage()
    table_name = request.query_params.get("table", "albums")
    s = jp.QSelect(options=table_names, a=wp, label="Select Table", outlined=True, input=selected_event,
        style="width: 350px; margin: 0.25rem; padding: 0.25rem;", value=table_name,)
    g = tables[table_name].jp.ag_grid(a=wp, style="height: 90vh; width: 99%; margin: 0.25rem; padding: 0.25rem;")
    g.options.pagination = True
    g.options.paginationAutoPageSize = True
    wp.g = g
    return wp


@jp.SetRoute("/city")
def city_test():
    wp = jp.WebPage()
    g = pd.read_sql_query("SELECT DISTINCT city, country, customerid from customers ORDER BY country",db_con,).jp.ag_grid(a=wp)
    g.style = "height: 99vh; width: 450px; margin: 0.25rem; padding: 0.25rem;"
    return wp

jp.justpy(db_test)