Writing Your First App (Part 2)

The topics covered in this document expand upon the example app developed in Writing Your First App (Part 1). You will gain the most from this tutorial if you follow along by running your own compute node and entering in the code at each step. We begin with the following code:

from sqlalchemy.types import Text, Float
from sqlalchemy.schema import Column
from tropofy.database.tropofy_orm import DataSetMixin
from tropofy.app import AppWithDataSets, Step, StepGroup
from tropofy.widgets import SimpleGrid, KMLMap
from simplekml import Kml


class Store(DataSetMixin):
    name = Column(Text)
    latitude = Column(Float)
    longitude = Column(Float)


class MyKMLMap(KMLMap):
    def get_kml(self, app_session):
        kml = Kml()
        for store in app_session.data_set.query(Store).all():
            kml.newpoint(name=store.name, coords=[(store.longitude, store.latitude)])
        return kml.kml()


class MyFirstApp(AppWithDataSets):
    def get_name(self):
        return "My First App"

    def get_gui(self):
        return [
            StepGroup(name='Stores', steps=[Step(name='Stores', widgets=[SimpleGrid(Store)])]),
            StepGroup(name='Map', steps=[Step(name='Map', widgets=[MyKMLMap()])])
        ]

Database Constraints

Those of you familiar with databases will know that there is a range of constraints that can be put on any table definition. SQLAlchemy provides us with a full range of possible constraints.

Column Types

Our example app currently has a single database table, with three columns, each of which has a specfic type. For each new Store object, all member values must be of the correct type. For example, Store.latitude must be a Float. This is a floating point number (decimal or integer). When a new Store is added in our tropofy.widgets.SimpleGrid, if the column values are not of the correct types, an error will be returned and the Store will not be added to the database. Common SQLAlchemy types include BOOLEAN, FLOAT, INTEGER and TEXT. For a full list, see the SQLAlchemy docs.

To see this in action, run our example app and add some new Stores. Try and add one with a text value for latitude, you will see an error because the value is the wrong type. Next, add a row where name, latitude, and longitude and all left empty. You will see an empty row appear in the table. This is possible as by default, SQLAlchemy columns allow null values.

Nullable Columns

By default, columns in SQLAlchemy allow null values. To stop this, pass the parameter nullable=False to Column. As an example, consider Store.name. Does it make sense for this to be null? Not really. Each Store should have a name:

class Store(DataSetMixin):
    name = Column(Text, nullable=False)

Seeing as the purpose of our app is to display each Store on a map, it doesn’t make sense for Store.latitude or Store.longitude to be nullable either. Let’s change these also.

class Store(DataSetMixin):
    name = Column(Text, nullable=False)
    latitude = Column(Float, nullable=False)
    longitude = Column(Float, nullable=False)

Note

Changing your database definition during development:

With the inclusion of Nullable Columns, we have changed the definition of our database for the first time. It is now possible that data existing in the database is no longer consistent with the database definition. If you added a Store which did not have a name, latitude or longitude, this will be true for you.

So what does this mean for our app?

During development of a Tropofy App, it is common that you will frequently change your database definition. As you are in development and have no customers, the way to deal with this is to simply delete the database when you know you have made a change, or start seeing errors.

  • First make sure your Tropofy app is no longer running by hitting ctr+c in the terminal.
  • Locate the file tropofy_db.db (this is your database), in the same directory from which you execute tropofy app -c config.py.
  • Delete tropofy_db.db
  • Relaunch your Tropofy app with tropofy app -c config.py

Primary Keys

A core component of relational databases is a tables primary key, which uniquely identifies each record in the table. Tropofy takes care of this for you behind the scenes, so that you do not need to define any primary keys in your Tropofy App. In fact, it is not valid to set any primary keys in your code.

Warning

Do not define primary_keys in your SQLAlchemy code. They are not required in Tropofy, and uniqueness constraints should be used instead.

Uniqueness Constraint

