/search.css" rel="stylesheet" type="text/css"/> /search.js">
| Classes | Job Modules | Data Objects | Services | Algorithms | Tools | Packages | Directories | Tracs |

In This Package:

vgroup.py
Go to the documentation of this file.
00001 #!/usr/bin/env python
00002 """
00003 Performs context *group by* queries on DBI Validity tables and persists as list-of-dicts for 
00004 subsequent interrogation, for example:: 
00005 
00006         mysql> select SITEMASK,SUBSITE,count(*) as N,min(SEQNO) as minSEQNO,max(SEQNO) as maxSEQNO,min(TIMESTART) as minTIMESTART,max(TIMESTART) as maxTIMESTART,min(INSERTDATE) as minINSERTDATE,max(INSERTDATE) as maxINSERTDATE from DcsAdLidSensorVld group by SITEMASK,SUBSITE ;
00007 
00008         +----------+---------+-------+----------+----------+---------------------+---------------------+---------------------+---------------------+
00009         | SITEMASK | SUBSITE | N     | minSEQNO | maxSEQNO | minTIMESTART        | maxTIMESTART        | minINSERTDATE       | maxINSERTDATE       |
00010         +----------+---------+-------+----------+----------+---------------------+---------------------+---------------------+---------------------+
00011         |        1 |       1 | 14393 |        1 |    83951 | 2011-06-13 03:06:00 | 2013-03-05 18:43:59 | 2012-01-22 15:31:25 | 2013-03-06 03:37:56 | 
00012         |        1 |       2 | 14528 |        2 |    83950 | 2011-06-13 03:06:00 | 2013-03-05 18:40:25 | 2012-01-22 15:31:25 | 2013-03-06 03:34:23 | 
00013         |        2 |       1 | 12295 |        3 |    83949 | 2011-06-13 03:06:00 | 2013-03-05 18:36:13 | 2012-01-22 15:31:25 | 2013-03-06 03:30:16 | 
00014         |        4 |       1 | 11341 |        4 |    83945 | 2011-06-13 03:06:00 | 2013-03-05 18:20:57 | 2012-01-22 15:31:25 | 2013-03-06 03:14:55 | 
00015         |        4 |       2 | 11135 |        5 |    83946 | 2011-06-13 03:06:00 | 2013-03-05 18:21:40 | 2012-01-22 15:31:25 | 2013-03-06 03:15:41 | 
00016         |        4 |       3 | 11107 |        6 |    83944 | 2011-06-13 03:06:00 | 2013-03-05 18:18:46 | 2012-01-22 15:31:25 | 2013-03-06 03:12:43 | 
00017         |       32 |       1 |  4619 |        7 |    83947 | 2011-06-13 03:06:00 | 2013-03-05 18:22:31 | 2012-01-22 15:31:25 | 2013-03-06 03:16:27 | 
00018         |       32 |       2 |  4533 |        8 |    83948 | 2011-06-13 03:06:00 | 2013-03-05 18:33:39 | 2012-01-22 15:31:25 | 2013-03-06 03:27:42 | 
00019         +----------+---------+-------+----------+----------+---------------------+---------------------+---------------------+---------------------+
00020         8 rows in set (0.83 sec)
00021 
00022 
00023 ::
00024 
00025         [blyth@cms01 vmap]$ ./vgroup.py vgroup_*.pk
00026         INFO:__main__:select SITEMASK,SUBSITE,count(*) as N,min(SEQNO) as minSEQNO,max(SEQNO) as maxSEQNO,min(TIMESTART) as minTIMESTART,max(TIMESTART) as maxTIMESTART,min(INSERTDATE) as minINSERTDATE,max(INSERTDATE) as maxINSERTDATE from %sVld group by SITEMASK,SUBSITE 
00027         INFO:pickler:reading from vgroup_base.pk 
00028         SITE SUBS N      minSEQ maxSEQ minTIMESTART         maxTIMESTART         minINSERTDATE        maxINSERTDATE       
00029         1    1    14512  1      84903  2011-06-13 03:06:00  2013-03-10 18:43:31  2012-01-22 15:31:25  2013-03-11 03:37:27 
00030         1    2    14647  2      84902  2011-06-13 03:06:00  2013-03-10 18:39:57  2012-01-22 15:31:25  2013-03-11 03:33:53 
00031         2    1    12414  3      84901  2011-06-13 03:06:00  2013-03-10 18:36:18  2012-01-22 15:31:25  2013-03-11 03:30:20 
00032         4    1    11460  4      84897  2011-06-13 03:06:00  2013-03-10 18:21:49  2012-01-22 15:31:25  2013-03-11 03:15:44 
00033         4    2    11254  5      84898  2011-06-13 03:06:00  2013-03-10 18:22:48  2012-01-22 15:31:25  2013-03-11 03:16:44 
00034         4    3    11226  6      84896  2011-06-13 03:06:00  2013-03-10 18:19:18  2012-01-22 15:31:25  2013-03-11 03:13:13 
00035         32   1    4738   7      84899  2011-06-13 03:06:00  2013-03-10 18:23:24  2012-01-22 15:31:25  2013-03-11 03:17:19 
00036         32   2    4652   8      84900  2011-06-13 03:06:00  2013-03-10 18:33:09  2012-01-22 15:31:25  2013-03-11 03:27:10 
00037         INFO:pickler:reading from vgroup_post7.pk 
00038         SITE SUBS N      minSEQ maxSEQ minTIMESTART         maxTIMESTART         minINSERTDATE        maxINSERTDATE       
00039         1    1    14512  1      84903  2011-06-13 03:06:00  2013-03-10 18:43:31  2012-01-22 15:31:25  2013-03-11 03:37:27 
00040         1    2    14647  2      84902  2011-06-13 03:06:00  2013-03-10 18:39:57  2012-01-22 15:31:25  2013-03-11 03:33:53 
00041         2    1    12414  3      84901  2011-06-13 03:06:00  2013-03-10 18:36:18  2012-01-22 15:31:25  2013-03-11 03:30:20 
00042         4    1    11460  4      84897  2011-06-13 03:06:00  2013-03-10 18:21:49  2012-01-22 15:31:25  2013-03-11 03:15:44 
00043         4    2    11254  5      84898  2011-06-13 03:06:00  2013-03-10 18:22:48  2012-01-22 15:31:25  2013-03-11 03:16:44 
00044         4    3    11226  6      84896  2011-06-13 03:06:00  2013-03-10 18:19:18  2012-01-22 15:31:25  2013-03-11 03:13:13 
00045         4    4    4655   84904  89558  2012-07-30 00:37:02  2013-03-10 18:23:24  2013-03-11 07:24:17  2013-03-11 07:27:57 
00046         32   1    4738   7      84899  2011-06-13 03:06:00  2013-03-10 18:23:24  2012-01-22 15:31:25  2013-03-11 03:17:19 
00047         32   2    4652   8      84900  2011-06-13 03:06:00  2013-03-10 18:33:09  2012-01-22 15:31:25  2013-03-11 03:27:10 
00048         INFO:pickler:reading from vgroup_post8.pk 
00049         SITE SUBS N      minSEQ maxSEQ minTIMESTART         maxTIMESTART         minINSERTDATE        maxINSERTDATE       
00050         1    1    14512  1      84903  2011-06-13 03:06:00  2013-03-10 18:43:31  2012-01-22 15:31:25  2013-03-11 03:37:27 
00051         1    2    14647  2      84902  2011-06-13 03:06:00  2013-03-10 18:39:57  2012-01-22 15:31:25  2013-03-11 03:33:53 
00052         2    1    12414  3      84901  2011-06-13 03:06:00  2013-03-10 18:36:18  2012-01-22 15:31:25  2013-03-11 03:30:20 
00053         2    2    4472   89559  94030  2012-08-06 05:37:07  2013-03-10 18:33:09  2013-03-11 07:27:58  2013-03-11 07:31:47 
00054         4    1    11460  4      84897  2011-06-13 03:06:00  2013-03-10 18:21:49  2012-01-22 15:31:25  2013-03-11 03:15:44 
00055         4    2    11254  5      84898  2011-06-13 03:06:00  2013-03-10 18:22:48  2012-01-22 15:31:25  2013-03-11 03:16:44 
00056         4    3    11226  6      84896  2011-06-13 03:06:00  2013-03-10 18:19:18  2012-01-22 15:31:25  2013-03-11 03:13:13 
00057         4    4    4655   84904  89558  2012-07-30 00:37:02  2013-03-10 18:23:24  2013-03-11 07:24:17  2013-03-11 07:27:57 
00058         32   1    4738   7      84899  2011-06-13 03:06:00  2013-03-10 18:23:24  2012-01-22 15:31:25  2013-03-11 03:17:19 
00059         32   2    4652   8      84900  2011-06-13 03:06:00  2013-03-10 18:33:09  2012-01-22 15:31:25  2013-03-11 03:27:10 
00060         INFO:pickler:reading from vgroup_post.pk 
00061         SITE SUBS N      minSEQ maxSEQ minTIMESTART         maxTIMESTART         minINSERTDATE        maxINSERTDATE       
00062         1    1    14512  1      84903  2011-06-13 03:06:00  2013-03-10 18:43:31  2012-01-22 15:31:25  2013-03-11 03:37:27 
00063         1    2    14647  2      84902  2011-06-13 03:06:00  2013-03-10 18:39:57  2012-01-22 15:31:25  2013-03-11 03:33:53 
00064         2    1    12414  3      84901  2011-06-13 03:06:00  2013-03-10 18:36:18  2012-01-22 15:31:25  2013-03-11 03:30:20 
00065         2    2    4472   89559  94030  2012-08-06 05:37:07  2013-03-10 18:33:09  2013-03-11 07:27:58  2013-03-11 07:31:47 
00066         4    1    11460  4      84897  2011-06-13 03:06:00  2013-03-10 18:21:49  2012-01-22 15:31:25  2013-03-11 03:15:44 
00067         4    2    11254  5      84898  2011-06-13 03:06:00  2013-03-10 18:22:48  2012-01-22 15:31:25  2013-03-11 03:16:44 
00068         4    3    11226  6      84896  2011-06-13 03:06:00  2013-03-10 18:19:18  2012-01-22 15:31:25  2013-03-11 03:13:13 
00069         4    4    4655   84904  89558  2012-07-30 00:37:02  2013-03-10 18:23:24  2013-03-11 07:24:17  2013-03-11 07:27:57 
00070         32   1    4738   7      84899  2011-06-13 03:06:00  2013-03-10 18:23:24  2012-01-22 15:31:25  2013-03-11 03:17:19 
00071         32   2    4652   8      84900  2011-06-13 03:06:00  2013-03-10 18:33:09  2012-01-22 15:31:25  2013-03-11 03:27:10 
00072         [blyth@cms01 vmap]$ 
00073 
00074 
00075 """
00076 import os, logging
00077 from pickler import Pickler
00078 log = logging.getLogger(__name__)
00079 
00080 class VF(dict):
00081     """
00082     Validity field querying and presentation 
00083     """
00084     xf = "SITEMASK SIMMASK SUBSITE TASK".split() 
00085     qf = "SEQNO AGGREGATENO".split()
00086     tf = "TIMESTART TIMEEND INSERTDATE VERSIONDATE".split()
00087     af = xf + qf + tf
00088     fmt = {'x':4,'q':6,'t':20,'e':6} 
00089 
00090     def __init__(self, name, **kwa):
00091         dict.__init__(self, name=name)
00092         self.update(kwa)
00093         if name in self.xf:
00094             self.type = 'x'
00095         elif name in self.qf:
00096             self.type = 'q'
00097         elif name in self.tf:
00098             self.type = 't'
00099         else:
00100             self.type = 'e'
00101             assert self['as_'], "non standard expression field names require an 'as_' specifer " 
00102         pass
00103 
00104     # sql 'as' name
00105     as_  = property(lambda _:'%(as_)s' % _ if _.has_key('as_') else _['name'])
00106     asmin = property(lambda _:"min%(name)s" % _)
00107     asmax = property(lambda _:"max%(name)s" % _) 
00108     asmm  = property(lambda _:[_.asmin,_.asmax])
00109 
00110     # sql column 
00111     sqlmin = property(lambda _:"min(%(name)s) as min%(name)s" % _)
00112     sqlmax = property(lambda _:"max(%(name)s) as max%(name)s" % _)
00113     sqlcol = property(lambda _:'%(name)s as %(as_)s' % _ if _.has_key('as_') else _['name'])
00114     sqlmm  = property(lambda _:[_.sqlmin,_.sqlmax])
00115 
00116     # value formatting
00117     fwid  = property(lambda _:_.fmt[_.type])
00118     ffmt = property(lambda _:"%("+_.as_+")-"+str(_.fwid)+"s")
00119     fmin = property(lambda _:"%(min"+_['name']+")-"+str(_.fwid)+"s")
00120     fmax = property(lambda _:"%(max"+_['name']+")-"+str(_.fwid)+"s")
00121     fmtmm  = property(lambda _:[_.fmin,_.fmax])
00122 
00123     # formatted label
00124     lfm  = property(lambda _:"%-" + str(_.fwid) + "s" )
00125     lfmt = property(lambda _:_.lfm % _.as_[:_.fwid])
00126     lmin = property(lambda _:_.lfm % _.asmin[:_.fwid])
00127     lmax = property(lambda _:_.lfm % _.asmax[:_.fwid])
00128     lmtmm  = property(lambda _:[_.lmin,_.lmax])
00129 
00130 
00131 class VGroup(dict):
00132 
00133     def __init__(self, *args, **kwa):  
00134         """
00135         :param xf: space delimited list of context fields to **group by** OR None for default "SITEMASK SIMMASK SUBSITE TASK" 
00136         :param qf: space delimited list of other fields to report ranges for OR None for default "SEQNO AGGREGATENO"
00137         :param tf: space delimited list of date fields to report ranges for OR None for default "TIMESTART TIMEEND INSERTDATE VERSIONDATE"
00138         """
00139 
00140         dict.__init__(self, *args, **kwa)
00141 
00142         xf = self.get('xf',None)
00143         qf = self.get('qf',None)
00144         tf = self.get('tf',None)
00145 
00146         xf = map(VF, xf.split() if xf else VF.xf) 
00147         qf = map(VF, qf.split() if qf else VF.qf) 
00148         tf = map(VF, tf.split() if tf else VF.tf) 
00149 
00150         cf = [VF("count(*)",as_="N")]
00151 
00152         xcol = map(lambda _:_.sqlcol, xf )
00153         gcol = ",".join(xcol)
00154 
00155         col  = map(lambda _:[_.sqlcol],  xf + cf ) 
00156         col += map(lambda _:_.sqlmm,     qf + tf )  
00157 
00158         fld  = map(lambda _:[_.as_], xf + cf )
00159         fld += map(lambda _:_.asmm,  qf + tf )
00160 
00161         fmt  = map(lambda _:[_.ffmt] , xf + cf )
00162         fmt += map(lambda _:_.fmtmm,  qf + tf )
00163 
00164         lbl  = map(lambda _:[_.lfmt] , xf + cf )
00165         lbl += map(lambda _:_.lmtmm,  qf + tf )
00166 
00167         scol = sum(col,[])
00168         sfld = sum(fld,[])
00169         sfmt = sum(fmt,[])
00170         slbl = sum(lbl,[])
00171 
00172         col = ",".join(scol)
00173         table = "%s"  # filled in the __call__
00174         sql = "select %(col)s from %(table)sVld group by %(gcol)s " % locals()
00175         log.info(sql)
00176 
00177         fld = " ".join(sfld)
00178         fmt = " ".join(sfmt)
00179         lbl = " ".join(slbl)
00180 
00181         self.sql = sql
00182         self.xcol = xcol
00183         self.fmt = fmt
00184         self.lbl = lbl
00185         self.fld = fld
00186 
00187     def populate(self, arg): 
00188         """
00189         Clear this dict and set contents from the `arg`
00190 
00191         :param arg: either path to pickled obj OR pre-retrieved dict
00192         """
00193         self.clear()
00194         dod = arg if type(arg) == dict else Pickler.retrieve(arg)
00195         for k in dod:
00196             self[k] = dod[k]
00197 
00198     def __call__(self, db, table ):
00199         """
00200         Perform validity context group query, retaining 
00201         results within this list of dict  
00202 
00203         :param db: DB instance
00204         :param table: payload table name
00205         """
00206         for d in db(self.sql % table):
00207             key = tuple(map(lambda k:int(d[k]),self.xcol))
00208             self[key] = d
00209 
00210     def persist(self, path):
00211         """
00212         :param path: for pickling 
00213         """
00214         Pickler.persist(dict(self), path)
00215 
00216     def tuple_keys(self):
00217         return sorted(filter(lambda _:type(_)==tuple,self.keys()))
00218 
00219     def column(self, k):
00220         tks = self.tuple_keys()
00221         return map(lambda tk:self[tk][k], tks ) 
00222 
00223     def __repr__(self):
00224         return "\n".join([self.lbl] + map(lambda k:self.fmt % self[k], self.tuple_keys() )  )
00225 
00226 
00227 
00228 
00229 if __name__ == '__main__':
00230     import sys
00231     logging.basicConfig(level=logging.INFO)
00232     from DybPython import DB 
00233     db = DB("client")
00234 
00235     vg = VGroup(xf="SITEMASK SUBSITE", qf="SEQNO", tf="TIMESTART INSERTDATE")
00236     if len(sys.argv)>1: 
00237         for pk in sys.argv[1:]: 
00238             if os.path.exists(pk):
00239                 vg.populate(pk)
00240                 print vg
00241 
00242 if 0:
00243     vg(db,"DcsAdLidSensor") 
00244     vg.persist(pc)
00245  
| Classes | Job Modules | Data Objects | Services | Algorithms | Tools | Packages | Directories | Tracs |

Generated on Fri May 16 2014 09:57:24 for DybDbi by doxygen 1.7.4