Shulou(Shulou.com)06/02 Report--

This article focuses on "how to understand Python reports and logs". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "how to understand Python reports and logs"!

Export Excel report

A report is to dynamically display data in tables, charts, and other formats, so someone uses this formula to describe the report:

Report = multiple formats + dynamic data

There are many tripartite libraries that support writing Excel files in Python programs, including xlwt, xlwings, openpyxl, xlswriter, pandas and so on. Although xlwt only supports writing Excel files in xls format, it performs well in terms of performance. Let's take xlwt as an example to demonstrate how to export an Excel report in a Django project, such as an Excel table that contains all the teacher information.

Def export_teachers_excel (request):

# create a workbook

Wb = xlwt.Workbook ()

# add worksheet

Sheet = wb.add_sheet ('teacher information table')

# query the information of all teachers (Note: this place needs to be optimized later)

Queryset = Teacher.objects.all ()

# write headers to the Excel form

Colnames = ('name', 'introduction', 'high praise', 'poor rating', 'discipline')

For index, name in enumerate (colnames):

Sheet.write (0, index, name)

# write the teacher's data to the cell

Props = ('name',' detail', 'good_count',' bad_count', 'subject')

For row, teacher in enumerate (queryset):

For col, prop in enumerate (props):

Value = getattr (teacher, prop,'')

If isinstance (value, Subject):

Value = value.name

Sheet.write (row + 1, col, value)

# Save Excel

Buffer = BytesIO ()

Wb.save (buffer)

# write binary data to the message body of the response and set the MIME type

Resp = HttpResponse (buffer.getvalue (), content_type='application/vnd.ms-excel')

# Chinese file names need to be encoded as percent signs

Filename = quote ('teacher .xls')

# tell the browser to download the file and the corresponding file name through the response header

Resp ['content-disposition'] = fanciattachment; filename= "{filename}"

Return resp

Map URL.

Urlpatterns = [

# omit the above code here

Path ('excel/', views.export_teachers_excel)

# the following code is omitted here

]

Generate front-end statistical chart

If you need to generate front-end statistical charts in the project, you can use Baidu's ECharts. The specific approach is that the back-end returns the data needed for statistical charts by providing data interfaces, and the front-end uses ECharts to render bar charts, line charts, pie charts, scatter charts and other charts. For example, if we want to generate a report that counts the favorable and poor reviews of all teachers, we can do it in the following way.

Def get_teachers_data (request):

# query the information of all teachers (Note: this place also needs to be optimized later)

Queryset = Teacher.objects.all ()

# use generation to put the teacher's name in a list

Names = [teacher.name for teacher in queryset]

# use generative formula to put the teacher's praise in a list

Good = [teacher.good_count for teacher in queryset]

# use generation to put the teacher's poor scores in a list

Bad = [teacher.bad_count for teacher in queryset]

# return data in JSON format

Return JsonResponse ({'names': names,' good': good, 'bad': bad})

Map URL.

Urlpatterns = [

# omit the above code here

Path ('teachers_data/', views.export_teachers_excel)

# the following code is omitted here

]

Use ECharts to generate a bar chart.

Teacher evaluation statistics

Go back to the front page

Var myChart = echarts.init (document.querySelector ('# main'))

Fetch ('/ teachers_data/')

.then (resp = > resp.json ())

.then (json = > {

Var option = {

Color: ['# f00','# 00f']

Title: {

Text: 'teacher Evaluation Statistical Chart'

}

Tooltip: {}

Legend: {

Data: ['good', 'bad']

}

XAxis: {

Data: json.names

}

YAxis: {}

Series: [

{

Name: 'high praise'

Type: 'bar'

Data: json.good

}

{

Name: 'bad review'

Type: 'bar'

Data: json.bad

}

]

}

MyChart.setOption (option)

})

Let's run it!

Configuration log

During the development phase of the project, it is necessary to display enough debugging information to assist developers in debugging the code; after the project is online, it is also necessary to record warnings, errors and other information when the system is running so that the relevant personnel can understand the running status of the system and maintain the code. To do these two events well, we need to configure the log for the Django project.

The log configuration of Django can be done by referring to the official documents and combining with the actual needs of the project. These contents can basically be copied from the official documents and then adjusted locally. Some reference configurations are given below.

