Managing existing databases with Django

In most projects, there are always almost constant CRUD operations, which are tedious to write, but one of the essential features of the system as a whole. We also face similar problems in the previous project, although we have implemented a relatively complete management background and tried to do code reuse, but as the size of the project grows, the sample code that needs to be written is expanding, taking up a lot of development time.

In the face of this situation, I naturally think of Django. You know, the Django Admin is almost tailored to this demand. But for our project, there are a few more issues to solve:

• Our database uses SQL Server. Django does not have a good support for this by default;
• The database structure is managed by another tool, and Django does not have permissions to directly modify the database structure. Because
• This, we cannot use Django migrate;
  For the same reason, we were unable to create a data table (including Auth/session, etc.) for the Django Admin built-in requirements in the database.
  Let's solve these problems. If you encounter a similar situation, you can refer to this article. SQL Server Support

Unfortunately, there are several types of SQL Server adapters developed for Django, but they are older and there are problems with the new version of Django support. After trying, have we chosen? Django-mssql, although the feature is available, the library only supports Django 1.8, tested and not compatible with Django 1.11, and Django 2.x is even worse. Fortunately, we don't need very new features, so we've locked the version with virtualenv:

Django==1.8django-mssql==1.8pywin32==223

Here is still to recommend my own built Python Development Learning Group: 725479218, the group is learning Python development, if you are learning Python, small series welcome you to join, everyone is the software Development Party, Do not regularly share dry goods (only Python software development related), including my own 2018 of the latest Python advanced information and high-level development tutorials, welcome to advanced and into the small partners to deep python
Django-mssql is the Windows version of the library, behind the scenes using ADO as the driver, so also to install Pywin32.

Multiple databases

There are basically two ideas for the second and third questions. The first one is to skip the Django built-in database-based implementation by implementing a custom backend. It works in principle, but it's a simple attempt to find a lot of custom parts and too much work. In short, this road is not very desirable.

The second idea is to take advantage of Django's multi-database support. Since the business database is not managed by Django, then a database is used to support the basic functionality of Django, and Django only queries and updates the business database and does not execute migrate. Of course, in order to use multiple databases, we need to do more work on the configuration. As users of the background are basically only the company's internal business personnel, the amount of data will not be large, with the server-level database is sledgehammer suspicion. For simplicity, the default SQLite is used as the built-in database:

DATABASES = {    ‘default‘: {        ‘ENGINE‘: ‘django.db.backends.sqlite3‘,        ‘NAME‘: os.path.join(BASE_DIR, ‘db.sqlite3‘),    },    ‘mydb‘: {        ‘ENGINE‘: ‘sqlserver_ado‘,        ‘HOST‘: ‘127.0.0.1‘,        ‘NAME‘: ‘<DB_NAME>‘,        ‘USER‘: ‘<DB_USER>‘,        ‘PASSWORD‘: ‘<DB_PASSWORD>‘,        ‘OPTIONS‘: {            ‘provider‘: ‘SQLOLEDB‘,        }    }}

It should be noted that the default value provided by Django-mssql for the provider option (according to the official document should be SQLCLI10) results in a "Provider not found" error. Because provider settings depend on ADO's registration information, not necessarily on all machines, you may need to test your own to decide which option is available.

We now have two data sources configured, but we also need to tell Django about their control relationships with the model. Achieving this can be defined at various levels, such as statement/entity/Global. For our needs, the correspondence is fixed, model-by-definition is not necessary, and global definition is the simplest. The object that implements this definition is called database Router in the Django terminology. First, define the class name in settings.py:

DATABASE_ROUTERS = [‘project.db.MyAppRouter‘]

Then complete the implementation of the class:

class MyAppRouter:    def db_for_read(self, model, **hints):        if model._meta.app_label == ‘myapp‘:            return ‘myapp‘        return None    def db_for_write(self, model, **hints):        if model._meta.app_label == ‘myapp‘:            return ‘myapp‘        return None    def allow_relation(self, obj1, obj2, **hints):        return None    def allow_migrate(self, db, app_label, model_name=None, **hints):        return False

Data routing requires four methods to be implemented according to Django requirements. The main is to read and write two methods, we need to determine which data source to match according to the model. The other two methods are not very meaningful at the moment, according to the default implementation.

Defining the Model

Configuration to this completion, the next step is to create the model. For data tables that already exist, you can use the administrative command inspectdb to reverse generate code, reducing the burden of some manual input. But the generated code may not exactly meet your requirements, so you should check it yourself. For SQL Server, if the primary key name is not the default ID, then INSPECTDB does not seem to recognize them automatically, so we need to check if the primary key field is Primary_key, and if not, add it.

python manage.py inspectdb --database=myapp > myapp\models.py

To facilitate debugging and identifying records, we typically add verbose_name to the model class and overload the built-in string method.

class XXModel(models.Model):    XXId = models.BigIntegerField(primary_key=True)    ...    class Meta:        managed = False        db_table = ‘XXModel‘        verbose_name = ‘模型名称‘        verbose_name_plural = ‘模型名称‘    def __str__(self):        return self.XXField

Add the model to admin, the corresponding background management information is completed.

admin.site.register(XXModel, XXAdmin)

Run the program

Finally, generate the necessary tables for the built-in database and create an administrator account to run the program. The following commands do not need to be explained:

$ python manage.py migrate$ python manage.py createsuperuser$ python manage.py runserver

# #总结
Our first version of the daemon was coded by hand, and took about two weeks to complete. The problem is that a large number of sample code is added manually for each additional model. It took only a day to rewrite it to Django, including getting familiar with the data and how to use it, and adding a model takes a few minutes. That's why many developers who know Django are moving to other platforms and looking for similar projects. As far as I know, Spring Boo and Django are conceptually similar, but Boo mostly goes for code-generation routes, more complexity, and theoretically more flexibility (I haven't studied in depth). The Nodejs community has keystone.js and Sails.js, but the former is specifically for MongoDB, which supports a variety of database backend, but has heard signs of a recent halt to development. The. Net community used to have a dynamicdata, and now it doesn't seem to have the following. Django, which has been developing for years, should also be the most mature, ecologically and most complete product in its class.

The potential problem with Django is the lack of a modern interface, and the difficulty of deep customization. But for our back-end applications, these are acceptable costs.

Managing existing databases with Django