Skip to content

Instantly share code, notes, and snippets.

@TerryE
Created October 26, 2023 12:36
Show Gist options
  • Save TerryE/55e413ce59b40a7233df9d76ad5821e6 to your computer and use it in GitHub Desktop.
Save TerryE/55e413ce59b40a7233df9d76ad5821e6 to your computer and use it in GitHub Desktop.
Use Octopus rest API to download price data for date range
#! /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 );
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment