How can I add new rows and columns to the table with Django?-CodePudding

The client wants something very different from me. He wants to have the ability to create a table from the admin panel and to give commands to this feature from the front.

These are the commands: Clicking the ROW button will create a new line. When the CELL button is clicked, a new column will be created.

I can do this with JavaScript. But I don't know how to save them in database with django. So how should the column be stored in the database when it is created? I have no idea. If you have any code sample or idea about it, please share with me. Thank you.

CodePudding user response：

I don't know if it actually works or not. The general idea would be

make a django form with field (may be enum so that we can select accordingly), nullable or not as boolean, max_length if any, default value if any if forms.

And in views generate raw sql and execute it.

class DynamicDatabaseForm(forms.Form):
    varchar_field_name = forms.CharField()
    varchar_max_length = forms.IntegerField()
    nullable = forms.BooleanField()
    char_field_default = forms.CharField()
    # ... other properties

In views

class DynamicDatabaseFieldAddView(View):
    def post(self, request):
        table_name = 'user_user' # the name of the database table in which you want to add column
        if request.POST.get('field') == 'varchar':
            column_type = f"varchar({request.POST['varchar_max_length']})"
            if request.POST['nullable']:
                sql = f"ALTER TABLE {table_name} ADD {varchar_field_name} {column_type} NULL;"
            else:
                sql = f"ALTER TABLE {table_name} ADD {varchar_field_name} {column_type} NOT NULL DEFAULT {request.POST['char_field_default']};"
            with connection.cursor() as cursor:
                cursor.execute(sql)
            # other logic

CodePudding user response：

You could use a JSONfield to store all the rows for all the tables. Something like

class DynamicTableSchema( models.Model):
    table_name = models.CharField( max_length= ...)
    schema = models.JSONField( ...)

class DynamicTable( models.Model):
    table_name = models.CharField( max_length= ...)
    # table = models.ForeignKey( 'DynamicTableSchema', ..., related_name='rows', ) # alternative
    data = models.JSONField( ...)

schema would contain a list of valid column names. You might also store associated information for validation here, like what is acceptable data, and what is the human-readable column label.

data would contain the data for the rows in the named table.

Conceptual examples:

table = TableSchema.objects.get( name='MyTable')
print(table.schema)
{ 'quantity':{ 
     'type':'integer',
     'default': 0,
     'label': 'quantity' 
     },
  'description':{
      'type':'string',
      'default': None,
      'label': 'Item Description',
      'max_len': 80 
     }
}
foo = DynamicTable.objects.filter( table_name='foo').first()
# or using alternative ForeignKey,
# foo = table.rows.first()
print( foo.data)
{
    'quantity': 42,
    'description': 'Wooly Socks',
}

If your underlying DB is Postgres, then its abilities to search such data by queryset is considerable.

You will be responsible for dealing with validation of the user's data against the schema (dynamically created forms?) and dealing with insertion of default values when a new column is created (or implicitly generating a default value when such a column is first accessed). There's a pretty close relationship to be found between form.valid_data and data in the model.

The Python package Cerberus might also be helpful.