Thread: reduce number of multiple values to be inserted

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.

> On May 29, 2018, at 6:10 PM, tango ward <tangoward15@gmail.com> wrote:
>
>
> Hi,
>
> I am working on inserting multiple values for a table. I need to insert 3 values of data for each age of the students
fromthe same village. It will be 3 different ages of student per village. 
>
> My sample code:
>
>
> 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
toreduce the script? This has to be done strictly via script. 
>
>
> Thanks,
> J
>
Is “current_timezone, current_timezone” just a typo?  I think you need to make the 117 data lines and load using \copy

On 05/29/2018 05:10 PM, tango ward wrote:
> 
> Hi,
> 
> I am working on inserting multiple values for a table. I need to insert 
> 3 values of data for each age of the students from the same village. It 
> will be 3 different ages of student per village.
> 
> My sample code:
> 
> 
> 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.

Not sure where you are pulling the data from and how it is ordered, but 
an outline:

data_input (Assuming sorted by village and then age)

for village in data_input:
    for age in village:
         curr_pgsql.execute('''
         INSERT INTO student (created, modified, name,
                                       address, age, level)
                  VALUES(current_timezone, current_timezone,
                         %s, %s, %s, 2)''', (name, village, age))

> 
> 
> Thanks,
> J
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 05/29/2018 06:36 PM, Adrian Klaver wrote:
> On 05/29/2018 05:10 PM, tango ward wrote:
>>
>> Hi,
> Not sure where you are pulling the data from and how it is ordered, 
> but an outline:
>
> data_input (Assuming sorted by village and then age)
>
> for village in data_input:
>     for age in village:
>          curr_pgsql.execute('''
>          INSERT INTO student (created, modified, name,
>                                       address, age, level)
>                  VALUES(current_timezone, current_timezone,
>                         %s, %s, %s, 2)''', (name, village, age))
>
>>
>>
>> Thanks,
>> J
>>
>
>
You might need random(name)? ;)

On Wed, 30 May 2018 08:10:05 +0800
tango ward <tangoward15@gmail.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@wrkhors.com                                    +1 888 359 3508