如何将数组插入数据库？ [英] How to insert arrays into a database?

问题描述

在我之前的问题，很多用户希望我给更多的数据玩具。所以我有工作出口我的所有数据和处理它与Python，但后来我意识到：我在哪里留下所有这些数据？

In my previous question a lot of users wanted me to give some more data to toy with. So I got working on exporting all my data and processing it with Python, but then I realized: where do I leave all this data?

我决定把最好的东西放在数据库中，所以至少我没有 to解析每个原始文件。但是，由于我对数据库一无所知，这是令人困惑的。我尝试了一些教程来创建一个sqlite数据库，添加一个表和字段并尝试插入我的numpy.arrays，但它不能使它工作。

Well I decided the best thing would be to stick them in a database, so at least I don't have to parse the raw files every time. But since I know nothing about databases this is turning out to be quite confusing. I tried some tutorials to create a sqlite database, add a table and field and try to insert my numpy.arrays, but it can't get it to work.

通常情况下，每个狗的结果如下所示：

Typically my results per dog look like this:

所以我有35只不同的狗，每只狗有24个测量。每个测量本身具有未知的触点数量。每个测量由3D阵列（整个板的248帧[255x63]）和2D阵列（板的每个传感器的最大值[255x63]组成）组成。在数据库中存储一个值不是一个问题，但是将2D数组放在那里似乎没有起作用。

So I have 35 different dogs and each dog has 24 measurement. Each measurement itself has an unknown amount of contacts. Each measurement consists out of a 3D array (248 frames of the whole plate [255x63]) and a 2D array (the maximal values for each sensor of the plate [255x63]). Storing one value in a database wasn't a problem, but getting my 2D arrays in there didn't seem to work.

所以我的问题是我该如何订购在数据库中并将数组插入到其中？

So my question is how should I order this in a database and insert my arrays into it?

推荐答案

您可能想要开始使用狗表包含每只狗的所有平面（非数组）数据，每只狗拥有一个的东西，如姓名，性别和年龄： / p>

You'll probably want to start out with a dogs table containing all the flat (non array) data for each dog, things which each dog has one of, like a name, a sex, and an age:

CREATE TABLE `dogs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(64),
  `age` INT UNSIGNED,
  `sex` ENUM('Male','Female')
);

从那里，每只狗有很多测量，所以你需要一个 dog_mesaurements 表存储24个度量：

From there, each dog "has many" measurements, so you need a dog_mesaurements table to store the 24 measurements:

CREATE TABLE `dog_measurements` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `dog_id` INT UNSIGNED NOT NULL,
  `paw` ENUM ('Front Left','Front Right','Rear Left','Rear Right'),
  `taken_at` DATETIME NOT NULL
);

然后每当你进行测量时，你 INSERT INTO dog_measurements（dog_id，taken_at ）VALUES（*？*，NOW（））; 其中* ？ *是从狗的狗的ID

Then whenever you take a measurement, you INSERT INTO dog_measurements (dog_id,taken_at) VALUES (*?*, NOW()); where * ? * is the dog's ID from the dogs table.

然后，您需要表格来存储每个度量的实际框架，如：

You'll then want tables to store the actual frames for each measurement, something like:

CREATE TABLE `dog_measurement_data` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `dog_measurement_id` INT UNSIGNED NOT NULL,
  `frame` INT UNSIGNED,
  `sensor_row` INT UNSIGNED,
  `sensor_col` INT UNSIGNED,
  `value` NUMBER
);

这样，对于250帧中的每一个，您循环遍历63个传感器中的每一个，并存储该传感器的值与帧号进入数据库：

That way, for each of the 250 frames, you loop through each of the 63 sensors, and store the value for that sensor with the frame number into the database:

INSERT INTO `dog_measurement_data` (`dog_measurement_id`,`frame`,`sensor_row`,`sensor_col`,`value`) VALUES
(*measurement_id?*, *frame_number?*, *sensor_row?*, *sensor_col?*, *value?*)

显然替换了测量ID？， frame_number？，sensor_number？ ，具有实际价值： - ）

Obviously replace measurement_id?, frame_number?, sensor_number?, value? with real values :-)

所以基本上每个 dog_measurement_data 是给定帧的单个传感器值。这样，要获取所有给定帧的所有传感器值，您将：

So basically, each dog_measurement_data is a single sensor value for a given frame. That way, to get all the sensor values for all a given frame, you would:

SELECT `sensor_row`,sensor_col`,`value` FROM `dog_measurement_data`
WHERE `dog_measurement_id`=*some measurement id* AND `frame`=*some frame number*
ORDER BY `sensor_row`,`sensor_col`

这将为您提供该框架的所有行和列。

And this will give you all the rows and cols for that frame.

这篇关于如何将数组插入数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！