source: TOOLS/ConsoGENCI/bin/init_conso_tbl.py @ 2713

Last change on this file since 2713 was 2713, checked in by labetoulle, 9 years ago

Initial import

File size: 4.7 KB
Line 
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3
4# ==================================================================== #
5# ssh readonly@prodiguer-test-db.ipsl.upmc.fr                          #
6# psql -U prodiguer_db_user prodiguer                                  #
7#                                                                      #
8# ssh readonly@prodiguer-test-db.ipsl.upmc.fr -L 5432:localhost:5432   #
9# ==================================================================== #
10
11
12# This must come first
13from __future__ import print_function, unicode_literals, division
14
15# Standard library imports
16import os
17import math
18# import datetime as dt
19from argparse import ArgumentParser
20
21# Application library imports
22import libconsodb as cdb
23
24
25########################################
26def get_arguments():
27  parser = ArgumentParser()
28  parser.add_argument("project", action="store",
29                      help="Project name")
30  parser.add_argument("center", action="store",
31                      help="Center name (idris/tgcc)")
32
33  parser.add_argument("-v", "--verbose", action="store_true",
34                      help="verbose mode")
35  parser.add_argument("-d", "--dryrun", action="store_true",
36                      help="only print what is to be done")
37  parser.add_argument("-r", "--range", action="store", nargs=2,
38                      help="date range: ssaammjj ssaammjj")
39
40  # parser.add_argument("-f", "--full", action="store_true",
41  #                     help="plot the whole period")
42  # parser.add_argument("-i", "--increment", action="store",
43  #                     type=int, default=1, dest="inc",
44  #                     help="sampling increment")
45  # parser.add_argument("-m", "--max", action="store_true",
46  #                     help="plot with y_max = allocation")
47  # parser.add_argument("-s", "--show", action="store_true",
48  #                     help="interactive mode")
49  # parser.add_argument("-d", "--dods", action="store_true",
50  #                     help="copy output on dods")
51
52  return parser.parse_args()
53
54
55########################################
56if __name__ == "__main__":
57
58  # .. Initialization ..
59  # ====================
60  # ... Command line arguments ...
61  # ------------------------------
62  args = get_arguments()
63  if args.verbose:
64    print(args)
65
66  ROOT_DIR = "/home_local/slipsl/ConsoGENCMIP6/init_db"
67  DATA_DIR = os.path.join(ROOT_DIR, "data")
68
69  SUBMIT_DIR = os.getcwd()
70
71  # project = "rgzi"
72  # centre = "idris"
73
74  conso_per_login = {}
75
76  # Connection info
77  db_host = "134.157.170.104"
78  # db_port = "5432"
79  db_name = "prodiguer"
80  db_user = "prodiguer_db_user"
81  # db_pwd = "secret"
82
83  if args.center == "idris":
84    pattern = "compta_*.dat"
85  elif args.center == "tgcc":
86    pattern = "ccc_myproject.dat_*"
87  else:
88    print("Unknown center {}".format(args.center))
89    exit()
90
91  filelist = cdb.find_input_files(DATA_DIR, pattern, args.range)
92
93  # Build dictionary from files
94  # for filename in cdb.find_idris_files(DATA_DIR):
95  for filename in filelist:
96    print(filename)
97    # jour, heure, alloc, consos = \
98    date, alloc, consos = \
99          cdb.parse_input_cpt(filename, args.project, args.center)
100          # cdb.parse_idris_cpt(filename, args.project)
101
102    # date = dt.datetime.strptime("{} {}".format(jour, heure), "%d/%m/%Y %H:%M")
103
104    for item in consos:
105      login, conso = item
106      if login not in conso_per_login:
107        conso_per_login[login] = set()
108      conso_per_login[login].add((date, conso, ))
109
110  conn, cursor = cdb.connect_db(db_host, db_name, db_user)
111
112  # Extract allocation id from table
113  table_name = "conso.tbl_allocation"
114  request = (
115    "SELECT id "
116    "FROM " + table_name + " "
117    "WHERE project = '" + args.project + "'"
118    "  AND centre = '" + args.center + "'"
119    ";"
120  )
121
122  cdb.select_db(cursor, request)
123  if cursor.rowcount != 1:
124    print(cursor.fetchall())
125    exit()
126
127  (allocation_id, ) = cursor.fetchone()
128
129  for login, conso_list in conso_per_login.iteritems():
130
131    # test = "'"+login+"'" if login != "total" else "NULL"
132    # print(test)
133
134    lines_req = [
135      (
136        "('{alloc}', "
137        "'{date}', "
138        "{total_hrs}, "
139        "{login}, "
140        "{create})"
141      ) .format(
142        alloc=allocation_id,
143        date=date,
144        total_hrs=conso if not math.isnan(conso) else "'NaN'",
145        login="'"+login+"'" if login != "total" else "NULL",
146        create="CURRENT_TIMESTAMP",
147      ) for (date, conso) in conso_list
148    ]
149
150    table_name = "conso.tbl_consumption"
151    request = (
152      "INSERT INTO " + table_name + " ("
153      "  allocation_id, "
154      "  date, "
155      "  total_hrs, "
156      "  login, "
157      "  row_create_date "
158      ") "
159      "VALUES "
160    )
161    request = request + ", ".join(lines_req)
162    print(request)
163
164    cdb.insert_db(cursor, request)
165
166    if not args.dryrun:
167      cdb.commit_db(conn)
Note: See TracBrowser for help on using the repository browser.