用Python轻松开发数据库取数下载工具

 1 简介

这是我的系列教程「Python+Dash快速web应用开发」的第十四期，在前两期中，我们针对dash_table的自定义样式、前后端分页、单元格内容编辑等特点展开了介绍。

而在dash_table中还有很多高级特性，可以极大程度上丰富DataTable()所渲染网页表格的交互能力，今天的文章作为「交互表格篇」的下篇，我们就来一起学习其中比较实用的一些特性。

图1

2 dash_table的更多实用功能

2.1 更多表格交互特性

上一期文章最后我们学习了通过设置参数editable=True，使得渲染出的表格可以通过鼠标双击进行编辑，而dash_table除此之外，还有更多实用的交互能力：

2.1.1 按列排序

• 普通单列排序

在DataTable()中，我们只需要设置参数sort_action='native'，即可开启列排序功能，此时每一列列名单元格内都会出现部件供我们点击切换排序方式：

app1.py

1. import dash 
2. import dash_table 
3. import dash_bootstrap_components as dbc 
4.  
5. import seaborn as sns 
6.  
7. df = sns.load_dataset('iris') 
8.  
9. app = dash.Dash(__name__) 
10.  
11. app.layout = dbc.Container( 
12.     [ 
13.         dash_table.DataTable( 
14.             data=df.to_dict('records'), 
15.             columns=[ 
16.                 {'name': column, 'id': column} 
17.                 for column in df.columns 
18.             ], 
19.             style_table={ 
20.                 'height': '500px', 
21.                 'overflow-y': 'auto' 
22.             }, 
23.             sort_action='native' 
24.         ) 
25.     ], 
26.     style={ 
27.         'margin-top': '50px' 
28.     } 
29. ) 
30.  
31. if __name__ == '__main__': 
32.     app.run_server(debug=True) 

图2

• 基于后端排序的多列排序

在DataTable()中设置sort_action='native'时，对应的是「按列排序」的前端模式，也即是数据一次性灌注到浏览器的前提下进行排序，这种方式不仅不适合大型数据集，而且只支持「单列排序」。

而当数据渲染方式为后端模式时，我们通过设置参数sort_action='custom'以及sort_mode='multi'，配合在回调中获取属性sort_by中记录的参与排序的列名及升序降序方式，就可以实现多列排序。

我们在上一期的app2.py的基础上修改得到下面的例子：

app2.py

1. import dash 
2. import dash_bootstrap_components as dbc 
3. import dash_table 
4. from dash.dependencies import Input, Output 
5.  
6. import seaborn as sns 
7.  
8. df = sns.load_dataset('iris') 
9. df.insert(0, '#', df.index) 
10.  
11. app = dash.Dash(__name__) 
12.  
13. app.layout = dbc.Container( 
14.     [ 
15.         dbc.Spinner( 
16.             dash_table.DataTable( 
17.                 id='dash-table', 
18.                 columns=[ 
19.                     {'name': column, 'id': column} 
20.                     for column in df.columns 
21.                 ], 
22.                 page_size=15,  # 设置单页显示15行记录行数 
23.                 page_action='custom', 
24.                 page_current=0, 
25.                 style_header={ 
26.                     'font-family': 'Times New Romer', 
27.                     'font-weight': 'bold', 
28.                     'text-align': 'center' 
29.                 }, 
30.                 style_data={ 
31.                     'font-family': 'Times New Romer', 
32.                     'text-align': 'center' 
33.                 }, 
34.                 sort_action='custom', 
35.                 sort_mode='multi' 
36.             ) 
37.         ) 
38.     ], 
39.     style={ 
40.         'margin-top': '50px' 
41.     } 
42. ) 
43.  
44.  
45. @app.callback( 
46.     [Output('dash-table', 'data'), 
47.      Output('dash-table', 'page_count')], 
48.     [Input('dash-table', 'page_current'), 
49.      Input('dash-table', 'page_size'), 
50.      Input('dash-table', 'sort_by')] 
51. ) 
52. def refresh_page_data(page_current, page_size, sort_by): 
53.  
54.     if sort_by: 
55.         return ( 
56.             df 
57.             .sort_values( 
58.                 [col['column_id'] for col in sort_by], 
59.                 ascending=[ 
60.                     col['direction'] == 'asc' 
61.                     for col in sort_by 
62.                 ] 
63.             ) 
64.             .iloc[page_current * page_size:(page_current + 1) * page_size] 
65.             .to_dict('records'), 
66.             1 + df.shape[0] // page_size 
67.         ) 
68.  
69.     return ( 
70.         df.iloc[page_current * page_size:(page_current + 1) * page_size].to_dict('records'), 
71.         1 + df.shape[0] // page_size 
72.     ) 
73.  
74.  
75. if __name__ == '__main__': 
76.     app.run_server(debug=True) 

