banana-manager 0.6.0

Ready-to-go web app for end-users to interact with tables in a database.

🍌 Banana Manager

Welcome to Banana Manager! Banana Manager is a Python package designed to connect to a database and create a simple web app that displays and allows updates to selected tables. This tool is perfect for non-technical end-users who need to interact with database tables without using complex DBA tools.

Powered by

• Dash and AG Grid: User-friendly, intuitive, and interactive web interface with powerful table displays and editing capabilities.
• Pydantic and YAML: Fast and accurate data handling and configuration.
• SQLAlchemy: Secure, efficient, and flexible database operations for multiple database backends.

Installation

To install Banana Manager, simply use pip:

pip install banana-manager

Also, remember to install the appropriate database connector for your project, like pyodbc or psycopg2. If you’re unsure, SQLAlchemy will inform you when you load your application.

Additionally, consider installing a production server like waitress:

pip install waitress

Getting started

At the end of this tutorial, you’ll have a folder structure similar to the following:

my_manager
    └─ app.py
    └─ config.yaml
    └─ my_tables
            └─ my_group_of_tables.yaml
            └─ another_group_of_tables.yaml

Configuring the Manager

Create a config.yaml file in the root folder of your project with the following structure:

connection:
  drivername: <optional str>
  username: <optional str>
  password: <optional str>
  host: <optional str>
  port: <optional str>
  database: <optional str>
dataPath: "data"
tablePaths: ["tables"]
title: "Banana Database Manager"
theme: <optional str>
defaultColDef: <optional dict>
defaultGridOptions: <optional dict>

• connection (dict) : This will create a SQLAlchemy URL object. See the section Dialets for more information about the appropriate driver.
• dataPath (str, default="data") : The folder where the app data files will be stored.
• tablePaths (list[str], default=["tables"]) : List of folder where the table models YAML files are stored.
• title (str, default="Banana Database Manager") : HTML header title attribute.
• theme (str, default="cyan") : One of the default Mantine colors.
• defaultColDef (dict[str, Any]) : AG Grid column properties.
• defaultGridOptions (dict[str, Any]) : AG Grid options.

Defining the tables

The tables can be defined using YAML files located in the folders specified in the config.yaml. If no folder is specified, create a new folder named "tables" in the root folder.

Each YAML file represents a group containing a list of tables. Here’s the structure:

groupName: <optional string>
displayOrder: <optional integer>
tables:
  - name: <string>
    schemaName: <optional string>
    displayName: <optional string>
    columns:
      - name: <string>
        displayName: <optional string>
        dataType: (optional)
          data: <optional dict>
          type: <string>
        columnDef: <optional dict>
      - <other columns>
    orderBy: (optional)
      - column: <string>
        desc: <optional bool>
      - <other columns>
    limit: <optional int>
    defaultColDef: <optional dict>
    gridOptions: <optional dict>

  - <other tables>

Group configuration

• groupName (str, optional) : Name of the group that will be displayed in the side menu.
• displayOrder (int, optional) : Order which the groups will be stacked in the side menu.
• tables (list) : List of table configurations.

Table configuration

• name (str) : Name of the table in the database.
• schemaName (str, optional) : Schema where the table is located in the database.
• displayName : (str, optional) : Name that will be displayed at the side menu.
• columns (list) : List of column configurations.
• orderBy (list[dict]) : Default ordering of the table rows.
• limit (int) : Maximum of rows returned after order by.
• defaultColDef (dict[str, Any]) : AG Grid column properties.
• gridOptions (dict[str, Any]) : AG Grid options.

Order by configuration

• column (str) : Name of the column
• desc (bool, optional) : If True, order table by column in descending order.

Column configuration

• name (str) : Name of the column in the database.
• displayName (str, optional) : Name that will be displayed in the table.
• dataType (dict, optional) : Set the column to one of the special data types.
• columnDef (dict[str, Any]) : AG Grid column properties.

Data type configuration

The dataType argument customizes how column data is interpreted and displayed. It consists of two keys:

• type (str): Specifies the data type. Available options are:

  • default: Standard data type, no special handling.
  • color: Interprets the data as a color, rendering a visual color representation.
  • enumerator: Maps raw database values to user-friendly labels.
  • foreign: Creates a relationship with another table for enhanced data display.

• data (dict, optional): Contains additional information required for specific type values. Each type requires a different data structure:

  • default: No data required (ignored if provided).
  • color: No data required (ignored if provided).
  • enumerator: A dictionary mapping raw database values (keys) to display labels (values). For example:

    data: {"active": "Active", "inactive": "Inactive"}
    

    Here, a database value of "active" is displayed as "Active" in the frontend.
  • foreign: Specifies the relationship details for joining with another table. Structure:

    data:
      tableName: <string>        # Name of the related table.
      schemaName: <string>       # Schema of the related table.
      columnName: <string>       # Column in the related table to join with.
      columnDisplay: <string>    # Column in the related table to display in the frontend.
    

    Example:

    data:
      tableName: "users"
      schemaName: "public"
      columnName: "id"
      columnDisplay: "username"
    

    This configuration joins the current table with the users table on the id column, displaying the username column instead of the raw id.

About column definitions

You can set the columnDef property in the Column and PrimaryKey models and set the defaultColDef property in the Config and Table models.

The column definitions set in the Config model are applied to every table and column in the application, but they will be overwritten by the definitions set in the Table, and lastly overwritten by the definitions set in Column and PrimaryKey.

BananaManager does not offer support to every single definition of the original AG Grid, specially the Enterprise-only properties, but are some highlights that were fully tested:

• editable (bool) : Set to true if this column is editable, otherwise false.
• hide (bool) : Set to true for this column to be hidden.
• sortable (bool) : Set to false to disable sorting which is enabled by default.
• filter (bool) : Set to true to use the default filter component.

Load the application

Create an app.py file in the root folder:

from banana import Banana

app = Banana()
MY_PORT = 4000 

if __name__ == "__main__":
    app.run_server(port=MY_PORT)

This will load a development server in the selected port. Consider running a production server with waitress:

from banana import Banana
from waitress import serve

app = Banana()
MY_PORT = 4000

if __name__ == "__main__":
    serve(app.server, port=MY_PORT)

Roadmap

Version	Description	Release date
v0.1	Load table and update cells	First half of July 2024
v0.2	Table groups	Second half of July 2024
v0.3	Logging and configurations	First half of August 2024
v0.4	Insert rows and color themes	Second half of August 2024
v0.5	Change history	September 2024
v0.6	Color and enumerator data types	December 2024
v0.7	Undo changes	First quarter of 2025
v0.8	Delete rows	Second quarter of 2025
v0.9	User authentication	2025
v0.10	Advanced user authorization	2025

License

Banana Manager is released under the MIT License. See the LICENSE file for more details.