Skip to main content

Functions

Functions lets you fetch data or perform actions.

There are two major types of functions: Data Fetchers and Scripts.

All Functions can access user input via State. They can also access and modify Widget and UI Component properties via Context.

Functions

Function Types​

Let's take a look at the two types of functions:

Data Fetchers​

In a nutshell, Data Fetchers are functions used to "fetch" data for your tables. When you bind a data fetcher function to a table, Dropbase automatically formats your data in a table format that is automatically displayed in your tables.

Currently Dropbase allows you to fetch data with both SQL and Python.

Using SQL to Fetch Data​

  1. First you will need to choose a SQL Data Fetcher when creating a function
  2. You will then need to choose a data source from which to fetch data
  3. Write any SQL code to fetch data from your SQL database

SQL data fetcher

Accessing State and Context​

You can use {{ }} to dynamically update SQL with State and Context values. One way this is useful is to query data in a table based on data selected in another table.

tip

Use best practices for writing your SQL statements: uniquely alias/identify each of the columns in your SELECT statement, especially when selecting columns from multiple tables in JOIN statements. If columns have the same name, use aliases to uniquely identify them. This is especially needed for optimal conversion from Tables to Smart Tables if you need them later.

Using Python to Fetch Data​
  1. First you will need to choose a Python Data Fetcher when creating a function
  2. Write any Python code that fetches data, including data from external APIs.
  3. Python Data Fetchers must return DataFrame to render in Table components.

Python data fetcher

Format of Python Data Fetcher​
  • Your function name must be the same as the file name i.e. if your fetcher's file name is get_data, then the Python function that returns the DataFrame should have the signature: def getdata() → df.DataFrame and must return a DataFrame.
  • Any Data Fetcher that accesses user input must take in state
Accessing State and Context​

You can access state or context, including selected row, user input, UI component properties from the State or Context objects that will imported in your file.

Using Python Data Fetchers​

Let's take a look at some examples of Python Data Fetchers:

Example 1: Python Data Fetcher get_data.py that returns a DataFrame


import pandas as pd

def get_data() -> pd.DataFrame:
    df = pd.DataFrame({"col1": [1, 2], "col2": [3, 4]})
    return df[:5]

Example 2: Using Stripe's API to fetch customer data

import requests
import pandas as pd

def getCustomers() -> pd.DataFrame:
    api_key = "YOUR_STRIPE_API_KEY"
    url = "https://api.stripe.com/v1/customers"
    headers = {
        "Authorization": f"Bearer {api_key}"
    }
    response = requests.get(url, headers=headers)

    customer_data = response.json()
    customers = customer_data.get('data', [])
    df = pd.DataFrame(customers)

    return df

Scripts​

Now that we've covered Data Fetchers, let's take a look at Scripts. Where as Data Fetchers are used to fetch data and primarily used to bind data to tables, Scripts are used to perform other types of actions.

Scripts are written in Python and can be used to do anything Python can do. This includes fetching data, but also includes other actions like sending emails, updating databases, and more. We can also use these scripts to modify UI component properties via Context.

For the sake of seperating concerns, we've seperated Scripts into two types: UI Functions that modify UI component properties and Generic Functions that perform any other desired operation.

note

UI Functions are the same as Generic Functions, but they must take in Context and return Context. In every other way, they are the same as Generic Functions.

Modifying UI with UI Functions​

Let's take a look at an example of a UI Function that modifies a widget's message:

Widget message

Function Code:


from workspace.App2.page1 import State, Context

def widget_message(state: State, context: Context) -> Context:
    context.widgets.widget1.message = "Hello World!"
    return context

In this example, we are modifying the message of a widget called widget1 to say "Hello World!".

We pass Context into the function so that we can modify the message property of the widget. Once the message property is modified, the client will detect the change and update the UI accordingly.

Doing Anything Else with Generic Functions​

Generic Python function can perform any other logic with Python. The biggest difference between UI Functions and Generic Functions is that Generic Functions do not have a return type requirement.

Let's take a look at some examples of Generic Functions:

Example 1: A function that updates the database with user input

from server.database import Database
from workspace.App3.page1 import State

def update_db(state: State):
    with Database("demo") as db:
        # Update the field "workspace_name" to "New Value" based on the workspace_id value of a selected row, when the is_active field is true
        selectedWorkspaceID = state.tables.table1.workspace_id
        db.update(
            table="workspaces",
            keys={"workspace_id": selectedWorkspaceID, "is_active":True},
            values={"workspace_name": "New Value"})
        db.commit()

        # Insert a new record with workspace_id 11
        db.insert(
            "workspaces",
            {
                "workspace_id": 11,
                "workspace_name": "Workspace M",
                "is_active": "true",
                "created_at": "2023-09-29 21:10:00.630643"
            },
        )
        db.commit()

In this example, we are using a Generic Function to update a database with user input. We are using the state object to access the selected row in a table and then using the Database class to update and insert records.

An important thing to note here is that we do not have a return type requirement for Generic Functions. This means that we do not have to return a DataFrame like we do with Data Fetchers or return Context like we do with UI Functions.

Example 2: The same function as above, but updating the UI with Context

from server.database import Database
from workspace.App3.page1 import State, Context

def update_db(state: State, context: Context) -> Context:
    with Database("demo") as db:
        # Update the field "workspace_name" to "New Value" based on the workspace_id value of a selected row, when the is_active field is true
        selectedWorkspaceID = state.tables.table1.workspace_id
        db.update(
            table="workspaces",
            keys={"workspace_id": selectedWorkspaceID, "is_active":True},
            values={"workspace_name": "New Value"})
        db.commit()

        # Insert a new record with workspace_id 11
        db.insert(
            "workspaces",
            {
                "workspace_id": 11,
                "workspace_name": "Workspace M",
                "is_active": "true",
                "created_at": "2023-09-29 21:10:00.630643"
            },
        )
        db.commit()

        #Notify users via widget system message
        context.widgets.widget1.message = "All database edits completed"

    return context

In this example, we expand on the previous example by using and returning the context object. This allows us to modify the message property of a widget called widget1. Modifying the message property triggers a message visible to end-users via the widget component.

note

Since Generic Functions can use and return Context, they can perform the same job as UI functions. However, UI Functions are specifically designed to modify UI component properties and are the recommended way to do so.