def pruebaConectorBD():
    SQL_PRUEBA = """SELECT table_name FROM information_schema.tables 
                    WHERE table_type = 'BASE TABLE' AND table_schema = 'public' 
                    ORDER BY table_type, table_name;"""
                    
    try:
        print("********************* PRUEBA PARA OBTENER TODOS *********************")
        cnn = ConexionBD(SQL_PRUEBA, params = None, tipoConsulta= ConexionBD.SELECT)
        print(cnn.obtenerTodos())

        print("********************* PRUEBA PARA OBTENER VARIOS *********************")
        cnn = ConexionBD(SQL_PRUEBA, params = None, tipoConsulta= ConexionBD.SELECT)
        for e in cnn.obtenerVarios(2):
            print(e)
        
        print("********************* PRUEBA PARA OBTENER UNO *********************")
        cnn = ConexionBD(SQL_PRUEBA, params = None, tipoConsulta= ConexionBD.SELECT)
        print(cnn.obtenerUno())
        
        print("PRUEBAS A CONECTORBD HECHAS SATISFACTORIAMENTE")
    except Exception, e:
        print(e)
        print(errorcodes.lookup(e.pgcode[:2]))
        print(errorcodes.lookup(e.pgcode))
        print("ERROR EN PRUEBAS A CONECTORBD")

def get_strerror(exc):
	if exc.pgcode == None:
		return "Code=None: Database error"

	return "Code={0}: {1}: {2}".format(
		exc.pgcode,
		errorcodes.lookup(exc.pgcode[:2]),
		errorcodes.lookup(exc.pgcode)
	)

def insert_pgp_key(k, dbcursor):
    if (k == None or k == ""):
        print "skipping empty key '%s'" %k
        return

    # first try inserting the key
    try:
        print "inserting pgp key %s"  %k
        #print row
        #print dbcursor.mogrify("INSERT INTO contactdb_pgpkey (pgp_key_id) VALUES (%s)", (k,))
        dbcursor.execute("INSERT INTO contactdb_pgpkey (pgp_key_id) VALUES (%s)", (k,))
        #print "inserted id %d" %(dbcursor.lastrowid)
    except Exception, e:
        print "could not insert pgp key %s"  %k
        print errorcodes.lookup(e)

def get_functional_alias(db_name):
    """Returns the funcional alias and dnsname for a certain database"""
    try:
        with POOL.getconn() as conn:
            with conn.cursor() as curs:
                curs.execute("""select dns_name, alias
                from functional_aliases
                where db_name = %s""", (db_name,))
                logging.debug('DB query: %s', curs.query)
                return curs.fetchone()
    except DatabaseError as dberr:
        logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
        logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
        return None
    finally:
        POOL.putconn(conn)

def get_instances_by_status(status):
    """Returns a JSON object containing all the data for instances with a status"""
    try:
        status = str(status)
        conn = instdb.get_connection()
        curs = conn.cursor()
        curs.execute("""SELECT username, db_name, e_group, category, creation_date, expiry_date, db_type, db_size, no_connections, project, description, version, state, status, master, slave, host 
                        FROM dod_instances WHERE status = :status
                        ORDER BY db_name""", {"status": status})
        rows = curs.fetchall()
        cols = [i[0] for i in curs.description]
        if rows:
            res = create_json_from_result(rows, cols)
            
            # Load the user's data from FIM and join it to the result in Json
            connF = fimdb.get_connection()
            cursF = connF.cursor()
            cursF.execute("""SELECT instance_name, owner_first_name, owner_last_name, owner_login, owner_mail, owner_phone1, owner_phone2, owner_portable_phone, owner_department, owner_group, owner_section
                            FROM fim_ora_ma.db_on_demand""")
            rowsF = cursF.fetchall()
            colsF = [i[0] for i in cursF.description]
            if rowsF:
                usersJson = create_dict_from_result(rowsF, colsF, "INSTANCE_NAME")
                for instance in res:
                    if instance["DB_NAME"] in usersJson:
                        instance["USER"] = usersJson[instance["DB_NAME"]]
            return res
        return None
    except DatabaseError as dberr:
        logging.error("PG Error: %s", dberr.pgerror)
        logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode))
        return None
    finally:
        instdb.end_connection(conn)

