source: TOOLS/ConsoGENCI/trunk/bin/libconso_db.py @ 3083

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

# ==================================================================== #
# Author: Sonia Labetoulle                                             #
# Contact: sonia.labetoulle _at_ ipsl.jussieu.fr                       #
# Created: 2016                                                        #
# History:                                                             #
# Modification:                                                        #
# ==================================================================== #

# =================================================================== #
# 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
from __future__ import print_function, division

# Standard library imports
import psycopg2
import psycopg2.extras

# Application library imports


#######################################################################
def connect_db(db_host, db_name, db_user, db_pwd):

  conn_string = "host={} dbname={} user={} password={}".format(
    db_host, db_name, db_user, db_pwd
  )

  # Print the connection string we will use to connect
  print("Connecting to database\n  -> {}".format(
      conn_string.replace(db_pwd, "*****")
    )
  )

  # Get a connection, if a connect cannot be made an exception will be
  # raised here
  try:
    conn = psycopg2.connect(conn_string)
  except Exception as rc:
    print("Problem with DB connect:\n{}".format(rc))
    exit(9)

  # conn.cursor will return a cursor object, you can use this cursor
  # to perform queries
  # cursor = conn.cursor()
  cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  print("  => Connected!\n")

  return conn, cursor


#######################################################################
def select_db(cursor, request):

  # Execute query
  try:
    cursor.execute(request)
  except Exception as rc:
    print("Problem with select:\n{}".format(rc))
    exit(9)

  # # retrieve the records from the database
  # records = cursor.fetchall()

  # # print out the records using pretty print
  # # note that the NAMES of the columns are not shown, instead just indexes.
  # # for most people this isn't very useful so we'll show you how to return
  # # columns as a dictionary (hash) in the next example.
  # pprint(records)


#######################################################################
def insert_db(cursor, request):
  """
  """

  # Execute query
  try:
    cursor.execute(request)
  except Exception as rc:
    print("Problem with insert:\n{}".format(rc))
    exit(9)


#######################################################################
def commit_db(conn):
  """
  """

  # Execute query
  try:
    conn.commit()
  except Exception as rc:
    print("Problem with commit:\n{}".format(rc))
    exit(9)


#######################################################################
def close_db(conn):
  """
  """

  try:
    conn.close()
  except Exception as rc:
    print("Problem with close:\n{}".format(rc))
    exit(9)


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