python : mysql : Return 0 when no rows found

Question

Table structure - Data present for 5 min. slots -

data_point | point_date

12 | 00:00

14 | 00:05

23 | 00:10

10 | 00:15

43 | 00:25

10 | 00:40

When I run the query for say 30 mins. and if data is present I'll get 6 rows (one row for each 5 min. stamp). Simple Query -

select data_point from some_table where point_date >= start_date AND point_date < end_date order by point_date

Now when I don't have an entry for a particular time slot (e.g. time slot 00:20 is missing), I want the "data_point" to be returned as 0

The REPLACE, IF, IFNULL, ISNULL don't work when there no rows returned.

I thought Union with a default value would work, but it failed too or maybe I didn't use it correctly.

Is there a way to get this done via sql only ?

Note : Python 2.6 & mysql version 5.1

geertjanvdk · Accepted Answer · 2010-01-23 14:14:17Z

Yes, you can do that using SQL only. A solution would be to use a Stored Routine. The bellow Stored Procedure produces following output:

start cnt 00:05:00 1 00:10:00 0 00:15:00 1 00:20:00 0 00:25:00 1 00:30:00 0 00:35:00 1 00:40:00 0 00:45:00 0 00:50:00 0 00:55:00 2

The table I used:

CREATE TABLE `timedata` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` datetime DEFAULT NULL, `c2` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) )

Here the Stored Procedure (adjust for your environment):

DROP PROCEDURE IF EXISTS per5min; DELIMITER // CREATE PROCEDURE per5min () BEGIN DECLARE dtMin DATETIME; DECLARE dtMax DATETIME; DECLARE dtStart DATETIME; DECLARE dtStop DATETIME; DECLARE tmDiff TIME; DECLARE result INT UNSIGNED; SET @offset = 5 * 60; SELECT MIN(c1) into dtMin FROM timedata; SELECT MAX(c1) into dtMax FROM timedata; CREATE TEMPORARY TABLE tmp_per5min ( start TIME, cnt INT UNSIGNED ); SET dtStart = dtMin; REPEAT SELECT dtStart + INTERVAL @offset SECOND into dtStop; SELECT count(c2) into result FROM timedata WHERE c1 BETWEEN dtStart and dtStop; SELECT TIME(SUBTIME(dtStop,TIME(dtMin))) into tmDiff; INSERT INTO tmp_per5min (start,cnt) VALUES (tmDiff,result); SET dtStart = dtStop; UNTIL dtStop >= dtMax END REPEAT; SELECT * FROM tmp_per5min; DROP TABLE tmp_per5min; END; // DELIMITER ; CALL per5min();

If you save the above into a file called 'per5minproc.sql', you can load it like this:

shell> mysql -uroot test < per5minproc.sql

In Python using MySQLdb (I didn't get this working in MySQL Connector/Python, me ashamed!):

import MySQLdb as m if __name__ == '__main__': db = m.connect(user='root',db='test') c = db.cursor() c.callproc("per5min") print(c.fetchall()) c.close() db.close()

The solution above works, but probably will need some tweaking, e.g. dtStart can be an argument to the SP. And, it's indeed all SQL!

The above stored procedure was made quickly, so it there might a few bugs.. but it might get you going!

S.Lott · Accepted Answer · 2010-01-22 18:09:27Z

You cannot query data you do not have.

You (as a thinking person) can claim that the 00:20 data is missing; but there's no easy way to define "missing" in some more formal SQL sense.

The best you can do is create a table with all of the expected times.

Then you can do an outer join between expected times (including a 0 for 00:20) and actual times (missing the 00:20 sample) and you'll get kind of result you're expecting.

ggiroux · Accepted Answer · 2010-01-22 18:12:11Z

I see no easy way to create non-existing records out of thin air, but you could create yourself a point_dates table containing all the timestamps you're interested in, and left join it on your data:

select pd.slot, IFNULL(data_point, 0) from point_dates pd left join some_table st on st.point_date=pd.slot where point_date >= start_date AND point_date < end_date order by point_date

Collectives™ on Stack Overflow

python : mysql : Return 0 when no rows found

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related