I am planning a use case, to develop a web application in Django that uses clients database and generate some reports etc., For the chosen industry, all the clients will have the same data but stored in different tables with different column names. I should be able to connect to their databases and pull the required data into my generic web application to display some reports. So I am planning these and require guidance to do in the right way:
Connecting to database - As in standard SaaS model, a Middleware and DatabaseRouter should help me to connect to the client database.
Map the tables and fields - inspectdb command helps to prepare equivalent models for the clients database. But since I dont want all the database tables, I would like to map only required tables and required fields to my application models. This will be a one time configuration for each client. What would be the right approach to do this. I would also like to know how to map if the client is providing me an API access.
Since I dont expect all the clients to have similar API/Legacy database structure, I want to find out a way to map them through a configuration setting and link to my Django application models.
Using inspectdb is the starting point, not the ending point. After running inspectdb, it’s appropriate and expected that you will be editing what is generated to make it “right”. In this case, it simply means removing the generated models that you aren’t going to need.
If you’re talking about API access to that data, models wouldn’t be involved at all, at least not using the standard ORM. You’ll need to design or create the appropriate classes for handling that data internally.
Right, for each client inspectdb will create a set of Models. From these models I would like to pick required information and link to my Django application Models. This way, my application will run despite different clients storing the data in different table names or fields. Example:
From above, same information is stored by different clients in different tables. And during initial configuration, the client is required to map that employee_name in Employee model to his Table and Field. This way, I just work on my models and if the datasources are mapped, the application works for any client database.
If in API mode, I can do something like this to map the dict values to correspond my model fields.
class Employee(models.Model):
employee_name = models.CharField()
@classmethod
def create_from_json(cls, emp): #emp is dict
json2model = {'full_name': 'employee_name'}
return cls(**{json2model[k]:v for k,v in emp.items()})
emp = Employee.create_from_json(json.loads(json_emp))
Using RawSQL, things are getting pretty easy to work on legacy databases of clients but I would like such functionality with the potential of Django models and ORM.
Yes that works, but unless you’re saving that copy of the data to your database, I’m not sure what value that provides.
Using inspectdb will certainly help to facilitate that. But it’s not something that you need to use. You can always manually create the models needed for accessing those tables.
No. You can ignore the ORM completely and manage your own connections and SQL.
You can connect to any database within your code - Django doesn’t restrict what you do. You’re just doing it without any benefits of the ORM, which means you need to manage your connections, transactions, SQL generation, etc yourself.
Starting point. If I could connect to a database and read the data in tabular format, I can write some wrapper classes to link the columns to Django Models and work on them for ORM.
The idea is not to save any database in my application but use client connection string to connect to their database and generate some reports.
You might be able to load the data into those models, but at that point, you’re not really using the ORM. (You’re not going to have access to querysets, filters, relationship managers, connection management, query caches, etc) This seems like a lot of work for very little gain.