图3

2.1.2 按列条件筛选

除了基于指定字段进行排序之外，dash_table还支持列的条件筛选，设置filter_action="native"，就可以开启基础的按列条件筛选功能，此时每一列表头下都会多出供用户输入筛选条件的单元格：

app3.py

1. import dash 
2. import dash_table 
3. import dash_bootstrap_components as dbc 
4.  
5. import seaborn as sns 
6.  
7. df = sns.load_dataset('iris') 
8.  
9. app = dash.Dash(__name__) 
10.  
11. app.layout = dbc.Container( 
12.     [ 
13.         dash_table.DataTable( 
14.             data=df.to_dict('records'), 
15.             columns=[ 
16.                 {'name': column, 'id': column} 
17.                 for column in df.columns 
18.             ], 
19.             # 自定义条件筛选单元格样式 
20.             style_filter={ 
21.                 'font-family': 'Times New Romer', 
22.                 'background-color': '#e3f2fd' 
23.             }, 
24.             style_table={ 
25.                 'height': '500px', 
26.                 'overflow-y': 'auto' 
27.             }, 
28.             style_header={ 
29.                 'font-family': 'Times New Romer', 
30.                 'font-weight': 'bold', 
31.                 'text-align': 'center' 
32.             }, 
33.             style_data={ 
34.                 'font-family': 'Times New Romer', 
35.                 'text-align': 'center' 
36.             }, 
37.             filter_action="native" 
38.         ) 
39.     ], 
40.     style={ 
41.         'margin-top': '50px' 
42.     } 
43. ) 
44.  
45. if __name__ == '__main__': 
46.     app.run_server(debug=True) 

图4

而dash_table中自带的条件筛选语法很丰富，有条件的朋友可以前往https://dash.plotly.com/datatable/filtering了解更多。

而dash_table同样可以实现后端筛选，和前面的后端排序类似，主要利用filter_query属性的回调变化在后台基于pandas等框架进行数据筛选，比较简单，这里就不再赘述。

2.2 自带的数据表格下载功能

dash_table还自带了将当前所渲染的表格内容直接下载为csv或xlsx格式文件的简易功能，通过参数export_format设置导出的文件格式，但自带的下载按钮样式比较丑，如果你对此有比较高的要求，还是建议结合之前的「上传下载篇」自己设计相关功能：

图5

2.3 冻结首行

通过设置参数fixed_rows={'headers': True}，我们可以实现下滑查看表格的过程中，始终保持表头被冻结：

图6

3 开发一个在线取数工具

在学习完今天的内容之后，我们来结合之前「上传下载篇」中提到的下载功能，来制作一个简单的对指定数据库中的数据表进行快速条件筛选并下载的工具，其中DataTable的derived_virtual_data属性记录了经过排序、条件筛选等操作后当前显示的表格数据：

图7

app4.py

