将SQL查询的结果格式化为JSON

Question

SELECT tbl_room.room, tbl_room.room_id, tbl_device.name, tbl_display.display, tbl_function.function, tbl_device.format
    FROM tbl_device
    INNER JOIN tbl_room ON tbl_room.id = tbl_device.room_id
    INNER JOIN tbl_display ON tbl_display.id = tbl_device.display_id
    INNER JOIN tbl_function ON tbl_function.id = tbl_device.function_id
    ORDER BY tbl_room.room_id;

Bedroom (Main)  bedroom_main    bme280/1    gauge   temperature {"min": 0, "max": 50, "dp": 1, "units": "°C"}
Bedroom (Main)  bedroom_main    bme280/1    gauge   humidity    {"min": 0, "max": 100, "dp": 1, "units": "%"}
Bedroom (Main)  bedroom_main    bme280/1    gauge   pressure    {"min": 0, "max": 1100, "dp": 1, "units": "hPa"}
Front Room  front_room  ds18b20/heater  gauge   temperature {"min": 0, "max": 50, "dp": 1, "units": "°C"}

[
    { "name": "Office",
      "id": "office",
      "devices": []
    },
    { "name": "Front Room", 
       "id": "front_room",
       "devices": []
    }
  }
]

这可以在单个sql查询中完成吗？或者我必须在循环中对每个房间进行查询吗？还是在一次查询中转储整个数据集，然后在pyhton中处理它更有效率？这是一个很小的数据集，但我有兴趣知道哪一个是最有效的方法。

先谢谢你，马丁

下面是我的表结构：

-- Table: tbl_device
CREATE TABLE tbl_device (
    name        VARCHAR NOT NULL ON CONFLICT ROLLBACK,
    room_id     INTEGER CONSTRAINT fk_room REFERENCES tbl_room (id) 
                        NOT NULL,
    function_id INTEGER CONSTRAINT fk_function REFERENCES tbl_function (id) 
                        NOT NULL ON CONFLICT ROLLBACK,
    display_id  INTEGER CONSTRAINT fk_display REFERENCES tbl_display (id) 
                        NOT NULL ON CONFLICT ROLLBACK,
    format      VARCHAR NOT NULL ON CONFLICT ROLLBACK
                        DEFAULT [default],
    UNIQUE (
        name,
        room_id,
        function_id,
        display_id
    )
    ON CONFLICT ROLLBACK
);


-- Table: tbl_display
CREATE TABLE tbl_display (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    display VARCHAR NOT NULL ON CONFLICT ROLLBACK
                    UNIQUE ON CONFLICT ROLLBACK
);


-- Table: tbl_function
CREATE TABLE tbl_function (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    function VARCHAR NOT NULL ON CONFLICT ROLLBACK
                     UNIQUE ON CONFLICT ROLLBACK,
    control  BOOLEAN NOT NULL
                     DEFAULT (0) 
);


-- Table: tbl_room
CREATE TABLE tbl_room (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    room_id VARCHAR NOT NULL
                    UNIQUE ON CONFLICT ROLLBACK,
    room    VARCHAR NOT NULL ON CONFLICT ROLLBACK
);

Answer 1

首先，没有办法直接将JSON响应从MySQL数据库馈送到VueJS或任何其他App。VueJS应用程序是应用程序的前端。您必须创建一个连接到MySQL数据库的后端，从MySQL数据库获取必要的数据，将其转换为JSON并发送到Vue应用程序。

为了开发后端，您可以使用PHP、Python、Java、NodeJS等语言。

如果您可以继续使用PHP，那么获取数据并转换为JSON是非常容易的。

但如果您仍然需要继续使用Python，则必须使用Flask或任何其他Python web框架来完成此操作。

<?php

$dbhost = 'hostname';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'database';

  $db = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

  if ($db->connect_errno) {
     printf("Failed to connect to database");
     exit();
  }

  $result = $db->query("SELECT * FROM "); // Your SQL query

  $data = array();

  while ( $row = $result->fetch_assoc())  {
    $data[]=$row;
  }


 echo json_encode($data);
 
?>