Created
October 26, 2023 12:36
-
-
Save TerryE/55e413ce59b40a7233df9d76ad5821e6 to your computer and use it in GitHub Desktop.
Use Octopus rest API to download price data for date range
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#! /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