Uniqueness constraints uniquely identify each record in a database table. When defining Columns in SQLAlchemy, a unique parameter exists to which True or False can be passed. For our example, what happens if two Stores are created with the same name? We can add a unique constraint to Store.name to ensure this can not happen. If a user tries to add a store with a name that already exists, they will see an error.

class Store(DataSetMixin):
    name = Column(Text, nullable=False, unique=True)
    latitude = Column(Float, nullable=False)
    longitude = Column(Float, nullable=False)

Note

Unique constraints and data sets:

The single column unique constraint added to Store has no knowledge of data sets. This will be a problem for most developers! It means that the unique constraint is saying that every Store name in the database must be unique. This is irrespective of user or data set and is very rarely the intended usage. To see this for yourself, try and add a Store with the same name to two separate data sets. See Multi-Column Database Constraints to see how to include the data set in a uniqueness constraint (and also Foreign Key Constraint).

Foreign Key Constraint

To show how to model a foreign key in Tropofy, let’s add in another SQLAlchemy class.

class Performance(DataSetMixin):
    store_name = Column(Text, ForeignKey('store.name', ondelete='CASCADE', onupdate='CASCADE'), nullable=False)
    year = Column(Integer, nullable=False)
    sales = Column(Float, nullable=False)
    expenses = Column(Float, nullable=False)

We need to add ForeignKey to our sqlalchemy.schema imports whenever a ForeignKey is used. Furthermore for our example, we are using Integer for the first time and thus need to add it to our Column Types imports.

from sqlalchemy.types import Text, Float, Integer
from sqlalchemy.schema import Column, ForeignKey

To expose this new class in the GUI, we add a new tropofy.widgets.SimpleGrid which reference this source class.

    def get_gui(self):
        return [
            StepGroup(name='Stores', steps=[Step(name='Stores', widgets=[SimpleGrid(Store)])]),
            StepGroup(name='Performances', steps=[Step(name='Performances', widgets=[SimpleGrid(Performance)])]),
            StepGroup(name='Map', steps=[Step(name='Map', widgets=[MyKMLMap()])])
        ]

This new class tracks the performance of each of our stores over years. It also adds our first foreign key relationship. A foreign key is a constraint on a column, which indicates that values on the column should be constrained to values present in a named remote column.

In our new class, Performance.store_name is a foreign key to the remote, target column Store.name (referenced by the lower case 'store.name' - see note below). This means that only names that exist in Store.name can be used in Performance.store_name. The first parameter to ForeignKey is the target column to which the column being defined is linked. Note that as with a single column Uniqueness Constraint, a single column foreign key has no knowledge of data set. For our example, this means that the values of Performance.store_name are limited to all values of Store.name in the database (not just the current data set as is usually expected). See Multi-Column Database Constraints on how to address this.

The other parameters ondelete and onupdate specify the behaviour of the Performace object when the linked Store is updated or deleted respectively. Specifying CASCADE means that any changes to a Store object will be ‘cascaded’ to any Performance objects where Performance.store_name == Store.name. For example, if the name of a Store is changed, the store_name of all Performance objects where (before the update) Store.name == Performance.store_name will have their store_name updated.

Note

Why ‘store.name’ and not ‘Store.name’?

ForeignKey constraints reference remote columns through the SQLAlchemy Core and not the ORM. The SQLAlchemy core is unaware of our SQLAlchemy Python classes and this means references to remote columns use the name of the table in the database and not the SQLAlchemy Python class. All table names are the lower case of their SQLAlchemy Python class name and thus 'store.name' and not Store.name is used. You don’t really need to understand what is going on here, just know that in ForeignKey constraints (and some other we will use soon), references to other tables are done as lower case strings.

Default values

To give a SQLAlchemy column a default value, simply specify the default parameter on the Column constructor. To add defaults Performance.sales and Performace.expenses:

class Performance(DataSetMixin):
    store_name = Column(Text, ForeignKey('store.name', ondelete='CASCADE', onupdate='CASCADE'), nullable=False)
    year = Column(Integer, nullable=False)
    sales = Column(Float, nullable=False, default=0)
    expenses = Column(Float, nullable=False, default=0)

