如何通过Flask将SQLite表格数据传递至Svelte页面

我用npm create svelte@latest和npm install创建了名为table-test的Svelte项目，初始页面运行正常。之后在routes目录下创建了table文件夹，添加了+page.svelte、Table.svelte和data-store.js文件，当前页面可以从data-store.js导入数据并传递给Table.svelte组件展示。

现在我在table-test中编写了server.py，已经实现连接SQLite数据库并读取表格数据（数据示例：[('Owner', 'This company'), ('User', 'Shopping mall'), ('Client', 'Male adult')]），但不知道如何将这些数据传递到table页面。相关代码如下：

table文件夹下的+page.svelte #

<script>
    import Table from './Table.svelte';
    import { studentsArray, colors } from './data-store.js';
    
    let redStyle = "redTable";
    let blueStyle = "blueTable";
</script>

<Table tableData={$studentsArray} style={redStyle}/>
<Table tableData={$colors} style={blueStyle}/>

table文件夹下的Table.svelte #

<script>
    export let tableData;
    export let style;
</script>

<table class={style}>
    <!-- <thead>
        <tr>
            {#each Object.keys(tableData[0]) as columnHeading}
                <th>{columnHeading}</th>
            {/each}
        <tr/>
    </thead> -->
    <tbody>
        {#each Object.values(tableData) as row}
            <tr>
                {#each Object.values(row) as cell}
                    <td>{cell}</td>
                {/each}
            </tr>
        {/each}
    </tbody>
</table>

<style>
    table, th, td {
        border: 1px solid;
        border-collapse: collapse;
        margin-bottom: 10px;
    }
    
    table.redTable {
        border: 2px solid #A40808;
        background-color: #EEE7DB;
        width: 100%;
        text-align: center;
        border-collapse: collapse;
    }
    table.redTable td, table.redTable th {
        border: 1px solid #AAAAAA;
        padding: 3px 2px;
    }
    table.redTable tbody td {
        font-size: 13px;
    }
    table.redTable tr:nth-child(even) {
        background: #F5C8BF;
    }
    table.redTable thead {
        background: #A40808;
        background: -moz-linear-gradient(top, #bb4646 0%, #ad2020 66%, #A40808 100%);
        background: -webkit-linear-gradient(top, #bb4646 0%, #ad2020 66%, #A40808 100%);
        background: linear-gradient(to bottom, #bb4646 0%, #ad2020 66%, #A40808 100%);
    }
    table.redTable thead th {
        font-size: 19px;
        font-weight: bold;
        color: #FFFFFF;
        text-align: center;
        border-left: 2px solid #A40808;
    }
    table.redTable thead th:first-child {
        border-left: none;
    }

        table.blueTable {
        border: 1px solid #1C6EA4;
        background-color: #EEEEEE;
        width: 100%;
        text-align: left;
        border-collapse: collapse;
    }
    table.blueTable td, table.blueTable th {
        border: 1px solid #AAAAAA;
        padding: 3px 2px;
    }
    table.blueTable tbody td {
        font-size: 13px;
    }
    table.blueTable tr:nth-child(even) {
        background: #D0E4F5;
    }
    table.blueTable thead {
        background: #1C6EA4;
        background: -moz-linear-gradient(top, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
        background: -webkit-linear-gradient(top, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
        background: linear-gradient(to bottom, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
        border-bottom: 2px solid #444444;
    }
    table.blueTable thead th {
        font-size: 15px;
        font-weight: bold;
        color: #FFFFFF;
        border-left: 2px solid #D0E4F5;
    }
    table.blueTable thead th:first-child {
        border-left: none;
    }
</style>

table文件夹下的data-store.js #

import { writable } from 'svelte/store';
    
export let studentsArray = writable([
    {
        "Name" : "Maria",
        "Favorite Subject" : "Math",
        "Age" : 14
    },
    {
        "Name" : "Jose",
        "Favorite Subject" : "Science",
        "Age" : 13
    }
]);

export let colors = writable([
    {
        "Name" : "Cyan",
        "HEX" : "#00FFFF"
    },
    {
        "Name" : "Yellow",
        "HEX" : "#FFFF00"
    },
    {
        "Name" : "Blue",
        "HEX" : "#0000FF"
    },
    {
        "Name" : "Lime",
        "HEX" : "#00FF00"
    },
    {
        "Name" : "Red",
        "HEX" : "#FF0000"
    },
    {
        "Name" : "White",
        "HEX" : "#FFFFFF"
    },
    {
        "Name" : "Black",
        "HEX" : "#000000"
    },
])

server.py代码 #

from flask import Flask, send_from_directory
import sqlite3

from flask import Flask, send_from_directory
import sqlite3

app = Flask(__name__)

# Path for our main Svelte page
@app.route("/")
def base():
    return send_from_directory('/src/routes/table', '+page.svelte')

# Path for all the static files (compiled JS/CSS, etc.)
@app.route("/<path:path>")
def home(path):
    return send_from_directory('/', path)

@app.route("/table")
def show_table():
    database = sqlite3.connect("Toilet.db")
    databasecur = database.cursor()
    selectedtable = 1140
    table_name = "table_" + str(selectedtable)
    sql_check = "SELECT * FROM " + table_name
    databasecur.execute(sql_check)
    check_result = databasecur.fetchall()
    database.close()


if __name__ == "__main__":
    app.run(debug=True)

要实现SQLite数据传递到Svelte页面，需要修改后端接口返回JSON数据，再在前端请求接口并处理数据格式，具体步骤如下：

1. 修改Flask后端接口 #

让/table接口返回包含表头和数据的JSON，同时解决跨域问题：

from flask import Flask, jsonify
import sqlite3
from flask_cors import CORS  # 先安装：pip install flask-cors

app = Flask(__name__)
CORS(app)  # 允许跨域请求

@app.route("/table")
def show_table():
    database = sqlite3.connect("Toilet.db")
    databasecur = database.cursor()
    selectedtable = 1140
    table_name = f"table_{selectedtable}"
    sql_check = f"SELECT * FROM {table_name}"
    databasecur.execute(sql_check)
    
    # 获取表头字段名
    columns = [desc[0] for desc in databasecur.description]
    # 将元组数据转换为组件兼容的字典数组
    data = [dict(zip(columns, row)) for row in databasecur.fetchall()]
    
    database.close()
    return jsonify({"data": data})

if __name__ == "__main__":
    app.run(debug=True, port=5000)

2. 修改Svelte页面请求数据 #

在+page.svelte中请求后端API，获取数据后传递给Table组件：

<script>
    import Table from './Table.svelte';
    import { writable } from 'svelte/store';
    
    let redStyle = "redTable";
    let blueStyle = "blueTable";
    // 存储数据库数据的store
    let dbTableData = writable([]);

    // 页面加载时请求数据
    async function loadDbData() {
        try {
            const res = await fetch('http://localhost:5000/table');
            const result = await res.json();
            dbTableData.set(result.data);
        } catch (err) {
            console.error('数据加载失败:', err);
        }
    }

    loadDbData();
</script>

<!-- 展示数据库数据 -->
<Table tableData={$dbTableData} style={redStyle}/>
<!-- 保留原有示例数据（可选） -->
<!-- <Table tableData={$studentsArray} style={redStyle}/>
<Table tableData={$colors} style={blueStyle}/> -->

3. 可选：恢复表头展示 #

取消Table.svelte中的表头注释，适配数据格式：

<script>
    export let tableData;
    export let style;
</script>

<table class={style}>
    {#if tableData.length > 0}
    <thead>
        <tr>
            {#each Object.keys(tableData[0]) as column}
                <th>{column}</th>
            {/each}
        </tr>
    </thead>
    {/if}
    <tbody>
        {#each tableData as row}
            <tr>
                {#each Object.values(row) as cell}
                    <td>{cell}</td>
                {/each}
            </tr>
        {/each}
    </tbody>
</table>

<!-- 样式部分不变 -->

运行步骤 #

1. 启动Flask后端：python server.py
2. 启动Svelte开发服务器：在table-test目录下执行npm run dev
3. 访问http://localhost:5173/table即可查看数据库数据展示

内容的提问来源于stack exchange，提问作者Zhentao