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

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# ==================================================================== #
# ssh readonly@prodiguer-test-db.ipsl.upmc.fr                          #
# psql -U prodiguer_db_user prodiguer                                  #
#                                                                      #
# ssh readonly@prodiguer-test-db.ipsl.upmc.fr -L 5432:localhost:5432   #
# ==================================================================== #

# this must come first
from __future__ import print_function, unicode_literals, division

# standard library imports
# from pprint import pprint

# Application library imports
import libconsodb as cdb


########################################
if __name__ == "__main__":

  project_list = [
    {
      "name": "gencmip6",
      "centre": "tgcc",
      "machine": "curie",
      "node": "thin",
      "alloc": 5000000,
      "start": "20150101",
      "end": "20150630",
    },
    {
      "name": "gencmip6",
      "centre": "tgcc",
      "machine": "curie",
      "node": "thin",
      "alloc": 15000000,
      "start": "20150701",
      "end": "20151231",
    },
    {
      "name": "rgzi",
      "centre": "idris",
      "machine": "ada",
      "node": "standard",
      "alloc": 2000000,
      "start": "20150101",
      "end": "20151231",
    },
  ]

  # Connection info
  db_host = "134.157.170.104"
  # db_port = "5432"
  db_name = "prodiguer"
  db_user = "prodiguer_db_user"
  # db_pwd = "secret"

  conn, cursor = cdb.connect_db(db_host, db_name, db_user)

  # Build request
  table_name = "conso.tbl_allocation"
  request = (
    "INSERT INTO " + table_name + " ("
    "project, centre, machine, node_type, total_hrs, "
    "start_date, end_date, row_create_date"
    ") VALUES "
  )

  lines_req = [
    (
      "('{name}', "
      "'{centre}', "
      "'{machine}', "
      "'{node}', "
      "{alloc}, "
      "'{start}', "
      "'{end}', "
      "{create})"
    ) .format(
      name=line["name"],
      centre=line["centre"],
      machine=line["machine"],
      node=line["node"],
      alloc=line["alloc"],
      start=line["start"],
      end=line["end"],
      create="CURRENT_TIMESTAMP",
    ) for line in project_list
  ]

  print(", ".join(lines_req))
  request = request + ", ".join(lines_req)
  print(request)

  cdb.insert_db(cursor, request)

  cdb.commit_db(conn)