Posted By

lrivers on 11/28/10


Tagged


Versions (?)

web2py actions for comparison with SQL


 / Published in: Python
 

This is my Python code

  1. def str2datetime(s, microsecs = 0, format = "%Y-%m-%dT%H:%M:%S"):
  2. parts = s.split(".")
  3. dt = datetime.strptime(parts[0], format)
  4. if microsecs > 0:
  5. return dt.replace(microsecond=int(parts[1][0:6]))
  6. return dt
  7.  
  8. def round_off(mins, secs, to_nearest=1):
  9. div_result, remainder = divmod(mins, to_nearest)
  10.  
  11. if remainder < 7:
  12. return to_nearest * div_result
  13. elif remainder > 7:
  14. return to_nearest * (div_result + 1)
  15. else: #remainder == 7
  16. if secs < 30:
  17. return to_nearest * div_result
  18. else:
  19. return to_nearest * (div_result + 1)
  20.  
  21. def panelrollup():
  22. print "rolling up panels"
  23. from datetime import timedelta
  24. format = "%Y-%m-%dT%H:%M:%S"
  25.  
  26. my_min=db4.data_table.FreezeTime.min()
  27. my_max=db4.data_table.FreezeTime.max()
  28.  
  29. firsttime = db4(db4.data_table).select(my_min).first()[my_min]
  30. lasttime = db4(db4.data_table).select(my_max).first()[my_max]
  31. starttime = firsttime.replace(minute=round_off(firsttime.minute, firsttime.second), second=0)
  32. finishtime = lasttime.replace(minute=round_off(lasttime.minute, lasttime.second), second=0)
  33.  
  34. # round_off rounds down, so:
  35. if firsttime > starttime:
  36. starttime = starttime + timedelta(minutes=1)
  37.  
  38. # starttime is the beginning of the rollup loop
  39. # endtime is the end
  40. # endtime = starttime + timedelta(minutes=1)
  41.  
  42. # setting up the report queries
  43. vi_min = db4.data_table.Vi.min()
  44. vi_max = db4.data_table.Vi.max()
  45. vi_sum = db4.data_table.Vi.sum()
  46. vi_count = db4.data_table.Vi.count()
  47. vo_min = db4.data_table.Vo.min()
  48. vo_max = db4.data_table.Vo.max()
  49. vo_sum = db4.data_table.Vo.sum()
  50. vo_count = db4.data_table.Vo.count()
  51. ii_min = db4.data_table.Ii.min()
  52. ii_max = db4.data_table.Ii.max()
  53. ii_sum = db4.data_table.Ii.sum()
  54. ii_count = db4.data_table.Ii.count()
  55. io_min = db4.data_table.Io.min()
  56. io_max = db4.data_table.Io.max()
  57. io_sum = db4.data_table.Io.sum()
  58. io_count = db4.data_table.Io.count()
  59. pi_min = db4.data_table.Pi.min()
  60. pi_max = db4.data_table.Pi.max()
  61. pi_sum = db4.data_table.Pi.sum()
  62. pi_count = db4.data_table.Pi.count()
  63. po_min = db4.data_table.Po.min()
  64. po_max = db4.data_table.Po.max()
  65. po_sum = db4.data_table.Po.sum()
  66. po_count = db4.data_table.Po.count()
  67.  
  68. my_macaddr = db4().select(db4.data_table.MacAddr, distinct=True, orderby=db4.data_table.MacAddr)
  69. my_panels = [str(my_macaddr[i].MacAddr) for i in range(len(my_macaddr))]
  70.  
  71. endtime = starttime
  72. kickoff = datetime.utcnow()
  73. print "panels start", kickoff
  74. while endtime <= finishtime:
  75. endtime = endtime + timedelta(minutes=1)
  76. for panel in my_panels:
  77. myset = db4((db4.data_table.MacAddr == panel) & (db4.data_table.FreezeTime >= starttime) & (db4.data_table.FreezeTime < endtime))
  78. rows = db4((db4.data_table.MacAddr == panel) & (db4.data_table.FreezeTime >= starttime) & (db4.data_table.FreezeTime < endtime)).select(vi_min, vi_max, vi_sum, vi_count, vo_min, vo_max, vo_sum, vo_count, ii_min, ii_max, ii_sum, ii_count, io_min, io_max, io_sum, io_count, pi_min, pi_max, pi_sum, pi_count, po_min, po_max, po_sum, po_count)
  79. l_vi_min=rows[0][vi_min]
  80. l_vi_max=rows[0][vi_max]
  81. l_vi_sum=rows[0][vi_sum]
  82. l_vi_count=rows[0][vi_count]
  83. l_vi_avg=l_vi_sum/(l_vi_count or 1)
  84. l_vo_min=rows[0][vo_min]
  85. l_vo_max=rows[0][vo_max]
  86. l_vo_sum=rows[0][vo_sum]
  87. l_vo_count=rows[0][vo_count]
  88. l_vo_avg=l_vo_sum/(l_vo_count or 1)
  89. l_ii_min=rows[0][ii_min]
  90. l_ii_max=rows[0][ii_max]
  91. l_ii_sum=rows[0][ii_sum]
  92. l_ii_count=rows[0][ii_count]
  93. l_ii_avg=l_ii_sum/(l_ii_count or 1)
  94. l_io_min=rows[0][io_min]
  95. l_io_max=rows[0][io_max]
  96. l_io_sum=rows[0][io_sum]
  97. l_io_count=rows[0][io_count]
  98. l_io_avg=l_io_sum/(l_io_count or 1)
  99. l_pi_min=rows[0][pi_min]
  100. l_pi_max=rows[0][pi_max]
  101. l_pi_sum=rows[0][pi_sum]
  102. l_pi_count=rows[0][pi_count]
  103. l_pi_avg=l_pi_sum/(l_pi_count or 1)
  104. l_po_min=rows[0][po_min]
  105. l_po_max=rows[0][po_max]
  106. l_po_sum=rows[0][po_sum]
  107. l_po_count=rows[0][po_count]
  108. l_po_avg=l_po_sum/(l_po_count or 1)
  109.  
  110. string_id = db4((db4.data_table.MacAddr == panel)).select(db4.data_table.StringID).first()
  111.  
  112. myRollupId = db4.panel_mins.insert(MacAddr=panel, FreezeTime=starttime, StringID=string_id.StringID, Vi_min=l_vi_min, Vi_max=l_vi_max, Vi_sum=l_vi_sum, Vi_count=l_vi_count, Vi_avg=l_vi_avg, Vo_min=l_vo_min, Vo_max=l_vo_max, Vo_sum=l_vo_sum, Vo_count=l_vo_count, Vo_avg=l_vo_avg, Ii_min=l_ii_min, Ii_max=l_ii_max, Ii_sum=l_ii_sum, Ii_count=l_ii_count, Ii_avg=l_ii_avg,Io_min=l_io_min, Io_max=l_io_max, Io_sum=l_io_sum, Io_count=l_io_count, Io_avg=l_io_avg, Pi_min=l_pi_min, Pi_max=l_pi_max, Pi_sum=l_pi_sum, Pi_count=l_pi_count, Pi_avg=l_pi_avg, Po_min=l_po_min, Po_max=l_po_max, Po_sum=l_po_sum, Po_count=l_po_count, Po_avg=l_po_avg)
  113. myset.update(RollupId=myRollupId)
  114. starttime = endtime
  115. if endtime == finishtime:
  116. touchdown = datetime.utcnow()
  117. print "done: ", touchdown, "ET: ", touchdown - kickoff
  118. return "Panels Rolled Up!"

Report this snippet  

You need to login to post a comment.