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

How to use the database query 1 second to find the needed data, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1 introduction

Learn the common methods of rendering static tables on web pages in Dash, and teach you how to cooperate with Dash in the final example, simply write a database query application ~ and specially hide a surprise at the end of the article!

2 render static tables in Dash

There are many ways to render "static" tables in Dash, and the method we are going to learn today is to cooperate with the third-party expansion of Table () parts in dash_bootstrap_components introduced in the previous article, and use the features of bootstrap to quickly create beautiful "static" tables:

2.1 composition of static tables

To learn how to render a static table in the front end based on Dash, we first need to learn its element composition. Dash continues the concepts related to table tags in html, and consists of six parts: Table (), Thead (), Tbody (), Tr (), Th (), and Td () to form a complete table. Start with a simple example:

❝app1.py ❞

Import dash import dash_html_components as html import dash_bootstrap_components as dbc app = dash.Dash (_ _ name__) app.layout = html.Div (dbc.Container (dbc.Table) [html.Thead (html.Tr ([html.Th ('first column')) Html.Th ('second column'),])) Html.Tbody ([html.Tr ([html.Td ('one row, one column'), html.Td ('one row, two columns') ), html.Tr ([html.Td ('two rows and one column'), html.Td ('two rows and two columns') ]) Style= {'margin-top':' 50px' # sets the height of the top white space})) if _ _ name__ = ='_ main__': app.run_server (debug=True)

Note that the Table () widget we use here is from dash_bootstrap_components, while the rest of the table components are from the Dash native dash_html_ components library, which serve the following purposes:

"Table ()"

Table () is the outermost part of a static table, and Table () in dash_bootstrap_components is chosen because it comes with many practical parameters, commonly used as follows:

"bordered": Bool type, used to set whether to "keep" the outer border of the table

"borderless": Bool type, which is used to set whether to "delete" the grid lines inside the table.

"striped": Bool type, which is used to set whether the zebra shading scheme is applied to numeric rows, that is, adjacent rows have different background colors

"dark": Bool type, used to set whether or not to apply the "dark" theme

"hover": Bool. When set to True, hovering the mouse over a row will have a corresponding effect.

With the above parameters, we can change the overall effect of the static table. For example, the app1.py effect after setting dark=True is as follows:

"Thead () and Tbody ()"

The child elements Thead () and Tbody () are required at the level below the component Table (), which are used to store header information and table value content information, respectively.

"Tr (), Th () and Td ()"

After the previous process of Table () nesting Thead () and Tbody (), we can begin to formally organize the data content in the "header area" and the "value area", respectively.

Since it is a table, the content should be organized in a grid way that comes first and then columns. The Tr () widget acts as a row container, and its nested child elements are elements at the location of each cell in the table.

Within the Tr () nested within Thead (), you need to use Th () to set the field name of each column, while inside the Tr () nested within Tbody (), both Td () and Th () can be used to set the numeric content of each cell, except that Th () has a bold effect when representing cell values:

❝app2.py ❞

Import dash import dash_html_components as html import dash_bootstrap_components as dbc app = dash.Dash (_ _ name__) app.layout = html.Div (dbc.Container (dbc.Table ([html.Thead (html.Tr) [html.Th ('Field 1')) Html.Th ('Field 2')])) Html.Tbody ([html.Tr ([html.Th ('1')) Html.Td ('test')]), html.Tr ([html.Th (' 2')) Html.Td ('test')]), html.Tr ([html.Td (' 3')) Html.Td ('test')])]), striped=True) Style= {'margin-top':' 50px' # sets the height of the top white space})) if _ _ name__ = ='_ main__': app.run_server (debug=True)

Th () and Td () both have additional parameters colSpan and rowSpan, which can be passed in integers to achieve the effect of "merging cells" horizontally or vertically, such as the following example:

❝app3.py ❞

Import dash import dash_html_components as html import dash_bootstrap_components as dbc app = dash.Dash (_ _ name__) app.layout = html.Div (dbc.Container (dbc.Table ([html.Thead (html.Tr) [html.Th ('Field 1')) Html.Th ('Field 2'), html.Th (' Field 3'), html.Th ('Field 4'),])) Html.Tbody ([html.Tr ([html.Th ('1'), # style sets horizontal center html.Td ('colSpan=2', colSpan=2) Style= {'text-align':' center'}), html.Td ('test'),]), html.Tr ([html.Th (' 2')) Html.Td ('test'), # style sets the vertical center html.Td (' rowSpan=2', rowSpan=2, style= {'vertical-align':' middle'}) Html.Td ('test')]), html.Tr ([html.Th (' 3')) Html.Td ('test'), html.Td (' test')])]], striped=True, bordered=True) Style= {'margin-top':' 50px' # sets the height of the top white space})) if _ _ name__ = ='_ main__': app.run_server (debug=True)

2.2 Quick Table rendering 2.2.1 Fast rendering of static tables using list derivation

Through the previous content, we know that if you render a static table with a style in Dash, but in the daily requirements, in the face of batch data, it is of course impossible for us to manually write the corresponding code for the whole table, for a large number of tables, we can cooperate with the list derivation commonly used in Python to achieve.

For example, the following example:

❝app4.py ❞

Import dash import dash_html_components as html import dash_bootstrap_components as dbc import pandas as pd import numpy as np fake_df = pd.DataFrame (np.random.rand (1000) .reshape (200,5)) fake_df.rename (lambda s: F 'field {s}', axis=1 Inplace=True) # batch format column name app = dash.Dash (_ _ name__) app.layout = html.Div (dbc.Container (dbc.Table ([html.Thead (html.Tr) [html.Th ('row subscript') Style= {'text-align':' center'})] + [html.Th (column, style= {'text-align':' center'}) for column in fake_df.columns])) Html.Tbody ([html.Tr ([html.Th (fags # {idx}', style= {'text-align':' center'})] + [html.Td [column] Style= {'text-align':' center'}) for column in fake_df.columns]) for idx, row in fake_df.iterrows ()])], striped=True Bordered=True), style= {'margin-top':' 50px' # set the height of the top white space})) if _ _ name__ = ='_ main__': app.run_server (debug=True)

Applying list derivation when generating headers and each row of content makes our code more concise.

2.2.2 Fast rendering of tables using from_dataframe ()

Although the above list derivation is much simpler, dash_bootstrap_components also provides the Table.from_dataframe () method, which can be passed directly into the pandas data box to quickly make a simple static table.

Its style-related parameters are the same as dbc.Table (), but the disadvantage is that the freedom of the element style within the custom table is not as high as the previous list derivation:

❝app5.py ❞

Import dash import dash_html_components as html import dash_bootstrap_components as dbc import pandas as pd import numpy as np fake_df = pd.DataFrame (np.random.rand (1000) .reshape (200,5)) fake_df.rename (lambda s: F 'field {s}', axis=1 Inplace=True) # batch format column name app = dash.Dash (_ _ name__) app.layout = html.Div (dbc.Container (# line code rendering static table dbc.Table.from_dataframe (fake_df, striped=True) Style= {'margin-top':' 50px' # sets the height of the top white space})) if _ _ name__ = ='_ main__': app.run_server (debug=True)

3 self-made and simple database query system

After learning today's content, we can create a lot of web applications with tables as the main content, such as database query system, we take Postgresql as an example, with the relevant functions of pandas and sqlalchemy, to quickly create a simple database query system.

First import all the data tables in the attachment of this issue into the target database using the following code:

Then only need to cooperate with Dash, just a few dozen lines of code can be achieved.

The corresponding code is as follows:

❝app6.py ❞

Import dash import dash_html_components as html import dash_bootstrap_components as dbc import dash_core_components as dcc from dash.dependencies import Input, Output State import pandas as pd from sqlalchemy import create_engine postgres_url = 'postgresql://postgres: fill in your password @ localhost:5432/Dash' engine = create_engine (postgres_url) app = dash.Dash (_ _ name__) app.layout = html.Div ([dbc.Row ([dbc.Col (dbc.Button) (' update database information') Id='refresh-db', style= {'width':' 100%'}), width=2), dbc.Col (id='db-table-names', placeholder=' select database data table', style= {'width':' 100%'}), width=4), dbc.Col (dbc.Button ('query', id='query', style= {'width':' 100%'})) Width=1)]), html.Hr (), dbc.Row ([dbc.Col (id='query-result')])] Style= {'margin-top':' 50px' # sets the height of the top white space}) @ app.callback (Output ('db-table-names',' options'), Input ('refresh-db',' n blank clicks') Prevent_initial_call=True) def query_data_records (n_clicks): # extract the target table and query its first 500 rows of records table_names = pd.read_sql_query ("select tablename from pg_tables where schemaname='public'", con=engine) return [{'label': name,' value': name} for name in table_names ['tablename']] @ app.callback (Output (' query-result', 'children') Input ('query',' nasty clicks'), State ('db-table-names',' value'), prevent_initial_call=True) def refresh_table_names (n_clicks, value): if value: query_result = pd.read_sql_query (f'select * from {value} limit 500 layers, con=engine) return html.Div (dbc.Table.from_dataframe (query_result, striped=True), style= {'height':' 600px' 'overflow':' auto'}) else: return dash.no_update if _ _ name__ = ='_ main__': app.run_server (debug=True) finish reading the above Do you know how to use a database query for 1 second to find the data you need? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.