0

AS a beginner on Python and SQLite I am wandering if there are more elegant solutions for the tasks I have.

My current SQLite database contains several time series as:

CREATE TABLE Y1 (time REAL, value REAL);
CREATE TABLE Y2 (time REAL, value REAL);
CREATE TABLE Y3 (time REAL, value REAL);
...

The time values are not identical for all variables, however there might be dublicates.

However, since the list of the variables is increasing, I want to transfer the data to a sturcture like. The Table 'VARLIST' should collect all variables.

CREATE TABLE VARLIST (VarID INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL);
INSERT INTO VARLIST VALUES(1, 'Y1');
INSERT INTO VARLIST VALUES(2, 'Y2');

...

The datapoints itself shall be transfered to the Tables, TIMESTAMPS and DATAPOINTS. The timestamps are in an sperate tabele since I want to associate additional information to this stamps (not shown in this siplified example).

CREATE TABLE TIMESTAMPS (timeID INTEGER PRIMARY KEY, time REAL);
CREATE TABLE DATAPOINTS (DataID INTEGER PRIMARY KEY, timeID INTEGER, VarID INTEGER, value REAL);

My question is: How can I best transfer the data from the tables Y1.. Y6 in the Tables TIMESTAMPS and DATAPOINTS? Of course I can read out all the data to Python and let Python do the regrouping. Anyhow I want to know if this would be possible also by using only SQLite commands.

Cœur
  • 37,241
  • 25
  • 195
  • 267
BerndGit
  • 1,530
  • 3
  • 18
  • 47

1 Answers1

1

The timestamps can simply be copied:

INSERT INTO TimeStamps(time)
SELECT time FROM Y1 UNION
SELECT time FROM Y2 UNION
SELECT time FROM Y3 UNION
SELECT time FROM Y4 UNION
SELECT time FROM Y5 UNION
SELECT time FROM Y6;

The datapoints require a lookup (using a correlated subquery) for the corresponding timestamp ID:

INSERT INTO DataPoints(timeID, VarID, value)
SELECT (SELECT timeID
        FROM TimeStamps
        WHERE time = all_Y.time),
       VarID,
       value
FROM (SELECT time, 1 AS VarID, value FROM Y1 UNION ALL
      SELECT time, 2,          value FROM Y2 UNION ALL
      SELECT time, 3,          value FROM Y3 UNION ALL
      SELECT time, 4,          value FROM Y4 UNION ALL
      SELECT time, 5,          value FROM Y5 UNION ALL
      SELECT time, 6,          value FROM Y6) AS all_Y;
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thank you for your fast Response. As I suspected there is an elegant way to do this. Please note that I have edited the definition of `DATAPOINTS` (`VarID` was missing). Kindly add this to your answer. – BerndGit Jan 03 '16 at 20:47
  • I expect I have to do it now for each variable individually as `INSERT INTO DataPoints(timeID,VarID, value) SELECT (SELECT timeID FROM TimeStamps WHERE time = Y.time),1, value FROM (SELECT time, value FROM Y1) AS Y` Correct? – BerndGit Jan 03 '16 at 20:58