Multi-Column Database Constraints

So far, all of the database constraints we have implemented have related to a single column. As such, they have been defined on the Column constructor. Things are not always this simple however, and it is often appropriate to use constraints that span several columns. The primary example of this, for which will be used by almost every Tropofy app, is combing a Uniqueness Constraint or Foreign Key Constraint with data set. When we added a Uniqueness Constraint to Store.name, this placed a constraint that every Store name in the database must be unique, irrespective of data set or user. This does not make sense for most apps, as usually users data should be completely independent of other users and each of their data sets. To fix this, let’s add UniqueConstraint and ForeignKeyConstraint to our sqlalchemy.schema imports:

from sqlalchemy.schema import Column, ForeignKeyConstraint, UniqueConstraint

Next, we remove the unique=True from the Column definition of Store.name, and add a new multi-column UniqueConstraint across Store.name and Store.data_set_id. data_set_id is a column added by Tropofy to every SQLAlchemy Python class that you make (if it derives from DataSetMixin), which tracks the data set to which an object is added. This column is added automatically for you to help make your class definitions cleaner.

class Store(DataSetMixin):
    name = Column(Text, nullable=False)
    latitude = Column(Float, nullable=False)
    longitude = Column(Float, nullable=False)

    @classmethod
    def get_table_args(cls):
        return (UniqueConstraint('name', 'data_set_id'),)

By creating this UniqueConstraint on Store, our foreign key on column Performance.store_name is now broken. This is because every foreign key must match a primary key or uniqueness constraint in another table. As we have made the uniqueness constraint span multi-columns, we must do the same for any foreign key that references it.

For multi-column foreign keys, ForeignKeyConstraint is implemented similarly to UniqueConstraint. The first argument to ForeignKeyConstraint is a list of columns in the local table, and the second a list of foreign column names in a reference table. See the SQLA ForeignKeyConstraint docs for more details.

We change the foreign key column Performance.store_name to a multi-column ForeignKeyConstraint which includes Performance.data_set_id:

class Performance(DataSetMixin):
    store_name = Column(Text, nullable=False)
    year = Column(Integer, nullable=False)
    sales = Column(Float, nullable=False, default=0)
    expenses = Column(Float, nullable=False, default=0)

    @classmethod
    def get_table_args(cls):
        return (ForeignKeyConstraint(['store_name', 'data_set_id'], ['store.name', 'store.data_set_id'], ondelete='CASCADE', onupdate='CASCADE'),)

As a further example, consider our examples Performance class, of which each object is used to track the performance of a store in a given year. It does not make sense for two Performance objects with the same store_name and year to exist in a dataset. Add a UniqueConstraint on Performance.store_name, Performance.year, and Performance.data_set_id:

class Performance(DataSetMixin):
    store_name = Column(Text, nullable=False)
    year = Column(Integer, nullable=False)
    sales = Column(Float, nullable=False, default=0)
    expenses = Column(Float, nullable=False, default=0)

    @classmethod
    def get_table_args(cls):
        return (
            UniqueConstraint('store_name', 'year', 'data_set_id'),
            ForeignKeyConstraint(['store_name', 'data_set_id'], ['store.name', 'store.data_set_id'], ondelete='CASCADE', onupdate='CASCADE'),
        )

Database constraints spanning multiple columns make use of the classmethod get_table_args. class attribute. get_table_args returns a tuple of constraints (A Python tuple consists of a number of values separated by commas, and can be thought of similarly to a list defined with () instead of []) Note that the last element in the tuple returned by get_table_args must have a trailing comma, as seen in the above example.

Working with Multiple Widgets

Our example app currently has three widgets; two of tropofy.widgets.SimpleGrid and a tropofy.widgets.KMLMap. Each of these are displayed on separate tropofy.app.StepGroup and tropofy.app.Step of the GUI. Let’s now see how to group widgets within steps and stepgroups. While we’re at it, we’ll also take a look at tropofy.widgets.Chart.

