How to insert arrays into a database?

Question

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?

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:

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?

Answer 1

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

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

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

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

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

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.