Auto CRUD on Parameterized Models For auto-ORM and auto-front-end with Panel and Param

Panel
1

Has anyone achieved a CRUD database interface in correspondence with parameterized objects with param and panel? I mean using param as like an ORM and panel as a front end? I’m most familiar with django models, but perhaps this can be done with sqlalchemy or duckdb or some kind of database interface. Then… making data apps becomes instantly accessible and scalable with panel from a data perspective. It’s like an auto-api through just defining param classes. And an auto-front end by serving with panel. Seems really powerful! I’m just thinking out loud, but I’m sure some more advanced community members here have achieved this.

I did come across @alitrack in another post about GitHub - alitrack/fab-dash: Flask Appbuilder Dashboard but I am not very familiar with flask and I’m wondering what some alternative methods might looks like. I would give points to the simplest or most holovizzy solutions.

THANK YOU :pray:

1 Like
2

I took a crack at it.

Auto Crud:

import param as pm
from sqlalchemy import (
    Boolean,
    Column,
    Date,
    DateTime,
    Float,
    Integer,
    PickleType,
    Sequence,
    String,
)


def param_to_sqlalchemy_type(param_type):
    if isinstance(param_type, pm.String):
        return String
    elif isinstance(param_type, pm.Number):
        return Float
    elif isinstance(param_type, pm.Integer):
        return Integer
    elif isinstance(param_type, pm.Boolean):
        return Boolean
    elif isinstance(param_type, pm.Date):
        return Date
    elif isinstance(param_type, pm.DateTime):
        return DateTime
    elif isinstance(param_type, (pm.List, pm.Dict, pm.Tuple)):
        return PickleType
    elif isinstance(param_type, pm.Selector):
        # Assuming string type for simplicity; adjust based on actual use case
        return String
    else:
        raise TypeError(f'Unsupported param type: {type(param_type)}')


def parameterized_to_model(cls, Base):
    # Define a primary key column
    attrs = {'id': Column(Integer, primary_key=True)}
    # Add other columns based on param attributes
    attrs.update(
        {
            name: Column(param_to_sqlalchemy_type(param))
            for name, param in cls.param.objects().items()
            if isinstance(param, pm.Parameter)
        }
    )
    attrs['__tablename__'] = cls.__name__.lower()
    return type(cls.__name__ + 'Model', (Base,), attrs)

Example:

import param as pm
from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, sessionmaker

from auto_crud_param import parameterized_to_model

Base = declarative_base()


# Example usage
class A(pm.Parameterized):
    name = pm.String(default='Item A')
    value = pm.Number(default=0)


AModel = parameterized_to_model(A, Base)

# Set up the database (for example, using SQLite)
engine = create_engine('sqlite:///mydatabase.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

The code is here :slight_smile:

2 Likes
3

HoloNote does some database things, but I believe your code is pretty interesting for generic cases! https://github.com/holoviz/holonote/blob/main/holonote/annotate/connector.py

1 Like
4

I added an example CRUD widget.

Havn’t connected backend / frontend yet.

import panel as pn
import param as pm
from panel.widgets import Button, MultiSelect, TextInput


class CRUDListParameter(pm.Parameterized):
    items = pm.List(default=[])

    def create(self, item):
        self.items.append(item)

    def read(self, index):
        return self.items[index]

    def update(self, index, new_item):
        self.items[index] = new_item

    def delete(self, index):
        del self.items[index]


class CRUDMultiSelect(MultiSelect):
    def __init__(self, crud_param, **params):
        self.crud_param = crud_param
        super().__init__(**params)
        self.options = self.crud_param.items

        # Create buttons for CRUD operations
        self.create_button = Button(name='Create', button_type='primary')
        self.update_button = Button(name='Update', button_type='success')
        self.delete_button = Button(name='Delete', button_type='danger')
        self.confirm_delete_button = Button(
            name='Confirm Delete', button_type='danger', visible=False
        )

        # Add event handlers
        self.create_button.on_click(self.create_item)
        self.update_button.on_click(self.update_item)
        self.delete_button.on_click(self.delete_item)
        self.confirm_delete_button.on_click(self.confirm_delete)

        # Input for create/update
        self.input_dialog = TextInput(name='Item', placeholder='Enter item here')

    def panel(self):
        # Update the options of the MultiSelect widget whenever the crud_param changes
        self.crud_param.param.watch(
            lambda event: setattr(self, 'options', event.new), 'items'
        )

        return pn.Column(
            self.input_dialog,
            pn.Row(
                self.create_button,
                self.update_button,
                self.delete_button,
                self.confirm_delete_button,
            ),
            self,  # The MultiSelect widget itself
        )

    def create_item(self, event):
        new_item = self.input_dialog.value
        if new_item:
            self.crud_param.create(new_item)
            self.input_dialog.value = ''  # Reset input field
            self.options = self.crud_param.items  # Update options
            self.param.trigger('options')  # Explicitly trigger an update

    def update_item(self, event):
        updated_item = self.input_dialog.value
        if not updated_item:
            return

        selected_items = self.value
        if not selected_items:
            return

        for selected_item in selected_items:
            selected_index = self.options.index(selected_item)
            self.crud_param.update(selected_index, updated_item)

        self.input_dialog.value = ''  # Reset input field
        self.options = self.crud_param.items  # Update options
        self.param.trigger('options')  # Explicitly trigger an update

        def update_existing_item(self, event):
            updated_item = self.input_dialog.value
            if updated_item:
                selected_items = self.value
                if not selected_items:
                    return
                selected_index = self.options.index(selected_items[0])
                self.crud_param.update(selected_index, updated_item)
                self.input_dialog.value = ''  # Reset input field

    def delete_item(self, event):
        selected_items = self.value
        if not selected_items:
            return

        self.confirm_delete_button.visible = True

    def confirm_delete(self, event):
        selected_items = self.value
        if not selected_items:
            return

        # Delete all selected items
        for item in selected_items:
            if item in self.options:
                selected_index = self.options.index(item)
                self.crud_param.delete(selected_index)

        # Update the options and hide the confirm delete button
        self.options = self.crud_param.items
        self.confirm_delete_button.visible = False
        self.param.trigger('options')  # Explicitly trigger an update

class MyCRUDApp(pm.Parameterized):
    # Example usage
    crud_param = CRUDListParameter()

    def __init__(self):
        super().__init__()
        self.crud_param.items = ['Item 1', 'Item 2', 'Item 3']  # Set initial items here
        self.crud_widget = CRUDMultiSelect(self.crud_param)

    def panel(self):
        return pn.Row(self.crud_widget.panel())


# Create and show the app
app = MyCRUDApp()
app.panel().servable()

image

4 Likes
5

This is so cool. Thanks for sharing. I want to able to convert easily between param, traitlets/ ipywidget, pydantic and now also SQL Alchemy models :slight_smile:

1 Like
6

hi @LinuxIsCool very cool thanks for your sharing , kindly please advise if we want to store CRUD to database which key/parameter that we need to put them to DB, or if you have any example for CRUD to mongoDB or any other DB would be glad

thanks

1 Like