We can add a tropofy.widgets.Chart to our App to display an overview of the companies finances across all stores:

class PerformanceBarChart(Chart):
    def get_chart_type(self, app_session):
        return Chart.BARCHART

    def get_table_schema(self, app_session):
        return {
            "year": ("string", "Year"),
            "sales": ("number", "Sales"),
            "expenses": ("number", "Expenses")
        }

    def get_table_data(self, app_session):
        results = []
        years = [y for r in app_session.data_set.query(Performance.year).distinct() for y in r]
        for year in years:
            performances = app_session.data_set.query(Performance).filter_by(year=year).all()
            results.append({
                "year": year,
                "sales": sum(p.sales for p in performances),
                "expenses": sum(p.expenses for p in performances),
            })
        return results

    def get_column_ordering(self, app_session):
        return ["year", "sales", "expenses"]

    def get_order_by_column(self, app_session):
        return "year"

    def get_chart_options(self, app_session):
        return {
            'title': 'Company Performance',
            'vAxis': {
                'title': 'Year',
                'titleTextStyle': {'color': 'red'}
            }
        }

As you can see, tropofy.widgets.Chart has several functions to be defined which enable a wide range of customised, dynamic charts. Tropofy leverages the power of Google Charts. See the documentation of tropofy.widgets.Chart for a full list of available charts and descriptions of each of the functions you need to implement.

We should now update the GUI with our new tropofy.widgets.Chart. To make our code more readable within tropofy.app.AppWithDataSets.get_gui(), we are now constructing each tropofy.app.StepGroup explicitly, to which we use tropofy.app.StepGroup.add_step() to add each tropofy.app.Step. We then return a list of StepGroups as is required by tropofy.app.AppWithDataSets.get_gui(). We have also logically separated our steps into ‘Input’ and ‘Output’ StepGroups.

    def get_gui(self):
        step_group_1 = StepGroup(name='Input')
        step_group_1.add_step(Step(name='Stores', widgets=[SimpleGrid(Store)]))
        step_group_1.add_step(Step(name='Performances', widgets=[SimpleGrid(Performance)]))

        step_group_2 = StepGroup(name='Output')
        step_group_2.add_step(Step(name='Map', widgets=[MyKMLMap()]))
        step_group_2.add_step(Step(name='Chart', widgets=[PerformanceBarChart()]))

        return [step_group_1, step_group_2]

We now have four steps contained within two step groups, with one widget per step. It is possible for us to combine the two output steps into one. This will put one widget on top of the other in the GUI:

    def get_gui(self):
        step_group_1 = StepGroup(name='Input')
        step_group_1.add_step(Step(name='Stores', widgets=[SimpleGrid(Store)]))
        step_group_1.add_step(Step(name='Performances', widgets=[SimpleGrid(Performance)]))

        step_group_2 = StepGroup(name='Output')
        step_group_2.add_step(Step(name='Visualisations', widgets=[MyKMLMap(), PerformanceBarChart()]))

        return [step_group_1, step_group_2]

Dashboards

A tropofy.app.Step can also be given a custom layout on which widgets are placed next to each other when the display is large enough. For smaller displays the widgets will collapse nicely into a single column layout. To demonstrate this, let’s first add another tropofy.widgets.Chart:

class StoreExpensesPieChart(Chart):
    def get_chart_type(self, app_session):
        return Chart.PIECHART

    def get_table_schema(self, app_session):
        return {
            "store": ("string", "Store"),
            "expenses": ("number", "Expenses")
        }

    def get_table_data(self, app_session):
        results = []
        for store in app_session.data_set.query(Store).all():
            performances = app_session.data_set.query(Performance).filter_by(store_name=store.name).all()
            results.append({
                "store": store.name,
                "expenses": sum(p.expenses for p in performances),
            })
        return results

    def get_column_ordering(self, app_session):
        return ["store", "expenses"]

    def get_chart_options(self, app_session):
        total_expense = sum(p.expenses for p in app_session.data_set.query(Performance).all())
        return {
            'title': 'Company Expenses: Total = ${expense}'.format(expense=str(total_expense)),
        }

