明白了，你希望将项目分为三个独立的文件： 1. **连接数据库的 Python 文件** (`database_connection.py`) 2. **创建 H5 页面的 Dash 应用文件** (`h5_dashboard.py`) 3. **主体代码文件** (`main.py`) 这种结构有助于代码的模块化和可维护性。下面是如何实现这种结构的具体步骤和代码示例。 ### 1. 连接数据库的 Python 文件 (`database_connection.py`) 这个文件负责从 MySQL 数据库中读取数据并返回一个 Pandas DataFrame。 ```python # database_connection.py import pandas as pd from sqlalchemy import create_engine def connect_to_database(): """ Connect to the MySQL database and return a DataFrame with the sellers dataset. """ try: engine = create_engine('mysql+pymysql://root:111111@localhost:3306/数据可视化') query = "SELECT * FROM olist_sellers_dataset" df = pd.read_sql(query, engine) print("Data imported successfully.") return df except Exception as e: print(f"Error connecting to the database or reading data: {e}") return None if __name__ == '__main__': df = connect_to_database() if df is not None: print(df.head()) ``` ### 2. 创建 H5 页面的 Dash 应用文件 (`h5_dashboard.py`) 这个文件负责创建和配置 Dash 应用，并定义图表的布局和交互逻辑。 ```python # h5_dashboard.py import dash from dash import dcc, html, Input, Output import dash_bootstrap_components as dbc import plotly.express as px import plotly.graph_objects as go # Initialize Dash application app = dash.Dash(__name__, external_stylesheets=[dbc.themes.BOOTSTRAP]) # Define layout app.layout = dbc.Container([ dbc.Row([ dbc.Col(html.H1("Olist Sellers Data Dashboard", style={'textAlign': 'center'}), width=12) ]), dbc.Row([ dbc.Col([ dcc.Dropdown( id='state-dropdown', options=[], # This will be populated dynamically value='all', multi=False, clearable=False, style={'width': '100%'} ) ], width=4) ], style={'padding': '20px'}), dbc.Row([ dbc.Col(dcc.Graph(id='graph1'), width=6), dbc.Col(dcc.Graph(id='graph3'), width=6), ]), dbc.Row([ dbc.Col(dcc.Graph(id='graph4'), width=6), dbc.Col(dcc.Graph(id='graph5'), width=6), ]), dbc.Row([ dbc.Col(dcc.Graph(id='graph6'), width=12), ]), ], fluid=True) # Define callback to update dropdown options and graphs @app.callback( [ Output('state-dropdown', 'options'), Output('graph1', 'figure'), Output('graph3', 'figure'), Output('graph4', 'figure'), Output('graph5', 'figure'), Output('graph6', 'figure') ], [ Input('state-dropdown', 'value') ], [ State('state-dropdown', 'options') ] ) def update_dropdown_and_graphs(selected_state, state_options): global df # Update dropdown options only once if not state_options: states_options = [{'label': state, 'value': state} for state in sorted(df['seller_state'].unique())] states_options.insert(0, {'label': 'All States', 'value': 'all'}) else: states_options = state_options # Filter data based on selected state if selected_state == 'all': filtered_df = df else: filtered_df = df[df['seller_state'] == selected_state] # Chart 1: Number of Sellers by State (Histogram) fig1 = px.histogram(filtered_df, x='seller_state', title='Number of Sellers by State', category_orders=dict(seller_state=filtered_df['seller_state'].value_counts().index)) # Chart 3: Top 10 Cities with Most Sellers (Bar Chart) top_cities = filtered_df['seller_city'].value_counts().head(10).reset_index() top_cities.columns = ['seller_city', 'count'] fig3 = px.bar(top_cities, x='seller_city', y='count', title='Top 10 Cities with Most Sellers', text_auto=True) fig3.update_xaxes(tickangle=45) # Chart 4: Distribution of Seller Zip Code Prefixes (Histogram) fig4 = px.histogram(filtered_df, x='seller_zip_code_prefix', nbins=50, title='Distribution of Seller Zip Code Prefixes') # Chart 5: Seller City-State Relationship (Heatmap) city_state_df = filtered_df.groupby(['seller_city', 'seller_state']).size().unstack().fillna(0) fig5 = px.imshow(city_state_df, labels=dict(x="State", y="City", color="Count"), title='Seller City-State Relationship', color_continuous_scale='YlGnBu') # Chart 6: Scatter Plot of Seller Zip Code Prefix vs City fig6 = px.scatter(filtered_df, x='seller_zip_code_prefix', y='seller_city', title='Scatter Plot of Seller Zip Code Prefix vs City') fig6.update_traces(marker=dict(size=8, opacity=0.6), selector=dict(mode='markers')) return states_options, fig1, fig3, fig4, fig5, fig6 if __name__ == '__main__': app.run(debug=True, use_reloader=False) ``` ### 3. 主体代码文件 (`main.py`) 这个文件负责协调其他两个文件的工作流，包括初始化数据库连接和启动 Dash 应用。 ```python # main.py import database_connection import h5_dashboard if __name__ == '__main__': # Load data from the database df = database_connection.connect_to_database() if df is None: print("Failed to load data. Exiting...") exit(1) # Make the DataFrame accessible to the Dash app h5_dashboard.df = df # Run the Dash application h5_dashboard.app.run(debug=True, use_reloader=False) ``` ### 项目结构 ``` project_directory/ │ ├── database_connection.py ├── h5_dashboard.py └── main.py ``` ### 运行项目 1. **安装依赖**： 确保你已经安装了必要的库，可以使用以下命令安装： ```bash pip install pandas sqlalchemy matplotlib seaborn dash dash-bootstrap-components plotly pymysql ``` 2. **运行项目**： 在命令行中导航到项目目录并运行 `main.py`： ```bash python main.py ``` ### 详细解释 #### 1. **数据库连接文件 (`database_connection.py`)** - **connect_to_database 函数**：负责连接到 MySQL 数据库并读取数据集。 - **全局变量 df**：用于存储从数据库读取的数据。 #### 2. **H5 页面文件 (`h5_dashboard.py`)** - **Dash 应用初始化**：定义 Dash 应用的布局和回调函数。 - **回调函数**：根据用户选择的州动态更新图表。 - **全局变量 df**：通过 `main.py` 传递数据集。 #### 3. **主体代码文件 (`main.py`)** - **协调工作流**：加载数据并启动 Dash 应用。 - **全局变量传递**：通过全局变量将数据集传递给 `h5_dashboard.py`。 这种结构不仅使代码更模块化，而且提高了可维护性和可扩展性。如果有任何其他需求或需要进一步优化，请随时告知！ 😊