如何将数组插入到数据库中? [英] 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?

好吧，我决定最好的办法是将它们保存在数据库中，所以至少我没有 每次解析原始文件.但由于我对数据库一无所知，结果证明这很令人困惑.我尝试了一些教程来创建一个 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 阵列(整个板 [255x63] 的 248 帧)和一个 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?

推荐答案

您可能想要从 dogs 表开始，该表包含每条狗的所有平面(非数组)数据，事物每只狗都有一个，比如名字、性别和年龄:

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()); 其中 * ? * 是dogs 表中的狗 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?*)

显然用真实值替换measurement_id?、frame_number?、sensor_number?、value? :-)

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