Project Completion Summary - Civil Affairs Bureau Project - Django 3.x Version - Development and Deployment Notes (2)

Preface

It's been a while since I last updated the blog. Recently I've still been working on the project mentioned in a previous post: Project Completion - Based on Django 3.x Version - Development and Deployment Notes

This project ran into problems in the early stages—the requirements weren't properly defined—which has led to a lot of tedious work now, leaving everyone physically and mentally exhausted. Sigh, I can only say that for a tech team, having a reliable and capable leader is extremely important.

Getting to the Point

This article continues to record some experience from Django project development.

This project is still based on my customized "DjangoStarter" project template. This project template (scaffold) integrates some commonly used third-party libraries and configurations, and includes a built-in code generator, so you can just focus on implementing business logic.

Batch Data Import

In the previous article I mentioned that the script I wrote to import a large amount of data was very slow, and then a reader commented that I could use bulk_create. So in the new requirements of phase two, after processing the data I used bulk_create to import it. The speed did improve considerably—it should be able to reach native SQL performance.

First add all the Model instances into a list, then batch-import them, and it's much faster.

Here's a pseudocode example

result = data_proc()
data = []

for item in result:
    print(f"处理：{item['name']}")
    data.append(ModelObj(name=item['name']))
    
print('正在批量导入')
ModelObj.objects.bulk_create(data)
print('完成')

Besides this batch-create API, Django ORM also supports batch updates with bulk_update, used the same way as this batch create.

Data Processing

Last time, with the requirements being urgent, after receiving several hundred MB of Excel data, I directly used Python's openpyxl library to preprocess it into JSON format, then imported it into the database one record at a time

And this data also involved multiple tables, which made data processing and import extremely slow

At the time the DB manager imported the Excel directly into a temporary database table for processing, but later we found that the data processed with SQL still had many errors after cleaning

So for the next batch of data, I chose to handle it myself. SQL isn't really suited for this kind of data cleaning~

Using openpyxl directly to process Excel is also a bit too amateurish. Python has many tools for data analysis that we can make use of, such as pandas.

The Excel given for this new requirement was really annoying—full of merged cells. It looks nice, but importing it into the database is such a hassle!

Fortunately pandas' data processing is powerful enough to handle this situation. Then, to make the whole data processing flow more intuitive, I brought in jupyter. PyCharm has it integrated now, and the experience is a bit better than the web version, though in actual use I found some bugs that somewhat affect the experience.

A data example is as follows

The first column of serial numbers is useless, ignore it. Then you can see that the people in the name column and the family members should be a one-to-many relationship. Cells were merged to look nicer, which makes processing really annoying. For merged cells, when pandas reads them in, only the first row has data.

But we can use pandas' data-filling functionality to handle this.

A simple code reference for handling merged cells:

import pandas as pd

xlsx1 = pd.ExcelFile('文件名.xlsx')
# 参数0代表第一个工作表，header=0代表第一行作为表头，uescols表示读取的列范围，我们不要第一列那个序号
df = pd.read_excel(xlsx1, 0, header=0, usecols='B:G')

df['姓名'].fillna(method='pad', inplace=True)
df['性别'].fillna(method='pad', inplace=True)
df['出生年月'].fillna(method='pad', inplace=True)
df['联系人'].fillna(method='pad', inplace=True)
df['联系电话'].fillna(method='pad', inplace=True)

There are comments in the code; just use fillna to fill in the missing fields

Then convert the DateFrame to a more easily processed JSON format (actually a dict in Python)

json_str = df.to_json(orient='records')
parsed = json.loads(json_str)

This gives you key-value pair data

PS: It seems you can iterate over df directly to get the data; converting to JSON seems like taking a detour, but I was in a hurry at the time and didn't look into it

References

• 2 methods for reading poorly structured Excel files with Pandas: https://www.shouxicto.com/article/1642.html

• Post-processing after PANDAS reads tables with merged cells: http://zhangqijun.com/2733-2/

Admin Backend Optimization

For customized projects, the Django Admin backend isn't used that much anymore, but it's still sufficient for viewing data as reports or doing simple filtering.

This project's admin interface is customized based on the simpleUI library

From the previous article you can see that I rewrote and replaced the admin backend homepage. The result is as follows

