MySQL read, calculate and write back

Question

I need help with some MySQL commands.

My MySQL table look's like this

    mysql> DESCRIBE sensor_readout;
+-----------+-----------+------+-----+---------------------+-------+
| Field     | Type      | Null | Key | Default             | Extra |
+-----------+-----------+------+-----+---------------------+-------+
| timestamp | timestamp | NO   |     | 0000-00-00 00:00:00 |       |
| sensor_id | char(2)   | NO   | PRI | NULL                |       |
| volume    | int(4)    | NO   | PRI | NULL                |       |
+-----------+-----------+------+-----+---------------------+-------+

My current stored data (it is updating on volume change) is

mysql> SELECT * FROM sensor_readout;
+---------------------+-----------+--------+
| timestamp           | sensor_id | volume |
+---------------------+-----------+--------+
| 2014-04-01 11:27:16 | 22        |   3327 |
| 2014-04-01 12:44:00 | 22        |   3328 |
| 2014-04-01 11:27:13 | 23        |   2643 |
| 2014-04-01 11:54:44 | 23        |   2644 |
| 2014-04-01 11:27:14 | 24        |   3407 |
| 2014-04-01 11:55:03 | 24        |   3408 |
+---------------------+-----------+--------+

What I would like to do is that whenever the script runs (I guess python but any other script suggestion is welcome) the script would take last volume record (by timestamp) on sensors id 22, 23 and 24. Perform mathematical calculation on 22 and 23 volume.

And than write back the data to table "poraba". 22+23 is representing "voda_mrzla". 24 would be voda_topla. With the date and month of entry to the database (don't need time).

mysql> DESCRIBE poraba;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| mesec      | date       | NO   |     | NULL    |       |
| voda_mrzla | varchar(5) | NO   |     | NULL    |       |
| voda_topla | varchar(5) | NO   |     | NULL    |       |
+------------+------------+------+-----+---------+-------+

For now I tried with the following python script (see below). But I am stuck there. Only thing I can do is create readout from last value on each sensor_id

import MySQLdb

#establish connection to MySQL. You'll have to change this for your database.
conn = MySQLdb.connect('localhost', '*****', '****', 'moteino') 

cursor=conn.cursor()



sql = """SELECT sensor_id, volume 
         FROM sensor_readout 
         WHERE sensor_id = 22  
         ORDER BY timestamp DESC LIMIT 1"""

sql1 = """SELECT sensor_id, volume 
         FROM sensor_readout 
         WHERE sensor_id = 23 
         ORDER BY timestamp DESC LIMIT 1"""

sql2 = """SELECT sensor_id, volume 
         FROM sensor_readout 
         WHERE sensor_id = 24 
         ORDER BY timestamp DESC LIMIT 1"""

cursor.execute(sql1)
data1=cursor.fetchall()

cursor.execute(sql2)
data2=cursor.fetchall()

cursor.execute(sql)
data=cursor.fetchall()


print(data, data1, data2)

The print output is like this

((('22', 3331L),), (('23', 2647L),), (('24', 3412L),))

UPDATE!!!!

Sweet guys the following code worked just as I wanted. I did change timestamp with time_recorded so minimize the timestamp confusion.

    -- Direct insert into DDBB
INSERT INTO poraba ( voda_mrzla, voda_topla )

  -- Get the values, with the right operations
  SELECT
    SUM( IF(sensor_id = 22 OR sensor_id = 23, volume, 0 ) ) voda_mrzla
  , SUM( IF ( sensor_id = 24, volume, 0 ) ) voda_topla
  FROM ( 
  -- Get only the values with max timestamp for each sensor
  SELECT s.`time_recorded`, s.sensor_id, s.volume
  FROM sensor_readout s
  INNER JOIN (
      SELECT sensor_id, MAX(`time_recorded`) mts
      FROM sensor_readout
      GROUP BY sensor_id
  ) maxS
  ON ( s.`time_recorded` = maxS.mts AND s.sensor_id = maxS.sensor_id )
  ) maxTime

I do have another favor to ask. The last step I would like to achieve is now for the script to take value from previous and current month and creates minus calculation. This will than represent the one month of used cold and hot water. Sorry for eng/slo wording mash-up (cold is voda_mrzla and hot is voda_topla)

       mysql> DESCRIBE usage_per_month;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| difference_cold | varchar(10) | NO   |     | NULL    |       |
| difference_hot  | varchar(10) | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

Mybe this table would be easier to select previous value

mysql> SELECT * FROM poraba;
+---------------------+------------+------------+-----------+
| mesec               | voda_mrzla | voda_topla | id_poraba |
+---------------------+------------+------------+-----------+
| 2014-03-03 16:19:08 | 5985       | 3417       |         1 |
| 2014-04-03 20:57:51 | 5978       | 3412       |         2 |

score 0 · Accepted Answer · edited May 23 '17 at 12:20

Use pure MySQL query, is going to be easier and faster: Try UPDATE with SELECT.

MySQL - UPDATE query based on SELECT Query

EDIT

I misread your question, in your case you should use INSERT with SELECT, :P

INSERT with SELECT

Ok, I had more time to enjoy this, XD:

-- Direct insert into DDBB
INSERT INTO poraba ( voda_mrzla, voda_topla )

  -- Get the values, with the right operations
  SELECT
    SUM( IF(sensor_id = 22 OR sensor_id = 23, volume, 0 ) ) voda_mrzla
  , SUM( IF ( sensor_id = 24, volume, 0 ) ) voda_topla
  FROM ( 
  -- Get only the values with max timestamp for each sensor
  SELECT s.`timestamp`, s.sensor_id, s.volume
  FROM sensor_readout s
  INNER JOIN (
      SELECT sensor_id, MAX(`timestamp`) mts
      FROM sensor_readout
      GROUP BY sensor_id
  ) maxS
  ON ( s.`timestamp` = maxS.mts AND s.sensor_id = maxS.sensor_id )
  ) maxTime

You may check the result of the select in this sqlfiddle: http://sqlfiddle.com/#!2/67e65/1

There are some table structure differences with your tables, to allow us insert timestamp easily: I set default timestamp CURRENT_TIMESTAMP, so any added row will get the current time. As you may see, with this query you can insert directly from the databases with no more script calculation. The sum and if are for getting only the right values from the three rows we get from the SELECT max values query (Check more info about this problem in SQL Select only rows with Max Value on a Column).

After that, your script only have to run this query and you'll have your fresh data in the table, ready to be picked, XD. Sorry about not writing the whole code in your Pyton script, I'm from PHP and don't want to mess it.

score 0 · Answer 2 · answered Apr 02 '14 at 12:34

Try this query in whatever script you like:

INSERT INTO poraba
SET voda_mrzla = (
    SELECT volume 
    FROM sensor_readout 
    WHERE sensor_id = 22  
    ORDER BY `timestamp` DESC LIMIT 1
) + (
    SELECT volume 
    FROM sensor_readout 
    WHERE sensor_id = 23  
    ORDER BY `timestamp` DESC LIMIT 1
),
voda_topla = (
    SELECT volume 
    FROM sensor_readout 
    WHERE sensor_id = 24
    ORDER BY `timestamp` DESC LIMIT 1
),
`date` = (
    SELECT `timestamp`
    FROM sensor_readout 
    ORDER BY `timestamp` DESC LIMIT 1
)

MySQL read, calculate and write back

2 Answers2

EDIT