LOGGING = {

'version': 1

# whether to disable existing loggers

'disable_existing_loggers': False

# Log formatter

'formatters': {

'simple': {

'format':'% (asctime) s% (module) s.% (funcName) s:% (message) s'

'datefmt':'% Y-%m-%d% HRV% MVA% S'

}

'verbose': {

'format':'% (asctime) s% (levelname) s [% (process) dmurf% (threadName) s]'

'% (module) s.% (funcName) s line% (lineno) d:% (message) s'

'datefmt':'% Y-%m-%d% HRV% MVA% S'

}

}

# Log filter

'filters': {

# only works when the debug value is True in the Django configuration file

'require_debug_true': {

'()': 'django.utils.log.RequireDebugTrue'

}

}

# Log processor

'handlers': {

# output to the console

'console': {

'class': 'logging.StreamHandler'

'level': 'DEBUG'

'filters': ['require_debug_true']

'formatter': 'simple'

}

# output to file (cut once a week)

'file1': {

'class': 'logging.handlers.TimedRotatingFileHandler'

'filename': 'access.log'

'when': 'W0'

'backupCount': 12

'formatter': 'simple'

'level': 'INFO'

}

# output to file (cut once a day)

'file2': {

'class': 'logging.handlers.TimedRotatingFileHandler'

'filename': 'error.log'

'when': 'D'

'backupCount': 31

'formatter': 'verbose'

'level': 'WARNING'

}

}

# logger recorder

'loggers': {

'django': {

# Log processor to be used

'handlers': [' console', 'file1',' file2']

# whether to propagate log information upwards

'propagate': True

# Log level (not necessarily the final log level)

'level': 'DEBUG'

}

}

}

You may have noticed that the formatters in the log configuration above is the log formatter, which represents how to format the output log, where the format placeholder indicates:

% (name) s-name of the logger

% (levelno) s-logging level in digital form

% (levelname) s-text name of the logging level

% (filename) s-the file name of the source file that performed the logging call

% (pathname) s-path name of the source file that performed the logging call

% (funcName) s-name of the function that performed the logging call

% (module) s-name of the module that performed the logging call

% (lineno) s-Line number on which the logging call was performed

% (created) s-time the logging was performed

% (asctime) s-date and time

% (msecs) s-millisecond portion

% (thread) d-Thread ID (integer)

% (threadName) s-Thread name

% (process) d-process ID (integer)

The handlers in the log configuration is used to specify the log processor. To put it simply, it specifies whether to output the log to the console, a file or a server on the network. Available processors include:

Logging.StreamHandler (stream=None)-you can output information to any file object similar to sys.stdout or sys.stderr

Logging.FileHandler (filename, mode='a', encoding=None, delay=False)-writes log messages to a file

Logging.handlers.DatagramHandler (host, port)-sends log information to network hosts with specified hosts and ports using the UDP protocol

Logging.handlers.HTTPHandler (host, url)-use HTTP's GET or POST method to upload log messages to a HTTP server

Logging.handlers.RotatingFileHandler (filename, mode='a', maxBytes=0, backupCount=0, encoding=None, delay=False)-writes log messages to a file, and if the size of the file exceeds the value specified by maxBytes, a file will be regenerated to log

Logging.handlers.SocketHandler (host, port)-sends log information to network hosts with specified hosts and ports using the TCP protocol

Logging.handlers.SMTPHandler (mailhost, fromaddr, toaddrs, subject, credentials=None, secure=None, timeout=1.0)-outputs the log to the specified email address

Logging.MemoryHandler (capacity, flushLevel=ERROR, target=None, flushOnClose=True)-outputs the log to the buffer specified by memory

Each of the above log handlers specifies an attribute called "level", which represents the log level, and different log levels reflect the severity of the information recorded in the log. Six levels of logs are defined in Python, in order from low to high: NOTSET, DEBUG, INFO, WARNING, ERROR, CRITICAL.

The last configured logger is used to actually output logs, and the Django framework provides a built-in logger as follows:

Django-all message loggers in the Django hierarchy

Django.request-Log messages related to request processing. The 5xx response is treated as an error message; the 4xx response is treated as a warning message

Django.server-Log messages related to requests received by the server called through runserver. The 5xx response is considered an error message; the 4xx response is logged as a warning message; everything else is logged as INFO

Django.template-Log messages related to template rendering

Django.db.backends-there are log messages that interact with the database, and you can use this logger if you want to display the SQL statements executed by the ORM framework.

The log level configured in the logger may not be the final log level, because it is also necessary to refer to the log level configured in the log processor, taking the higher of the two as the final log level.

Configure Django-Debug-Toolbar

Django-Debug-Toolbar is an artifact to assist debugging and optimization in the project development phase. As long as it is configured, you can easily view the project operation information shown in the following table, which is very important for debugging the project and optimizing the performance of Web applications.

Install Django-Debug-Toolbar. Pip install django-debug-toolbar

