Re: reduce number of multiple values to be inserted

On Wed, 30 May 2018 08:10:05 +0800
tango ward <tangoward15(at)gmail(dot)com> wrote:

> curr_pgsql.execute('''
> INSERT INTO student (created, modified, name,
> address, age,
> level )
> VALUES (current_timezone, current_timezone,
> 'Scott', 'Malayan Village', 21, 2),
> (current_timezone, current_timezone,
> 'Ben', 'Malayan Village', 21, 2),
> (current_timezone, current_timezone,
> 'Scott', 'Malayan Village', 21, 2),
>
> (current_timezone, current_timezone,
> 'Andrew', 'Malayan Village', 25, 2),
> (current_timezone, current_timezone,
> 'Larry', 'Malayan Village', 25, 2),
> (current_timezone, current_timezone,
> 'Adam', 'Malayan Village', 25, 2),
>
> (current_timezone, current_timezone,
> 'Elisse', 'Malayan Village', 27, 2),
> (current_timezone, current_timezone,
> 'Xena', 'Malayan Village', 27, 2),
> (current_timezone, current_timezone,
> 'Karen', 'Malayan Village', 27, 2)
>
> I will repeat the same process for 13 villages so that will be 117 of
> values. I would like to know if there's a way to reduce the script?
> This has to be done strictly via script.

Rather than try to cut+paste SQL it may be easier to put the data
into a flat file and have some code spit the SQL out for you:

#!/usr/bin/env perl
########################################################################
# housekeeping
########################################################################
use v5.22;
use YAML::XS qw( Load );

########################################################################
# package variables
########################################################################

my $data
= do
{
# slurp the data from named files or stdin.

local $/;
Load <ARGV>
};

my $prefix = <<'SQL';

/*
* Input data for vx.y.z of student data schema.
*/

insert into student
(
created,
modified,
name,
address,
age,
level
)
values
(
SQL

my $suffix = <<'SQL';
);

/*
* end of input
*/
SQL

########################################################################
# output the SQL
########################################################################

say $prefix;

while( my ( $place, $place_valz ) = each %$data )
{
while( my ( $nums, $namz ) = each %$place_valz )
{
for my $name ( @$namz )
{
say <<"SQL";
(
current_timezone,
current_timezone,
'$name' ,
'$place' ,
'$nums'
)
}
SQL
}
}

say $suffix;

# this is not a module
0
__END__

=head1 NAME

output_sql - dump insert ... values ... from YAML

=head1 SYNOPSIS

Inputs arrive via stdin, literal, or glob-ed paths:

output_sql << /path/to/foobar.yaml;
output_sql /path/to/foobar.yaml;
output_sql /path/to/*.yaml;

gzip -dc < /path/to/bigfile.yaml | output_sql;
gzip -dc < /path/to/bigfile.yaml | output_sql | psql;

Your data file could look like this if you want a single flat file
for all of it:

---
Malayan Village :
21, 2 :
- Ben
- Scott
25, 2 :
- Anderew
- Larry
- Adam
...
Another Village :
...

Or your could break it into chunks using multiple documents within
the YAML file (notice the extra '---'):

---
Malayan Village :
21, 2 :
- Ben
- Scott
25, 2 :
- Anderew
- Larry
- Adam
...
---
Another Village :
...

At which point $data, above, is an array and you get:

for my $chunk ( @$data )
{
while( my ( $place, $place_valz ) = each %$chunk )
{
...
}
}

with the added flexibility of breaking the input data into
multiple files if needed.

--
Steven Lembark 1505 National Ave
Workhorse Computing Rockford, IL 61103
lembark(at)wrkhors(dot)com +1 888 359 3508