DCS_db_parser.py
Go to the documentation of this file.
1 #!/usr/bin/env python
2 
3 #///////////////////////////////////////////////////////////////////////////////
4 # INCLUDES
5 import os # Access to operating system (env vars)
6 from ROOT import * # ROOT histograms, import * seems overkill
7 import psycopg2 # PostgreSQL database adapter
8 import psycopg2.extras # Do we use any extras?
9 from datetime import date, datetime, timedelta # Date/time stuff
10 import time # used to grab timezone
11 from array import array
12 import sys # Pass arguments through the command line
13 
14 #///////////////////////////////////////////////////////////////////////////////
15 # TIME PERIOD SETTINGS
16 # Extend DB period 1 day greater than intended plots 5/14/2018 LMM
17 if (sys.argv[1] == 'day'):
18  print 'Using data for one DAY'
19  period = 2
20  periodname = 'day'
21 elif (sys.argv[1] == 'week'):
22  print 'Using data for one WEEK'
23  period = 8
24  periodname = 'week'
25 elif (sys.argv[1] == 'month'):
26  print 'Using data for one MONTH'
27  period = 31
28  periodname = 'month'
29 else:
30  sys.exit("You need to run with 'day', 'week', or 'month' as the first argument, you had '" + sys.argv[1] + "'")
31 
32 #Check detector argument
33 if (sys.argv[2] != 'ND' and sys.argv[2] != 'FD'):
34  sys.exit("You need to run with 'ND' or 'FD' as the second argument, you had '" + sys.argv[2] + "'")
35 
36 
37 #///////////////////////////////////////////////////////////////////////////////
38 # CONFIGURATION
39 
40 DB_HOST = 'novadcs-far-logger.fnal.gov'
41 DB_PORT = '5432'
42 DB_USER = 'nova_reader'
43 # Hardcoded passwords = bad. Instead, read PWD from file (file defined in ENV)
44 DB_PASS = open(os.environ['NOVADBPWDFILE'], 'r').readlines()[0].strip()
45 DB_DATABASE = 'nova_prod'
46 
47 if (sys.argv[2] == 'ND'):
48  DB_HOST = 'ifdbrep.fnal.gov'
49  DB_PORT = '5439'
50 
51 # Here we will declare some functions that will perform specific actions and
52 # make the code more readable. To follow the execution of this script, go down
53 # to the "main" function. That is the function that is executed by default and
54 # will call all the other functions.
55 
56 #///////////////////////////////////////////////////////////////////////////////
58  """ Initiate connection to psql database
59  """
60 
61  # Initiate connection to database
62  try:
63  db = psycopg2.connect(\
64  "dbname=%s host=%s user=%s password=%s port=%s" % \
65  (DB_DATABASE, DB_HOST, DB_USER, DB_PASS, DB_PORT))
66 
67  except:
68  print "Unable to connect to the database"
69 
70  # Create cursor object so that we can send queries to the db.
71  # Note: The `cursor_factory' argument will create a dictionary
72  # cursor so that columns are returned as a dictionary so that we
73  # can access columns by their name instead of index.
74  global db_cursor
75  db_cursor = db.cursor(cursor_factory = psycopg2.extras.DictCursor)
76 
77 
78 #///////////////////////////////////////////////////////////////////////////////
79 def main():
80 
81  # Creates database object called db_cursor
83  #FIXME Should check that DB connect worked
84 
85  # CHANGE DATES HERE
86  # Set dates for testing
87  #t0 = datetime(year = 2016, month = 2, day = 9)
88  #t1 = datetime(year = 2016, month = 2, day = 15)
89 
90  # Set dates automatically
91  if time.daylight :
92  #print time.altzone/3600
93  utchrdiff = time.altzone/3600
94  else :
95  #print time.timezone/3600
96  utchrdiff = time.timezone/3600
97  #t1 = datetime.now() + timedelta(hours = 5) #+5hrs for timezone differences
98  # Want times in UTC, since that is what is in DB just use utcnow()
99  t1 = datetime.utcnow()
100  # "now" might not be the best time to use, if script takes a while, probably ok
101  t0 = t1 - timedelta(days = period)
102  #print "t0 = " + str(t0)
103  print " Getting info from DB from UTC t0 = " + str(t0) + "to t1 = " +str(t1)
104 
105  # Start at the first date
106  t = t0
107 
108  # Make a tree
109  f = TFile("trees/dcs_db_readout_multichan_"+periodname+"_"+sys.argv[2]+".root","recreate")
110  tree = TTree("dcs_db_tree","DCS DB readout")
111  firsttime=1
112  if (sys.argv[2] == 'ND') :
113  report_table = "neardet.dcs_analog_report"
114  channel_table = "neardet.dcs_channelid"
115  else :
116  report_table = "fardet.dcs_analog_report"
117  channel_table = "fardet.dcs_channelid"
118 
119  query = ("SELECT * FROM %s as report_tbl, %s as chan_tbl "
120  "WHERE chan_tbl.id=report_tbl.chanid and substring(name,2,1)=':' "
121  #" and substring(name,4,1) = 'L' and (substring(name,3,1) = 'T' or substring(name,3,1) = 'H') " Don't worry about sparse data for now.
122  " and tread>='%s' and tread<='%s' "
123  " order by chanid,tread " % (report_table,channel_table,str(t0), str(t1)) )
124 
125  # Submit query to psql. Store response as a Python object called "reports"
126  db_cursor.execute(query)
127  reports = db_cursor.fetchall()
128  branchname = 'dummy_name'
129  branchcounter = 0
130  for report in reports:
131  # Check if it is a new variable, and make a branch for it.
132  # a better way ot do this would be to output all data with the channel names to one tree, and
133  # change the plotter to select the dat for each variable. We'll stick with this for now.
134  if (branchname != report['name'].replace(':','')) :
135  # Add branch - one for each measurement (if not yet exist)
136  branchcounter = 0
137  vals = array('f', [0])
138  branchname = report['name'].replace(':','') #ROOT really doesn't like branches with ':' in the name
139  tree.Branch(branchname, vals, 'Variable/F')
140  times = TDatime() # sets times equal to current time
141  chantime = 'Time_' + branchname
142  tree.Branch(chantime, times)
143 
144  tread = report['tread']
145  times.Set(tread.year, tread.month, tread.day, tread.hour, tread.minute, tread.second)
146  vals[0] = report['readval']
147  tree.GetBranch(branchname).Fill()
148  tree.GetBranch(chantime).Fill()
149  branchcounter +=1
150  tree.GetBranch(branchname).SetEntries(branchcounter) # May not be needed, Probably does no harm. LMM
151  tree.GetBranch(chantime).SetEntries(branchcounter) # May not be needed, Probably does no harm. LMM
152  tree.SetEntries(-1) # Set entries to # of entries in branches Was needed to get data to file
153  tree.Write()
154  #tree.Print()
155  f.Write()
156  f.Close()
157  # Within the loop you could also construct a histogram with PyROOT, but I
158  # don't think TH1Fs handle dates very well. Generally what we do here is
159  # write the info we want to a TTree and save it in a .root file. Then in
160  # another script, we loop through that TTree and make a TGraph out of it.
161 
162 # Prevent execution on import
163 if __name__ == "__main__":
164  main()
correl_yv Fill(-(dy[iP-1][iC-1]), hyv->GetBinContent(iP, iC))
procfile open("FD_BRL_v0.txt")
def database_connect()