1. import dash 
2. import dash_bootstrap_components as dbc 
3. import dash_core_components as dcc 
4. import dash_html_components as html 
5. import dash_table 
6. from dash.dependencies import Input, Output 
7.  
8. from flask import send_from_directory 
9.  
10. import os 
11. import uuid 
12. from sqlalchemy import create_engine 
13. import pandas as pd 
14.  
15. try: 
16.     os.mkdir("downloads") 
17. except FileExistsError: 
18.     pass 
19.  
20. engine = create_engine('mysql+pymysql://root:mysql@localhost/DASH') 
21.  
22. app = dash.Dash(__name__) 
23.  
24.  
25. @app.server.route('/download/<file>') 
26. def download(file): 
27.     return send_from_directory('downloads', file) 
28.  
29.  
30. app.layout = dbc.Container( 
31.     [ 
32.         dbc.Row( 
33.             [ 
34.                 dbc.Col(dbc.Button('更新数据表', id='refresh-tables', style={'width': '100%'}), width=2), 
35.                 dbc.Col(dcc.Dropdown(id='table-select', style={'width': '100%'}), width=2) 
36.             ] 
37.         ), 
38.         html.Hr(), 
39.         dash_table.DataTable( 
40.             id='dash-table', 
41.             editable=True, 
42.             page_size=15, 
43.             style_header={ 
44.                 'font-family': 'Times New Romer', 
45.                 'font-weight': 'bold', 
46.                 'text-align': 'center' 
47.             }, 
48.             style_data={ 
49.                 'font-family': 'Times New Romer', 
50.                 'text-align': 'center' 
51.             }, 
52.             style_data_conditional=[ 
53.                 { 
54.                     # 对选中状态下的单元格进行自定义样式 
55.                     "if": {"state": "selected"}, 
56.                     "background-color": "#b3e5fc", 
57.                     "border": "none" 
58.                 }, 
59.             ], 
60.             filter_action="native" 
61.         ), 
62.         html.Br(), 
63.         html.A(id='download-url', target="_blank") 
64.     ], 
65.     style={ 
66.         'margin-top': '50px' 
67.     } 
68. ) 
69.  
70.  
71. @app.callback( 
72.     Output('table-select', 'options'), 
73.     Input('refresh-tables', 'n_clicks') 
74. ) 
75. def refresh_tables(n_clicks): 
76.     if n_clicks: 
77.         return [ 
78.             { 
79.                 'label': table, 
80.                 'value': table 
81.             } 
82.             for table in pd.read_sql_query('SHOW TABLES', con=engine)['Tables_in_dash'] 
83.         ] 
84.  
85.     return dash.no_update 
86.  
87.  
88. @app.callback( 
89.     [Output('dash-table', 'data'), 
90.      Output('dash-table', 'columns')], 
91.     Input('table-select', 'value') 
92. ) 
93. def render_dash_table(value): 
94.     if value: 
95.         df = pd.read_sql_table(value, con=engine) 
96.  
97.         return df.to_dict('records'), [ 
98.             {'name': column, 'id': column} 
99.             for column in df.columns 
100.         ] 
101.  
102.     else: 
103.         return [], [] 
104.  
105.  
106. @app.callback( 
107.     [Output("download-url", "href"), 
108.      Output("download-url", "children")], 
109.     [Input("dash-table", "derived_virtual_data"), 
110.      Input("dash-table", "filter_query")], 
111.     prevent_initial_call=True 
112. ) 
113. def download_table(derived_virtual_data, filter_query): 
114.     if derived_virtual_data: 
115.         print(derived_virtual_data) 
116.  
117.         filename = f"output_{uuid.uuid1()}.xlsx" 
118.  
119.         pd.DataFrame(derived_virtual_data).to_excel("downloads/" + filename, index=False) 
120.  
121.         return "/download/" + filename, "下载当前状态表格" 
122.  
123.     return "", "" 
124.  
125.  
126. if __name__ == '__main__': 
127.     app.run_server(debug=True) 

原文地址：https://mp.weixin.qq.com/s?__biz=MzA3ODYwNDkzOQ==&mid=2659064769&idx=1&sn=a83db12eff86231a0faa374f2f92573c&chksm=84ca8425b3bd0d3372c595dc4459fa52ba738ff2f678575aadde22a546d9e67fb4995da7a589&mpshare=1&