def save_feed_entries(entries):
    """
    Stores the given list of entries in the database

    Arguments:
    * feed_id - id of the feed to store the entries under
    * entries - a list of feed entries 
    """
    cursor = db.get_cursor(db.get_connection())

    insert_stmt = """INSERT INTO entries(
        item_id, 
        entry_published,
        entry_title,
        entry_author,
        entry_link,
        feed_id
        ) VALUES ( %s, %s, %s, %s, %s, %s );"""

    for entry in entries:
        try:
            cursor.execute(insert_stmt, entry)
            cursor.connection.commit()
        except IntegrityError as ie:
            err = errorcodes.lookup(ie.pgcode)
            if(err != 'UNIQUE_VIOLATION'): # Unique violation
                logger.info("Integrity error: %s", ie)
                raise
            cursor.connection.rollback()

    cursor.connection.commit() # Probably not neccesary

    cursor.close()

    return True

def _check_stmt_err(stmt, force):
    """Validate results of an executed Statement object relative to force flag

    Creating a constraint can produce a 'benign' error if it already exists.
    If `force` is true, such an error is ignored.

    :param Statement stmt:
    :param bool force:
    :return: None
    :raise: DatabaseError
    """
    if stmt.err is None:
        return

    # Detect error 42P16: multiple primary keys ... are not allowed.
    # This error is produced when the primary key is applied redundantly.
    already_exists = (
        hasattr(stmt.err, 'pgcode')
        and stmt.err.pgcode
        and psycopg2_errorcodes.lookup(
            stmt.err.pgcode) == 'INVALID_TABLE_DEFINITION')

    if force and already_exists:
        return

    raise stmt.err

    def bulkDictionaryInsert(self, table_name, col_dict, id_column=None):
        """
        """
        if len(col_dict) == 0:
            return

        placeholders = ', '.join(['%s'] * len(col_dict))
        columns = ', '.join(col_dict.keys())

        sql = "INSERT into %s ( %s ) VALUES ( %s )" % (table_name, columns, placeholders)

        if id_column is not None:
            sql = sql + " RETURNING " + id_column

        try:
            self.cur.execute(sql, col_dict.values())
            if id_column is not None:
                id_of_new_row = self.cur.fetchone()[0]
        except Exception as e:
            pgError = errorcodes.lookup(e.pgcode)
            raise RuntimeError(pgError)
        if id_column is not None:
            return id_of_new_row
        else:
            return None

def execute_db_query(db_name, query_string, args):
  """
  Inputs:
    db_name: type(string)
    query_string: type(string)
    args: type(list of strings)
  returns:
    type:
      pg.Error or NoneType)
  """
  conn = pg.connect("dbname=%s" %(db_name))
  curr = conn.cursor()

  try:
    curr.execute(query_string, args)
    conn.commit()
  except pg.Error as err:
    return errorcodes.lookup(err.pgcode)
  try:
    result = curr.fetchall()
  except pg.ProgrammingError as e:
    print e
    result = None

  curr.close()
  conn.close()
  return result

def next_dnsname():
    """Returns the next dnsname which can be used for a newly created
        instance, if any."""
    try:
        with POOL.getconn() as conn:
            with conn.cursor() as curs:
                curs.execute("""select dns_name
                from functional_aliases
                where db_name is NULL order by dns_name limit 1""")
                logging.debug('DB query: %s', curs.query)
                return curs.fetchone() # First unused dnsname or None
    except DatabaseError as dberr:
        logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
        logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
        return None
    finally:
        POOL.putconn(conn)

 def batchCommit(self):
     """
     """
     try:
         self.con.commit()
     except Exception as e:
         pgError =errorcodes.lookup(e.pgcode)
         raise RuntimeError(pgError)

def save_to_db(conn_string, name_file, db_file, insert_func):
    count = 0
    start_time = datetime.now()
    with psycopg2.connect(conn_string) as conn:
        with conn.cursor() as cur:
            for row in get_line(db_file):
                sql = insert_func(row,name_file)
                try:
                    cur.execute(sql)
                    count = count + 1
                    if count % 1000 == 0:
                        conn.commit()
                        print count," done"
                except Exception,e:
                    print errorcodes.lookup(e.pgcode)
                    print sql,e
                    break

def insertToDB( fecha, id_est, value, table, conn):
    """ This function  inserts single values into the specified table """
    dateFormat = "MM/DD/YYY/HH24"
    sql =  "SET TimeZone='UTC'; INSERT INTO %s (fecha, val, id_est) VALUES (to_timestamp('%s','%s'), '%s', '%s')\n" % (table, fecha,  dateFormat, value, id_est)
    cur = conn.cursor();
    try:
        cur.execute(sql);
        conn.commit()
    except psycopg2.DatabaseError as e:
    #except psycopg2.IntegrityError as e:
        if e.pgcode == '25P02':
            print('Failed to insert query, CODE:', e.pgcode, " Detail: ", errorcodes.lookup(e.pgcode[:2]))
        else:
            print('Failed to insert query, CODE:', e.pgcode, " Detail: ", errorcodes.lookup(e.pgcode[:2]))

        cur.close()
        conn.rollback()