This Pie Chart displays the total company expenses, broken down by Store. Now we add it to the GUI in our ‘Visualisation’ tropofy.app.Step. Here we will specify a layout for the widgets to create a dashboard. Instead of passing a list of widgets when constructing a tropofy.app.Step, it is possible to pass a list of dictionaries, where each dictionary defines a widget and the number of columns it should take up in the user interface. The total number of columns for each tropofy.app.Step is fixed at 12. When a row reaches capacity, additional Widgets will be forced onto a new row.

    def get_gui(self):
        step_group_1 = StepGroup(name='Input')
        step_group_1.add_step(Step(name='Stores', widgets=[SimpleGrid(Store)]))
        step_group_1.add_step(Step(name='Performances', widgets=[SimpleGrid(Performance)]))

        step_group_2 = StepGroup(name='Output')
        step_group_2.add_step(Step(
            name='Visualisations',
            widgets=[
                {"widget": PerformanceBarChart(), "cols": 6},
                {"widget": StoreExpensesPieChart(), "cols": 6},
                {"widget": MyKMLMap(), "cols": 12},
            ],
        ))

        return [step_group_1, step_group_2]

This user interface layout for the ‘Visualisation’ step results in two rows of widget. The first row contains both charts, taking half the screen width each. The second row has a full width map.

Example Data

It is strongly recommended to provide users of an app with example data. To do so, use tropofy.app.AppWithDataSets.get_examples(). This function returns a dictionary of {example name:function} pairs, where each function populates an empty tropofy.app.data_set.AppDataSet. The following function is added to our MyFirstApp definition:

    def get_examples(self):
        return {"Demo data for Brisbane North": load_example_data}

And the function to load the example data set is added globally. This function is always passed an empty tropofy.app.data_set.AppDataSet by Tropofy, to which the example data can be added:

def load_example_data(app_session):
    stores = []
    stores.append(Store(name="CLAYFIELD", latitude=-27.417536, longitude=153.056677))
    stores.append(Store(name="SANDGATE", latitude=-27.321538, longitude=153.069267))
    app_session.data_set.add_all(stores)

    performances = []
    performances.append(Performance(store_name="CLAYFIELD", year=2011, sales=1000, expenses=400))
    performances.append(Performance(store_name="CLAYFIELD", year=2012, sales=1170, expenses=460))
    performances.append(Performance(store_name="SANDGATE", year=2011, sales=660, expenses=1120))
    performances.append(Performance(store_name="SANDGATE", year=2012, sales=1030, expenses=540))
    app_session.data_set.add_all(performances)

Summary

Congratulations! You’ve reached the end of Part 2 of Writing Your First App. We’ve covered the fundamental concepts of what it takes to build a Tropofy App.

Note

Where to go from here?

If you’re feeling confident, dive into writing an app of your own! If you’re after some more practice, checkout more tutorials at Classification or try and expand on this example further yourself. Here are some suggestions of where to start:

  • Add another example data set.
  • Create another chart.

See below for the full code of the app we have made:

from sqlalchemy.types import Text, Float, Integer
from sqlalchemy.schema import Column, ForeignKeyConstraint, UniqueConstraint
from tropofy.database.tropofy_orm import DataSetMixin
from tropofy.app import AppWithDataSets, Step, StepGroup
from tropofy.widgets import SimpleGrid, KMLMap, Chart
from simplekml import Kml


class Store(DataSetMixin):
    name = Column(Text, nullable=False)
    latitude = Column(Float, nullable=False)
    longitude = Column(Float, nullable=False)

    @classmethod
    def get_table_args(cls):
        return (UniqueConstraint('name', 'data_set_id'),)


