source: TOOLS/ConsoGENCI/trunk/bin/init_conso_tbl.py @ 2783

Last change on this file since 2783 was 2783, checked in by labetoulle, 6 years ago
  • Run everything (DB inserts + plots) from single bash script
  • Change exit codes (unsigned in bash, so no negative values...) :
    • 0: everything was ok;
    • 1: nothing done, 'cause nothing to do => ok;
    • >= 2: error.
  • DB access now needs password
  • plot_bilan: added date of production and plotted range to image
  • Cleaning (useless comments, ...)
File size: 5.2 KB
Line 
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3
4# ==================================================================== #
5# Author: Sonia Labetoulle                                             #
6# Contact: sonia.labetoulle _at_ ipsl.jussieu.fr                       #
7# Created: 2016                                                        #
8# History:                                                             #
9# Modification:                                                        #
10# ==================================================================== #
11
12# This must come first
13from __future__ import print_function, unicode_literals, division
14
15# Standard library imports
16import os
17import math
18from argparse import ArgumentParser
19import pprint
20
21# Application library imports
22import libconso_db as cdb
23import libconso_cpt as ccpt
24import db_data
25
26pp = pprint.PrettyPrinter(indent=2)
27
28
29#######################################################################
30def get_arguments():
31  parser = ArgumentParser()
32  parser.add_argument("project", action="store",
33                      help="Project name")
34  parser.add_argument("center", action="store",
35                      help="Center name (idris/tgcc)")
36
37  parser.add_argument("-v", "--verbose", action="store_true",
38                      help="verbose mode")
39  parser.add_argument("-d", "--dryrun", action="store_true",
40                      help="only print what is to be done")
41  parser.add_argument("-r", "--range", action="store", nargs=2,
42                      help="date range: ssaammjj ssaammjj")
43
44  return parser.parse_args()
45
46
47#######################################################################
48if __name__ == "__main__":
49
50  # .. Initialization ..
51  # ====================
52  # ... Command line arguments ...
53  # ------------------------------
54  args = get_arguments()
55  if args.verbose:
56    print(args)
57
58  # ... Files and directories ...
59  # -----------------------------
60  ROOT_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
61  DATA_DIR = os.path.join(ROOT_DIR, "data")
62  SUBMIT_DIR = os.getcwd()
63
64  if args.verbose:
65    print("SUBMIT_DIR:", SUBMIT_DIR)
66    print("DATA_DIR:", DATA_DIR)
67
68  pattern = ccpt.cpt_pattern(args.center, args.project)
69  dirin = os.path.join(DATA_DIR, args.center, args.project)
70  filelist = ccpt.find_input_files(dirin, pattern, args.range)
71
72  # .. Connection to database ..
73  # ============================
74  if args.verbose:
75    print("Connection to database")
76  conn, cursor = cdb.connect_db(
77    db_data.db_host,
78    db_data.db_name,
79    db_data.db_user,
80    db_data.db_pwd,
81  )
82
83  # .. Extract allocation id from table ..
84  # ======================================
85  table_name = "conso.tbl_allocation"
86  request = (
87    "SELECT * "
88    "FROM " + table_name + " "
89    "WHERE project = '" + args.project + "'"
90    "  AND centre = '" + args.center + "'"
91    "ORDER BY start_date"
92    ";"
93  )
94
95  cdb.select_db(cursor, request)
96  # print(cursor.rowcount)
97  # print(cursor.fetchall())
98
99  allocs = []
100  for row in cursor:
101    allocs.append(ccpt.AllocRow(row))
102
103  # .. Build dictionary from files ..
104  # =================================
105  conso_per_alloc = {}
106  for filename in filelist:
107    date, blocs = ccpt.parse_input_cpt(
108      filename, args.project, args.center, mode_conso=True
109    )
110
111    for bloc in blocs:
112      if not bloc.alloc_id:
113        bloc.alloc_id = ccpt.get_project_id(bloc, allocs)
114        if not bloc.alloc_id:
115          print("no alloc id found, skip bloc")
116          print(bloc.machine, bloc.node, bloc.cpt_date)
117          pp.pprint(allocs)
118          continue
119      if bloc.alloc_id not in conso_per_alloc:
120        conso_per_alloc[bloc.alloc_id] = set()
121      for conso in bloc.consos:
122        conso_per_alloc[bloc.alloc_id].add(conso)
123
124  # .. Insert data in table, one alloc_id at a time ..
125  # ==================================================
126  for alloc_id, consos in conso_per_alloc.iteritems():
127    # ... Create request sub string ...
128    # ---------------------------------
129    lines_req = [
130      (
131        "('{alloc}', "
132        "'{date}', "
133        "{total_hrs}, "
134        "{login}, "
135        "{create})"
136      ) .format(
137        alloc=alloc_id,
138        date=item.date,
139        total_hrs=item.conso if not math.isnan(item.conso) else "'NaN'",
140        login="'"+item.login+"'" if item.login != "total" else "NULL",
141        create="CURRENT_TIMESTAMP",
142      ) for item in consos
143    ]
144
145    # ... Create full request ...
146    # ---------------------------
147    table_name = "conso.tbl_consumption"
148    request = (
149      "INSERT INTO " + table_name + " ("
150      "  allocation_id, "
151      "  date, "
152      "  total_hrs, "
153      "  login, "
154      "  row_create_date "
155      ") "
156      "VALUES "
157    )
158    request = request + ", ".join(lines_req)
159
160    # ... Execute request ...
161    # -----------------------
162    if args.verbose:
163      print("Execute request for alloc_id = {}".format(alloc_id))
164    cdb.insert_db(cursor, request)
165
166    # ... Commit inserts ...
167    # ----------------------
168    if not args.dryrun:
169      if args.verbose:
170        print("Commit inserts")
171      cdb.commit_db(conn)
172
173  # .. Close connection to database ..
174  # ==================================
175  if args.verbose:
176    print("Close connection")
177  cdb.close_db(conn)
178
179  exit(0)
Note: See TracBrowser for help on using the repository browser.