Configuration-modify settings.py. INSTALLED_APPS = ['debug_toolbar',] MIDDLEWARE = [' debug_toolbar.middleware.DebugToolbarMiddleware',] DEBUG_TOOLBAR_CONFIG = {# introduce jQuery library 'JQUERY_URL':' https://cdn.bootcss.com/jquery/3.3.1/jquery.min.js', # whether the toolbar collapses' SHOW_COLLAPSED': True, # whether the toolbar 'SHOW_TOOLBAR_CALLBACK': lambda x: True,}

Configuration-modify urls.py. If settings.DEBUG: import debug_toolbar urlpatterns.insert (0, path ('_ debug__/', include (debug_toolbar.urls)

Use-as shown in the following figure, after configuring Django-Debug-Toolbar, you will see a debugging toolbar on the right side of the page, which contains all kinds of debugging information mentioned earlier, including execution time, project settings, request headers, SQL, static resources, templates, caches, signals, etc., which is very easy to view.

Optimize ORM code

After configuring the log or Django-Debug-Toolbar, we can take a look at the implementation of the view function that exports teacher data to Excel reports. Here we focus on what the SQL query generated by the ORM framework looks like. I believe the results here will surprise you a little bit. After executing Teacher.objects.all (), we can notice that the SQL seen on the console or output through Django-Debug-Toolbar looks like this:

SELECT `tb_ teacher`.`no`, `tb_ teacher`.`name`, `tb_ teacher`.`detail`, `tb_ teacher`.`photo`, `tb_ teacher`.`good _ count`, `tb_ teacher`.`bad _ count`, `tb_ teacher`.`sno` FROM `tb_ teacher`; args= ()

SELECT `tb_ Secrett`.`no`, `tb_ Secrett`.`name`, `tb_ Secrett`.`introspec`, `tb_ account`.`create _ date`, `tb_ account`.`is _ hot`FROM `tb_ roomt`WHERE `tb_ Secrett`.`no` = 101; args= (101,)

SELECT `tb_ Secrett`.`no`, `tb_ Secrett`.`name`, `tb_ Secrett`.`introspec`, `tb_ account`.`create _ date`, `tb_ account`.`is _ hot`FROM `tb_ roomt`WHERE `tb_ Secrett`.`no` = 101; args= (101,)

SELECT `tb_ Secrett`.`no`, `tb_ Secrett`.`name`, `tb_ Secrett`.`introspec`, `tb_ account`.`create _ date`, `tb_ account`.`is _ hot`FROM `tb_ roomt`WHERE `tb_ Secrett`.`no` = 101; args= (101,)

SELECT `tb_ Secrett`.`no`, `tb_ Secrett`.`name`, `tb_ Secrett`.`introspec`, `tb_ account`.`create _ date`, `tb_ account`.`is _ hot`FROM `tb_ roomt`WHERE `tb_ Secrett`.`no` = 101; args= (101,)

SELECT `tb_ Secrett`.`no`, `tb_ Secrett`.`name`, `tb_ account`.`introspec`, `tb_ account`.`create _ date`, `tb_ account`.`is _ hot`FROM `tb_ roomt`WHERE `tb_ account`.`no` = 103; args= (103,)

SELECT `tb_ Secrett`.`no`, `tb_ Secrett`.`name`, `tb_ account`.`introspec`, `tb_ account`.`create _ date`, `tb_ account`.`is _ hot`FROM `tb_ roomt`WHERE `tb_ account`.`no` = 103; args= (103,)

The problem here is often called "1 query" (or "query 1"). Originally, only one SQL is needed to get the teacher's data, but because the teacher is associated with the discipline, when we query N pieces of teacher's data, Django's ORM framework sends N items of SQL to the database to query the teacher's subject information. Each SQL execution will have a lot of overhead and will put pressure on the database server, if you can complete the query of teachers and subjects in a SQL is certainly a better practice, this is also very easy to do, I believe you have figured out how to do. Yes, we can use join queries, but how do we do this when using Django's ORM framework? For many-to-one relationships (such as teachers and subjects in voting applications), we can use QuerySet's select_related () method to load associated objects, while for many-to-many relationships (such as orders and merchandise on e-commerce sites), we can use the prefetch_related () method to load associated objects.

In the view function that exports the teacher's Excel report, we can optimize the code in the following way.

Queryset = Teacher.objects.all () .select_related ('subject')

In fact, using ECharts to generate the front-end report of the view function, query teacher praise and bad data operation can also be optimized, because in this example, we only need to get the teacher's name, praise and bad rating of these three data, but in the default situation generated SQL will query all the fields of the teacher table. You can use the only () method of QuerySet to specify the properties that need to be queried, or you can use the defer () method of QuerySet to specify the properties that do not need to be queried for the time being. The generated SQL will use the projection operation to specify the columns that need to be queried, thus improving the query performance. The code is as follows:

Queryset = Teacher.objects.all () .only ('name',' good_count', 'bad_count')

Of course, if you want to count the average of good and bad reviews of teachers in each subject, you can also use Django's ORM framework. The code is as follows:

Queryset = Teacher.objects.values ('subject'). Annotate (

Good=Avg ('good_count'), bad=Avg (' bad_count'))

The elements in the QuerySet obtained here are dictionary objects, and there are three sets of key-value pairs in each dictionary, namely the subject representing the subject number, the good representing the good score and the bad representing the bad score. If you want to get the name of the discipline instead of the number, you can adjust the code as follows:

Queryset = Teacher.objects.values ('subject__name'). Annotate (

Good=Avg ('good_count'), bad=Avg (' bad_count'))

It can be seen that Django's ORM framework allows us to complete grouping and aggregation queries in relational databases in an object-oriented way.

At this point, I believe you have a deeper understanding of "how to understand Python reports and logs". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.