DCS_db_parser.py

Go to the documentation of this file.

#!/usr/bin/env python

#///////////////////////////////////////////////////////////////////////////////
# INCLUDES
import os                                      # Access to operating system (env vars)
from ROOT import *                             # ROOT histograms, import * seems overkill
import psycopg2                                # PostgreSQL database adapter
import psycopg2.extras                         # Do we use any extras?
from datetime import date, datetime, timedelta # Date/time stuff
import time # used to grab timezone
from array import array
import sys                                     # Pass arguments through the command line

#///////////////////////////////////////////////////////////////////////////////
# TIME PERIOD SETTINGS
# Extend DB period 1 day greater than intended plots 5/14/2018 LMM 
if (sys.argv[1] == 'day'):
  print 'Using data for one DAY'
  period = 2
  periodname = 'day'
elif (sys.argv[1] == 'week'):
  print 'Using data for one WEEK'
  period = 8
  periodname = 'week'
elif (sys.argv[1] == 'month'):
  print 'Using data for one MONTH'
  period = 31
  periodname = 'month'
else:
  sys.exit("You need to run with 'day', 'week', or 'month' as the first argument, you had '" + sys.argv[1] + "'")

#Check detector argument
if (sys.argv[2] != 'ND' and sys.argv[2] != 'FD'):
  sys.exit("You need to run with 'ND' or 'FD' as the second argument, you had '" + sys.argv[2] + "'")


#///////////////////////////////////////////////////////////////////////////////
# CONFIGURATION

DB_HOST     = 'novadcs-far-logger.fnal.gov'
DB_PORT     = '5432'
DB_USER     = 'nova_reader'
# Hardcoded passwords = bad. Instead, read PWD from file (file defined in ENV)
DB_PASS     = open(os.environ['NOVADBPWDFILE'], 'r').readlines()[0].strip()
DB_DATABASE = 'nova_prod'

if (sys.argv[2] == 'ND'):
  DB_HOST = 'ifdbrep.fnal.gov'
  DB_PORT = '5439'

# Here we will declare some functions that will perform specific actions and 
# make the code more readable. To follow the execution of this script, go down
# to the "main" function. That is the function that is executed by default and 
# will call all the other functions.

#///////////////////////////////////////////////////////////////////////////////
def database_connect():
  """ Initiate connection to psql database
    """

  # Initiate connection to database
  try:
    db = psycopg2.connect(\
        "dbname=%s host=%s user=%s password=%s port=%s" % \
        (DB_DATABASE, DB_HOST, DB_USER, DB_PASS, DB_PORT))
        
  except:
    print "Unable to connect to the database"

  # Create cursor object so that we can send queries to the db.
  # Note: The `cursor_factory' argument will create a dictionary
  # cursor so that columns are returned as a dictionary so that we
  # can access columns by their name instead of index.
  global db_cursor
  db_cursor = db.cursor(cursor_factory = psycopg2.extras.DictCursor)


#///////////////////////////////////////////////////////////////////////////////
def main():

  # Creates database object called db_cursor
  database_connect()
  #FIXME Should check that DB connect worked
  
  # CHANGE DATES HERE
  # Set dates for testing
  #t0 = datetime(year = 2016, month = 2, day = 9)
  #t1 = datetime(year = 2016, month = 2, day = 15)

  # Set dates automatically
  if time.daylight :
    #print time.altzone/3600
    utchrdiff =  time.altzone/3600
  else : 
    #print time.timezone/3600 
    utchrdiff = time.timezone/3600
  #t1 = datetime.now() + timedelta(hours = 5) #+5hrs for timezone differences
  # Want times in UTC, since that is what is in DB  just use utcnow()
  t1 = datetime.utcnow()
  # "now" might not be the best time to use, if script takes a while, probably ok 
  t0 = t1 - timedelta(days = period)
  #print "t0 = " + str(t0)
  print " Getting info from DB from UTC t0 = " + str(t0) + "to t1 = " +str(t1)

  # Start at the first date
  t = t0
 
  # Make a tree
  f = TFile("trees/dcs_db_readout_multichan_"+periodname+"_"+sys.argv[2]+".root","recreate")
  tree = TTree("dcs_db_tree","DCS DB readout")
  firsttime=1
  if (sys.argv[2] == 'ND') :
    report_table = "neardet.dcs_analog_report"
    channel_table = "neardet.dcs_channelid"
  else :
    report_table = "fardet.dcs_analog_report"
    channel_table = "fardet.dcs_channelid"

  query = ("SELECT * FROM %s as report_tbl, %s as chan_tbl "
           "WHERE chan_tbl.id=report_tbl.chanid and substring(name,2,1)=':' "
           #" 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.
           " and tread>='%s' and tread<='%s' "
           " order by chanid,tread " % (report_table,channel_table,str(t0), str(t1)) )
    
    # Submit query to psql. Store response as a Python object called "reports"
  db_cursor.execute(query)
  reports = db_cursor.fetchall()
  branchname = 'dummy_name'  
  branchcounter = 0
  for report in reports:
    # Check if it is a new variable, and make a branch for it.
    # a better way ot do this would be to output all data with the channel names to one tree, and 
    # change the plotter to select the dat for each variable. We'll stick with this for now.
    if (branchname != report['name'].replace(':','')) :
      # Add branch - one for each measurement (if not yet exist)
      branchcounter = 0
      vals = array('f', [0])
      branchname = report['name'].replace(':','') #ROOT really doesn't like branches with ':' in the name
      tree.Branch(branchname, vals, 'Variable/F')
      times = TDatime()   # sets times equal to current time
      chantime = 'Time_' + branchname
      tree.Branch(chantime, times)

    tread = report['tread']
    times.Set(tread.year, tread.month, tread.day, tread.hour, tread.minute, tread.second)
    vals[0] = report['readval']
    tree.GetBranch(branchname).Fill()
    tree.GetBranch(chantime).Fill()
    branchcounter +=1
    tree.GetBranch(branchname).SetEntries(branchcounter) # May not be needed, Probably does no harm. LMM
    tree.GetBranch(chantime).SetEntries(branchcounter) # May not be needed, Probably does no harm. LMM
  tree.SetEntries(-1) # Set entries to # of entries in branches Was needed to get data to file
  tree.Write()
  #tree.Print()
  f.Write()
  f.Close()
  # Within the loop you could also construct a histogram with PyROOT, but I
  # don't think TH1Fs handle dates very well. Generally what we do here is 
  # write the info we want to a TTree and save it in a .root file. Then in 
  # another script, we loop through that TTree and make a TGraph out of it.

# Prevent execution on import
if __name__ == "__main__":
  main()