Use Octopus rest API to download price data for date range

octopus_agile_download.pl

#! /usr/bin/perl
#
# Use the Octopus rest API to download bulk price data for a given date range from the
# api.octopus.energy service and load this into a denomalised DB table, `octopus` which contains
# a dts primay key and 48  (numeric) slot fields.  Note that the JSON response can contain
# part day dats (e.g the lastest day's prices stop at midnight UTC+1), so the DB load algo uses
# an UPDATE only setting the supplied fields if there are less than 48.
#
#
use strict; use warnings; use v5.10;
use LWP::Simple;
use JSON::XS;
use DBI;
use DateTime;
use Data::Dumper qw(Dumper); # Debug only

#
# Process the context including the two arguments: Starting day, and # of days;
#
die "Args are <from> <days>, from in YYYY-MM-DD format\n" if $#ARGV != 1;
my ($from,$to) = @ARGV;
my ($yyyy,$mm,$dd) = $from =~ /^(202\d)-(\d\d)-(\d\d)$/ or die "Invalid From Date";
# reformat to and from as JSON DateTime format
$from      = DateTime->new(year=>$yyyy, month=>$mm, day=>$dd, time_zone=>'UTC');
$to        = $from->clone->add( days => $to )  or die "Invalid #Days";
$from      = $from->strftime('%FT%RZ');  # YYYY-MM-DDThh:mmZ
$to        = $to->strftime('%FT%RZ');    # YYYY-MM-DDThh:mmZ
my $tariff = "AGILE-FLEX-22-11-25";
my $GSP    = "B";
my $rates  = "electricity-tariffs/E-1R-$tariff-$GSP/standard-unit-rates";
my $url    = "https://api.octopus.energy/v1/products/$tariff/$rates/" .
             "?period_from=${from}&period_to=${to}";
#
# The Octopus unit-rates get returns a JSON structure which is parsed to extra the
# half-hourly by day prices into the %readings HoL. Missing prices for any day are undef.
#
say "Fetching Octopus data from $from until $to.";
my $ret; my %readings;
while ($url) {
    $ret = get $url  // die "could not get $url";
    my $res = decode_json $ret;
    $url = $res->{next};
    foreach my $r (@{$res->{results} }) {
        my ($d, $hh, $mm) = ($r->{valid_from} =~ /(.{10})T(..):(..)./);
        $readings{$d} = [(undef) x 48] unless exists $readings{$d};
        my $slot =  2*$hh + ($mm eq '00' ? 0 : 1);
        $readings{$d}[$slot] = $r->{value_exc_vat};
    }
}
my @days = sort keys %readings;
my @dayRange = @days[0,$#days];
say "Loading $#days dats readings into Database";
#
# Connect to MySQL database, and get a list of the days which already have rows within the
# date range, then convert this to the lookup hash %dbd.  This is used when looping around
# the readings by day.  Normally the row for this dts doesn't exist, so a straight INSERT
# can be executed, but otherwise build the corresponding update must be generated.
#
my $dbh    = DBI->connect("DBI:mysql(PrintError=>0,RaiseError=>1):$ENV{MYSQL_DB}",$ENV{USER},'');
my $dbDays = $dbh->selectcol_arrayref("SELECT dts FROM octopus WHERE dts BETWEEN ? AND ?", {}, @dayRange);
my %dbDays = map {$_ => 1} @$dbDays;   # Create hash of dts which already exist;

# Now loops around the fetched daily prices; insert new days, but update any existing ones.

my $rsi = $dbh->prepare("INSERT INTO octopus VALUES (?" . ",?" x 48 .")");

foreach my $d (@days) {
    my $rv = $readings{$d};        # get 48 half-hour prices (some may be undef)
    
    unless (exists $dbDays{$d}) {  # do straight insert.  This is the main path case.
        unshift @$rv, $d;          # insert dts at head of BV array
        $rsi->execute(@$rv);       # and execute the prepared insert.

    } else  {                      # a row can already exist. If so then do an update.
        my @flds; my @bv;
        
        foreach my $i (0..$#$rv) { # this is rarely done so doesn't need to be efficient
            next unless defined $rv->[$i];  # skip undef entries in day's readings;
            push @flds, sprintf( "p%02d=?", $i);
            push @bv, $rv->[$i];
        }
        push @bv, $d;             # insert dts at end of BV array
        my $sql = "UPDATE octopus SET " . join(',', @flds) . " WHERE dts=?";
        $dbh->do( $sql, {}, @bv );
    }
}