This interface is implemented with Bootstrap and AdminLTE. The AdminLTE component library is indeed quite good—it adds several nice-looking components on top of Bootstrap, very useful~

Icons use font-awesome, charts use chart.js, both of which are components you can pick up just by reading the docs. I've collected the official documentation links below, help yourself~

One thing to note: In SimpleUI, the custom homepage is implemented as an iframe! And SimpleUI itself is Vue+ElementUI, so jumping from the homepage to other admin pages themselves is very hard to implement! Keep this in mind. I haven't thought of a good solution for now—maybe I'll try a different admin theme next time~

References

• AdminLTE: https://adminlte.io/docs/3.2/components/boxes.html
• FontAwesome: http://www.fontawesome.com.cn/faicons/
• Chart.js: https://www.chartjs.org/docs/latest/
• Django Advanced (1): Advanced Admin Backend Techniques (with many images)
• Django in Practice: A Hands-on Guide to Configuring Django SimpleUI for a Beautiful Admin (with many images): https://zhuanlan.zhihu.com/p/372185998

More on Django Aggregation Queries

The previous article mentioned aggregation queries but didn't go into detail. This article mainly covers these:

• aggregate
• annotate
• values
• values_list

Based on my current understanding, the first difference between aggregate and annotate is that the former returns a dict, while the latter returns a queryset, so you can continue to perform other query operations.

aggregate

Then there's the difference in use cases. aggregate is generally used for overall data statistics, for example

Counting the number of male and female users

from django.db.models import Count

result1 = User.objects.filter(gender='男').aggregate(male_count=Count('pk', distinct=True))
result2 = User.objects.filter(gender='女').aggregate(female_count=Count('pk', distinct=True))

PS: Actually you don't need to add the distinct parameter in the Count function here, since the primary key (pk) shouldn't have duplicates anyway

The data returned looks like this

# result1
{
    "male_count": 100
}

# result2
{
    "female_count": 100
}

Should be easy to understand

annotate

As for annotate, it's generally used together with grouping operations like values. Example:

from django.db.models import Count

result1 = User.objects.values('gender').annotate(count=Count('pk'))

Returned result

[
    {
        "gender": "男",
        "count": 100
    },
    {
        "gender": "女",
        "count": 100
    }
]

In short, after grouping with values, annotate performs aggregation on the data and then inserts the custom field into each group~ A bit of a mouthful; anyway, looking at the code above makes it easy to understand.

values / values_list

Finally, values and values_list do roughly the same thing: they extract information from a certain column of the data table. (Both of these are related to grouping)

For example, say our user table looks like this

We can use this code to extract all countries

User.objects.values("country")
# 或者
User.objects.values_list("country")

The former, after grouping by the specified field, returns a Queryset containing dictionaries

<QuerySet [{'country': '中国'}, {'country': '越南'}, {'country': '新加坡'}, {'country': '马来西亚'}, {'country': '中国'}, {'country': '中国'}]>

The latter returns a Queryset containing tuples

<QuerySet [('中国',), ('越南',), ('新加坡',), ('马来西亚',), ('中国',), ('中国',)]>

Then values_list can also take a flat=True parameter, which directly returns a Queryset containing an array

<QuerySet ['中国', '越南', '新加坡', '马来西亚', '中国', '中国']>

This makes the purpose of these two functions quite intuitive.

Combining this with annotate from above, if we want to count how many people there are in each country, we can use this code

User.objects.values("country").annotate(people_count=Count('pk'))

The result is roughly like this

[
    {
        "country":  "中国",
        "people_count": 3
    },
    {
        "country":  "越南",
        "people_count": 1
    },
    {
        "country":  "新加坡",
        "people_count": 3
    },
    {
        "country":  "马来西亚",
        "people_count": 3
    }
]

Done~

There are many more scenario examples for aggregation queries. This article only covered the general idea; I'll write a new blog post to go into more detail when I have time~

References

• Python Tutorial: How to Implement Group Queries in Django: https://chinese.freecodecamp.org/news/introduction-to-django-group/
• The difference between aggregate and annotate: https://www.cnblogs.com/Young-shi/p/15174328.html
• values / values_list: https://www.jianshu.com/p/e92ab45075d5
• django_filter's values / values_list: https://blog.csdn.net/weixin_40475396/article/details/79529256

