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

Question

我有一个小数据库，如下面的代码片段中所定义的。我想查询它以获取所有信息，并通过Flask API通过JSON文件将其发送到vue应用程序。目前我使用的查询是

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"}

我想把它放进一个JSON文件中，这样它就被排列成：

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

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

提前感谢您，Martyn

这是我的表格结构：

-- 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

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

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

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

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

以下是示例php代码

<?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);

?>

Answer 2

如果您的sqlite版本是使用JSON1扩展编译的，则启用以下内容：

SELECT json_group_array(json_object('name', tbl_room.name,
'id', tbl_room.room_id,
'devices', json_array()))
FROM tbl_room
GROUP BY tbl_room.name, tbl_room.room_id;