Re^7: Perl Database Entries

[poj]

Try this, check the log and if OK un-comment the database commands

#!/usr/bin/perl
use strict;
use JSON;
use DBI;
use Data::Dump 'pp';

my $api_results = q!
[{
  "id":1084,
  "slug":"twitter-sportchek",
  "name":"Twitter - SportChek",
  "provider":{
    "id":44,
    "slug":"twitter",
    "name":"Twitter - Shared",
    "access_type_cd":null,
    "login_method":"spider",
    "launch_timeout":45,
    "capabilities":{
      "has_import":false,
      "has_password_reset":true,
      "has_provision":false,
      "has_saml":false,
      "has_sync":false,
      "unsupported_browsers":[],
      "services":"authenticatable",
      "auth_or_directory":false,
      "has_ou":false
    },
    "icons":{
      "small":"https://am.com/a/images/apps/twitter_sm.png",
      "large":"https://assets.bitium.com/a/images/apps/twitter_lg.png"
+,
      "color_code":"7c8d95",
      "first_letter":"t"
    }
  },
  "configuration":{
    "saml":false,
    "login":"rtchek",
    "url":"https://twier.com",
    "extra":null,
    "install_type":"shared",
    "authentication_type":"browser",
    "launch_method":"get",
    "namespace":["twitter.com"]
  },
  "labels":[],
  "created_at":"2017-06-09T23:22:15.571-07:00",
  "status":"active",
  "key_vault":false,
  "requires_mfa_code":false
},
{
  "id":109291,
  "slug":"rei-spredfast",
  "name":"REI - Spredfast",
  "provider":{
    "id":5561,
    "slug":"spredfast",
    "name":"Spredfast",
    "access_type_cd":null,
    "login_method":"spider",
    "launch_timeout":45,
    "capabilities":{
      "has_import":false,
      "has_password_reset":false,
      "has_provision":false,
      "has_saml":false,
      "has_sync":false,
      "unsupported_browsers":[],
      "services":"authenticatable",
      "auth_or_directory":false,
      "has_ou":false
    },
    "icons":{
      "small":"https://assom/a/images/apps/spredfast_sm.png",
      "large":"https://assets.bitium.com/a/images/apps/spredfast_lg.pn
+g",
      "color_code":"65c6bb",
      "first_letter":"s"
    }
  },
  "configuration":{
    "saml":false,
    "login":"reiteamocial",
    "url":"https://www.spredfast.com",
    "extra":null,
    "install_type":"shared",
    "authentication_type":"browser",
    "launch_method":"get",
    "namespace":["www.spredfast.com"]
  },
  "labels":[],
  "created_at":"2017-06-19T12:33:33.561-07:00",
  "status":"active",
  "key_vault":false,
  "requires_mfa_code":false
}]!;

my $json = new JSON;
my $data = $json->allow_nonref->decode($api_results);
pp $data;

my $dbh;#  connect
my @cols = qw(
  id           app_slug       app_Name 
  provider_id  provider_slug  provider_saml 
  config_saml  config_login   config_url 
  created_date status         key_vault 
  mfa_code     configuration_install );
  
my $cols = join ',',@cols;    
my $sql_insert = "
  INSERT IGNORE INTO apps ( $cols ) 
  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)" ;
#my $sth = $dbh->prepare($sql_insert) or die $dbh->errstr;

my $logfile = 'log.dat';
open LOG,'>',$logfile or die "Could not open $logfile : $!";
print LOG join "\t",@cols,"\n";
my $count;
for (@$data){
  my @row;
  push @row,$_->{'id'};
  push @row,$_->{'slug'};
  push @row,$_->{'name'};
  push @row,$_->{'provider'}{'id'};
  push @row,$_->{'provider'}{'slug'};  
  push @row,$_->{'provider'}{'capabilities'}{'has_saml'};
  push @row,$_->{'configuration'}{'saml'};
  push @row,$_->{'configuration'}{'login'};
  push @row,$_->{'configuration'}{'url'};
  push @row,$_->{'created_at'};
  push @row,$_->{'status'};
  push @row,$_->{'key_vault'};
  push @row,$_->{'requires_mfa_code'};
  push @row,$_->{'configuration'}{'install_type'};
  
  #$sth->execute(@row) or die $sth->errstr; 
  print LOG join "\t",@row,"\n"; 
  ++$count;
}
close LOG;
print "$count records inserted - see $logfile";

poj

[cbtshare]

Thank you