Deploying a MySQL Database with Docker

Although I've seen people say that MySQL isn't suitable for deployment with Docker, Docker is just so convenient that the advantages outweigh the disadvantages, so this project continues to use Docker.

Continue using docker-compose to orchestrate the containers.

First, if you want to start a MySQL instance locally for testing, find an empty directory, create a standalone docker-compose.yml file, with the configuration below, then run docker-compose up.

In the configuration below I set up a volumes mapping, so the MySQL database files will be saved in the mysql-data folder under this local directory

version: "3"
services:
  mysql:
    image: daocloud.io/mysql
    restart: always
    volumes:
      - ./mysql-data:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=mysql-admin
      - MYSQL_USER=test
      - MYSQL_PASS=yourpassword
    ports:
      - "3306:3306"

Use ports to expose the port, making it convenient for us to connect to the database with tools like Navicat.

Below is the configuration integrated into the web project (a simplified configuration; for the detailed configuration, see my DjangoStarter project template)

version: "3"
services:
  mysql:
    image: daocloud.io/mysql
    restart: always
    volumes:
      - ./mysql-data:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=mysql-admin
    # 注意这里使用expose而不是ports里，这是暴露端口给其他容器使用，但docker外部就无法访问了
    expose:
      - 3306
  web:
    restart: always # 除正常工作外，容器会在任何时候重启，比如遭遇 bug、进程崩溃、docker 重启等情况。
    build: .
    command: uwsgi uwsgi.ini
    volumes:
      - .:/code
    ports:
      - "80:8000"
    # 在依赖这里指定mysql容器，然后才能连接到数据库
    depends_on:
      - mysql

I've added comments for the key configuration; it's easy to understand.

References

• Wrapping MySQL with Docker-compose, initializing data, and Redis: https://www.cnblogs.com/xiao987334176/p/12669080.html
• Docker Volumes You Must Know: https://www.cnblogs.com/edisonchou/p/docker_volumes_introduction.html

About Caching Issues

The previous article mentioned how to use caching. Redis with Django's native cache decorator cache_page is no problem, but when using the cache_response decorator from the third-party drf-extensions, there's an issue: it cannot cache responses based on different query params.

For example, the following two URLs point to the same endpoint but with different parameters, so in principle they should return different data.

• http://example.api/?type=1
• http://example.api/?type=2

But after adding the cache_response decorator, no matter what parameters are passed, it always returns the same result. I still haven't figured out whether I wrote something wrong or it's a bug in this library~

Performance Optimization

The same old topic…

The previous article also mentioned this a bit, but not in detail. People always say Django ORM has poor performance, but the bottleneck is actually still on the database IO side. In the face of the most time-consuming IO operations, that bit of performance disadvantage doesn't really amount to much (especially for a toB system like ours, which has no high-concurrency requirements).

After profiling, the bottlenecks were basically all in those statistics-type APIs. The characteristic of these APIs is that they need to query across multiple related tables, and often a single API needs to hit the database multiple times, so the optimization approach is very clear: reduce the number of database requests.

Two approaches

• One is to fetch all the data into memory at once, then use a data analysis library like pandas to do the aggregation;
• One is to first do precomputation, then save the intermediate results, and next time the API is requested, read the intermediate results directly and use them for aggregation

In the end I chose the second approach, and chose to store the intermediate results in a MongoDB database

Recap

At this point the project has only produced a staged result and is still unfinished. Through this project I also discovered many problems—both the team's and my own.

As for the team, our leader here is the type who doesn't really understand technology, and also has relatively poor stress tolerance. When tasks aren't urgent they don't interfere much with our progress, but when the project is in a rush everything falls into chaos—blindly giving orders, randomly adding requirements, randomly disrupting progress; in short, just making a mess and holding us back…

Of course the biggest problem still came from the government-enterprise department. There were major issues in communicating with the client in the early stages. Of course this may also be related to the chaotic structure of state-owned enterprises. There were even major problems with the contract—the requirements were never written clearly, which led to the client adding unlimited requirements after delivery.

In reality a government-enterprise project involves too many non-technical factors. This really shouldn't be something we technical people need to worry about, but that's just how reality is, alas.