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

This article is about how to use Python to easily develop database fetch download tools, the editor feels very practical, so share with you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.

1 introduction

This is the 14th issue of my series of tutorials, "Python+Dash Rapid web Application Development". In the first two issues, we introduced the custom style, front and rear paging, cell content editing and other features of dash_table.

While there are many advanced features in dash_table, which can greatly enrich the interactive ability of the web table rendered by DataTable (), let's learn some of the more practical features.

Figure 1

2 more practical features of dash_table 2.1 more table interaction features

At the end of the last article, we learned to set the parameter editable=True so that the rendered table can be edited by double-clicking the mouse, while dash_table has more practical interactive capabilities:

2.1.1 sort by column

Ordinary single-column sorting

In DataTable (), we only need to set the parameter sort_action='native', to enable the column sorting function. At this time, a part appears in the cell of each list for us to click and switch the sorting method:

App1.py

Import dash import dash_table import dash_bootstrap_components as dbc import seaborn as sns df = sns.load_dataset ('iris') app = dash.Dash (_ _ name__) app.layout = dbc.Container ([dash_table.DataTable (data=df.to_dict (' records'), columns= [{'name': column) 'id': column} for column in df.columns], style_table= {' height': '500pxcodes,' overflow-y': 'auto'}, sort_action='native')] Style= {'margin-top':' 50px'}) if _ _ name__ = ='_ main__': app.run_server (debug=True)

Figure 2

Multi-column sorting based on back-end sorting

When setting sort_action='native' in DataTable (), it corresponds to the front-end mode of "sort by column", that is, sorting under the premise that the data is injected into the browser at one time, which is not only not suitable for large datasets, but also only supports "single-column sorting".

When the data rendering mode is backend mode, we can achieve multi-column sorting by setting parameters sort_action='custom' and sort_mode='multi', to obtain the column names and ascending and descending order recorded in attribute sort_by in callback.

We modified the previous issue of app2.py to get the following example:

App2.py

Import dash import dash_bootstrap_components as dbc import dash_table from dash.dependencies import Input, Output import seaborn as sns df = sns.load_dataset ('iris') df.insert (0,' #, df.index) app = dash.Dash (_ name__) app.layout = dbc.Container ([dbc.Spinner (dash_table.DataTable (id='dash-table') Columns= [{'name': column,' id': column} for column in df.columns], page_size=15, # set up to display 15 rows of records per page page_action='custom', page_current=0 Style_header= {'font-family':' Times New Romer', 'font-weight':' bold', 'text-align':' center'}, style_data= {'font-family':' Times New Romer' 'text-align':' center'}, sort_action='custom', sort_mode='multi')], style= {'margin-top':' 50px'}) @ app.callback ([Output ('dash-table',' data')) Output ('dash-table',' page_count')], [Input ('dash-table',' page_current'), Input ('dash-table',' page_size'), Input ('dash-table',' sort_by')]) def refresh_page_data (page_current, page_size Sort_by): if sort_by: return (df .sort _ values ([col ['column_id'] for col in sort_by]) Ascending= [col ['direction'] = =' asc' for col in sort_by]). Ilocs [page _ current * page_size: (page_current + 1) * page_size] .to _ dict ('records') 1 + df.shape [0] / / page_size) return (df.ilok [page _ current * page_size: (page_current + 1) * page_size] .to _ dict ('records'), 1 + df.shape [0] / / page_size) if _ name__ = =' _ main__': app.run_server (debug=True)

Figure 3

2.1.2 filter by column criteria

In addition to sorting based on specified fields, dash_table also supports column native filtering. By setting filter_action= "filter", you can turn on the basic column condition filtering feature, and there will be more cells under each column header for users to enter filter criteria:

App3.py

Import dash import dash_table import dash_bootstrap_components as dbc import seaborn as sns df = sns.load_dataset ('iris') app = dash.Dash (_ _ name__) app.layout = dbc.Container ([dash_table.DataTable (data=df.to_dict (' records'), columns= [{'name': column) 'id': column} for column in df.columns], # Custom criteria filter cell style style_filter= {' font-family': 'Times New Romer',' background-color':'# e3f2fd'} Style_table= {'height':' 500pxframes, 'overflow-y':' auto'}, style_header= {'font-family':' Times New Romer', 'font-weight':' bold' 'text-align':' center'}, style_data= {'font-family':' Times New Romer', 'text-align':' center'}, filter_action= "native")] Style= {'margin-top':' 50px'}) if _ _ name__ = ='_ main__': app.run_server (debug=True)

Figure 4

While the conditional filtering syntax that comes with dash_table is very rich, conditional friends can go to https://dash.plotly.com/datatable/filtering to learn more.

Dash_table can also implement back-end filtering, which is similar to the previous back-end sorting. It mainly uses the callback changes of filter_query attributes to filter data in the background based on frameworks such as pandas, which is relatively simple, so I won't repeat it here.

2.2 built-in data table download function

Dash_table also comes with a simple feature to download the currently rendered table content directly to a file in csv or xlsx format. Set the format of the exported file through the parameter export_format, but the style of the built-in download button is ugly. If you have higher requirements for this, it is recommended to design the relevant functions by yourself combined with the previous "upload and download":

Figure 5

2.3 freeze the first line

By setting the parameter fixed_rows= {'headers': True}, we can always keep the header frozen during the slide to view the table:

Figure 6

3 develop an online fetch tool

After learning today's content, let's combine the download function mentioned in "upload and download" to create a simple tool for fast conditional filtering and downloading of data tables in a specified database, in which the derived_virtual_data attribute of DataTable records the table data currently displayed after sorting, conditional filtering, and other operations:

Figure 7

App4.py

Import dash import dash_bootstrap_components as dbc import dash_core_components as dcc import dash_html_components as html import dash_table from dash.dependencies import Input Output from flask import send_from_directory import os import uuid from sqlalchemy import create_engine import pandas as pd try: os.mkdir ("downloads") except FileExistsError: pass engine = create_engine ('mysql+pymysql://root:mysql@localhost/DASH') app = dash.Dash (_ _ name__) @ app.server.route (' / download/') def download (file): return send_from_directory ('downloads') File) app.layout = dbc.Container ([dbc.Row ([dbc.Col ('update data table', id='refresh-tables', style= {'width':' 100%'}), width=2), dbc.Col (id='table-select', style= {'width':' 100%'})) ), html.Hr (), dash_table.DataTable (id='dash-table', editable=True, page_size=15, style_header= {'font-family':' Times New Romer', 'font-weight':' bold') 'text-align':' center'}, style_data= {'font-family':' Times New Romer', 'text-align':' center'} Style_data_conditional= [{# customize the cell in the selected state "if": {"state": "selected"}, "background-color": "# b3e5fc", "border": "none"} ], filter_action= "native"), html.Br (), html.A (id='download-url', target= "_ blank")], style= {'margin-top':' 50px'}) @ app.callback (Output ('table-select',' options'), Input ('refresh-tables') ) def refresh_tables (n_clicks): if n_clicks: return [{'label': table,' value': table} for table in pd.read_sql_query ('SHOW TABLES') Con=engine) ['Tables_in_dash']] return dash.no_update @ app.callback ([Output (' dash-table', 'data'), Output (' dash-table', 'columns')], Input (' table-select', 'value')) def render_dash_table (value): if value: df = pd.read_sql_table (value Con=engine) return df.to_dict ('records'), [{' name': column, 'id': column} for column in df.columns] else: return [], [] @ app.callback ([Output ("download-url", "href"), Output ("download-url", "children")], [Input ("dash-table") "derived_virtual_data"), Input ("dash-table", "filter_query")], prevent_initial_call=True) def download_table (derived_virtual_data, filter_query): if derived_virtual_data: print (derived_virtual_data) filename = f "output_ {uuid.uuid1 ()} .xlsx" pd.DataFrame (derived_virtual_data). To_excel ("downloads/" + filename Index=False) return "/ download/" + filename, "download current status table" return "," if _ _ name__ = ='_ main__': app.run_server (debug=True) above is how to easily develop database fetch download tool with Python The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.