def allow_pgcodes(cr, *codes):
    """Context manager that will omit specified error codes.

    E.g., suppose you expect a migration to produce unique constraint
    violations and you want to ignore them. Then you could just do::

        with allow_pgcodes(cr, psycopg2.errorcodes.UNIQUE_VIOLATION):
            cr.execute("INSERT INTO me (name) SELECT name FROM you")

    .. warning::
        **All** sentences inside this context will be rolled back if **a single
        error** is raised, so the above example would insert **nothing** if a
        single row violates a unique constraint.

        This would ignore duplicate files but insert the others::

            cr.execute("SELECT name FROM you")
            for row in cr.fetchall():
                with allow_pgcodes(cr, psycopg2.errorcodes.UNIQUE_VIOLATION):
                    cr.execute("INSERT INTO me (name) VALUES (%s)", row[0])

    :param *str codes:
        Undefined amount of error codes found in :mod:`psycopg2.errorcodes`
        that are allowed. Codes can have either 2 characters (indicating an
        error class) or 5 (indicating a concrete error). Any other errors
        will be raised.
    """
    try:
        from psycopg2 import errorcodes, ProgrammingError
    except ImportError:
        from psycopg2cffi import errorcodes, ProgrammingError

    try:
        with cr.savepoint():
            yield
    except ProgrammingError as error:
        msg = "Code: {code}. Class: {class_}. Error: {error}.".format(
            code=error.pgcode,
            class_=errorcodes.lookup(error.pgcode[:2]),
            error=errorcodes.lookup(error.pgcode))
        if error.pgcode not in codes and error.pgcode[:2] in codes:
            logger.info(msg)
        else:
            logger.exception(msg)
            raise

    def delete_arch(arch_id):
        try:
            # Deletes a specific arch
            retval = app.config['db'].delete_arch(arch_id)

            if(retval == True):
                response.status = "200 DELETE OK"
            elif(retval == False):
                response.status = "404 Cannot DELETE"
            elif(retval == "23503"):
                response.status = "409 " + str(errorcodes.lookup(retval))
            else:
                response.status = "500 " + str(errorcodes.lookup(retval))

            return response.status
        except Exception as e:
            raise Exception('Exception encountered: ' + str(e))
            return None

def pg_errorname_lookup(pgcode):
    """
    Args:
        pgcode(int): A PostgreSQL error code.

    Returns:
        The error name from a PostgreSQL error code as per: https://www.postgresql.org/docs/9.5/static/errcodes-appendix.html
    """

    return pgerrorcodes.lookup(str(pgcode))

def host_metadata(host):
    """Returns a JSON object containing the metadata for all the entities
        residing on a host"""
    try:
        with POOL.getconn() as conn:
            with conn.cursor() as curs:
                curs.execute("""select db_name, data
                from (
                    select db_name, json_array_elements(data->'hosts') host, data
                        from metadata)
                    as foo
                    where trim(foo.host::text, '"') = %s""", (host, ))
                res = curs.fetchall() # Either a list of tuples or empty list
                return res if res else None
    except DatabaseError as dberr:
        logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
        logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
        return None
    finally:
        POOL.putconn(conn)

def update_functional_alias(dnsname, db_name, alias):
    """Updates a dnsname record with its db_name and alias"""
    try:
        with POOL.getconn() as conn:
            with conn.cursor() as curs:
                logging.debug("Updating functional alias record (%s, %s, %s)",
                    dnsname, db_name, alias)
                curs.execute("""update functional_aliases
                set db_name = %s, alias = %s where dns_name = %s""",
                    (db_name, alias, dnsname,))
                logging.debug('DB query: %s', curs.query)
                conn.commit()
                # Return True if the record was updated succesfully
                return curs.rowcount == 1
    except DatabaseError as dberr:
        logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
        logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
        return None
    finally:
        POOL.putconn(conn)

 def exec_sql(self, sql):
     """
     :param sql:
     :return:
     """
     try:
         self.cur.execute(sql)
         results = self.cur.rowcount
     except Exception as e:
         pgError = errorcodes.lookup(e.pgcode)
         raise RuntimeError(pgError)
     return results

 def open_spider(self, spider):
     try:
         self.connector = psycopg2.connect(
             user = self.username,
             database = self.database
         )
         self.cursor = self.connector.cursor()
         print('Conneting to database successfully!')
     except psycopg2.Error as e:
         print(
             'Failed to connect database. Returned: %s'
               % errorcodes.lookup(e.pgcode)
         )
         exit(-1)