class Performance(DataSetMixin):
    store_name = Column(Text, nullable=False)
    year = Column(Integer, nullable=False)
    sales = Column(Float, nullable=False, default=0)
    expenses = Column(Float, nullable=False, default=0)

    @classmethod
    def get_table_args(cls):
        return (
            UniqueConstraint('store_name', 'year', 'data_set_id'),
            ForeignKeyConstraint(['store_name', 'data_set_id'], ['store.name', 'store.data_set_id'], ondelete='CASCADE', onupdate='CASCADE'),
        )


class MyKMLMap(KMLMap):
    def get_kml(self, app_session):
        kml = Kml()
        for store in app_session.data_set.query(Store).all():
            kml.newpoint(name=store.name, coords=[(store.longitude, store.latitude)])
        return kml.kml()


class PerformanceBarChart(Chart):
    def get_chart_type(self, app_session):
        return Chart.BARCHART

    def get_table_schema(self, app_session):
        return {
            "year": ("string", "Year"),
            "sales": ("number", "Sales"),
            "expenses": ("number", "Expenses")
        }

    def get_table_data(self, app_session):
        results = []
        years = [y for r in app_session.data_set.query(Performance.year).distinct() for y in r]
        for year in years:
            performances = app_session.data_set.query(Performance).filter_by(year=year).all()
            results.append({
                "year": year,
                "sales": sum(p.sales for p in performances),
                "expenses": sum(p.expenses for p in performances),
            })
        return results

    def get_column_ordering(self, app_session):
        return ["year", "sales", "expenses"]

    def get_order_by_column(self, app_session):
        return "year"

    def get_chart_options(self, app_session):
        return {
            'title': 'Company Performance',
            'vAxis': {
                'title': 'Year',
                'titleTextStyle': {'color': 'red'}
            }
        }


class StoreExpensesPieChart(Chart):
    def get_chart_type(self, app_session):
        return Chart.PIECHART

    def get_table_schema(self, app_session):
        return {
            "store": ("string", "Store"),
            "expenses": ("number", "Expenses")
        }

    def get_table_data(self, app_session):
        results = []
        for store in app_session.data_set.query(Store).all():
            performances = app_session.data_set.query(Performance).filter_by(store_name=store.name).all()
            results.append({
                "store": store.name,
                "expenses": sum(p.expenses for p in performances),
            })
        return results

    def get_column_ordering(self, app_session):
        return ["store", "expenses"]

    def get_chart_options(self, app_session):
        total_expense = sum(p.expenses for p in app_session.data_set.query(Performance).all())
        return {
            'title': 'Company Expenses: Total = ${expense}'.format(expense=str(total_expense)),
        }


class MyFirstApp(AppWithDataSets):
    def get_name(self):
        return "My First App"

    def get_gui(self):
        step_group_1 = StepGroup(name='Input')
        step_group_1.add_step(Step(name='Stores', widgets=[SimpleGrid(Store)]))
        step_group_1.add_step(Step(name='Performances', widgets=[SimpleGrid(Performance)]))

        step_group_2 = StepGroup(name='Output')
        step_group_2.add_step(Step(
            name='Visualisations',
            widgets=[
                {"widget": PerformanceBarChart(), "cols": 6},
                {"widget": StoreExpensesPieChart(), "cols": 6},
                {"widget": MyKMLMap(), "cols": 12},
            ],
        ))

        return [step_group_1, step_group_2]

    def get_examples(self):
        return {"Demo data for Brisbane North": load_example_data}


def load_example_data(app_session):
    stores = []
    stores.append(Store(name="CLAYFIELD", latitude=-27.417536, longitude=153.056677))
    stores.append(Store(name="SANDGATE", latitude=-27.321538, longitude=153.069267))
    app_session.data_set.add_all(stores)

    performances = []
    performances.append(Performance(store_name="CLAYFIELD", year=2011, sales=1000, expenses=400))
    performances.append(Performance(store_name="CLAYFIELD", year=2012, sales=1170, expenses=460))
    performances.append(Performance(store_name="SANDGATE", year=2011, sales=660, expenses=1120))
    performances.append(Performance(store_name="SANDGATE", year=2012, sales=1030, expenses=540))
    app_session.data_set.add_all(performances)