Approach to bulk-update MS SQL table from a Django UI with filterable table

[bswylie]

Hi everyone,

I’m relatively new to Django (working through the quickstart), and really like the approach Django takes to rapid development. I’m very familiar with python and pyodbc and REST API’s, but relatively new to building for web, and I know that asking for some guidance upfront will probably steer me in the right direction much more quickly than me fumbling around. Some web searches I’ve been doing for guidance haven’t been fruitful so I’m reaching out to this community in the hope that someone can guide the start of this journey for me.

I have a case where I have a relatively complex SQL database (Microsoft SQL Server) already designed and partially populated, but essentially need to create a web interface to relatively quickly enable a small group of users to be able to do CRUD-like operations on a few of the tables in the database (excluding the “Create” or “Delete” operations, they will only read and update records already there in the SQL DB). The number of records edited each time will be small - at most, around 6000 records at once.

I would like to be able to have a Django web UI view (page) which shows a table of records for editing for a specific user type. Ideally this table should be filterable by one or more columns at once before they enter any value changes to update to the database - and they should be able to bulk update multiple records at once. Very similar in concept to filtering and editing in Excel, however with the data stored in SQL and needing to be accessed remotely, potentially by multiple people simultaneously (but with no overlap in what they would edit).

The complexities I see so far are:

1. I’m not likely to be using the Django database and migrations capability, and the flexibility of creating the data model from within Django will not be used unfortunately, given that there is a complex data model already built and populated.

2. The SQL table in the back end is JOIN-ed to some dimension tables to provide more meaningful dimension columns for the users to be able to filter by, before editing. Thus, I have a SQL view which provides the records with dimensions and current values, and the user would not be able to edit the dimension columns, only the value columns, and the resulting update operation back to the database would be writing back to the table based on a key that is available to the interface, but would thus not be a simple link between UI and one SQL table in the background.

3. I know that MS SQL Server is not natively supported however the package django-mssql-backend seems to be actively maintained.

Am I going along the right approach? Is it possible to create such an bulk-editable table with Django, linked to a view AND a table in MS SQL Server? If someone could help me fast track how I should best architect this, I would be extremely grateful!

Many thanks,
Brian

[adamchainz]

@bswylie

I’m not likely to be using the Django database and migrations capability, and the flexibility of creating the data model from within Django will not be used unfortunately, given that there is a complex data model already built and populated.

You can use inspectdb to generate models based upon your database tables. Not sure how it plays with MS SQL setups. Even if it doesn’t work, you can still use hand made models with the meta option managed = False set…

@bswylie

The SQL table in the back end is JOIN-ed to some dimension tables to provide more meaningful dimension columns for the users to be able to filter by, before editing. Thus, I have a SQL view which provides the records with dimensions and current values, and the user would not be able to edit the dimension columns, only the value columns, and the resulting update operation back to the database would be writing back to the table based on a key that is available to the interface, but would thus not be a simple link between UI and one SQL table in the background.

You can map multiple tables’ data (models) into one form and write back again to the write models in a custom save. For your setup it sounds like it would be best to ignore ModelForm and implement your own save() method to write back the correct fields to the table (model). For bulk editing of multiple rows (objects) you probably want to use formsets.

[bswylie]

Thanks so very much for the quick reply, Adam. Although I have a long way to go in understanding Django, I think I understand what you’ve said.

If I may be so bold to ask a couple of short follow-on questions:

1. From a UI perspective served through Djano view - having a “table” with multiple rows for editing - are there any tips you could provide as to how I would approach this?
2. Are there any links you have for examples of how I would go about the save() example without ModelForm, since this is a pattern very different to the official Django tutorial? (i.e. is there somewhere in the Django docs or somewhere else) for what you are referring to?

Again, thank you so much for your quick response, it has already helped!

[adamchainz]

@bswylie

From a UI perspective served through Djano view - having a “table” with multiple rows for editing - are there any tips you could provide as to how I would approach this?

If you render a formset, by default it will present something like that. This is what the Django admin does with its inline classes.

@bswylie

Are there any links you have for examples of how I would go about the save() example without ModelForm, since this is a pattern very different to the official Django tutorial? (i.e. is there somewhere in the Django docs or somewhere else) for what you are referring to?

class MyForm(forms.Form):
    book_title = forms.CharField(...)
    author_name = forms.CharField(...)

    def save(self):
        author = Author.objects.create(author_name=self.cleaned_data['author_name'])
        return Book.objects.create(author=author, title=self.cleaned_data['book_title'])

You can also inherit from a ModelForm that works for one model and extend it to save extra fields to other related models in a similar way.

[bswylie]

Hi Adam - thanks so much for guiding me with this, I am really appreciative given that these are newbie questions but you’ve given me a lot more confidence that I can go down this track!

Warm regards,
Brian

[adamchainz]

Glad to help!