reduce number of multiple values to be inserted

On Tuesday, May 29, 2018, tango ward <tangoward15@gmail.com> wrote:

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.

VALUES and CROSS JOIN might help but you haven't explained the pattern well
enough, if there is one, to know for sure. Frankly, you'll probably spend
more time figuring out the elegant way than just copy-paste-change so
unless you need to leverage this elsewhere I'd say just brute-force it.

David J.

On Wed, May 30, 2018 at 8:21 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tuesday, May 29, 2018, tango ward <tangoward15@gmail.com> wrote:

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.

VALUES and CROSS JOIN might help but you haven't explained the pattern
well enough, if there is one, to know for sure. Frankly, you'll probably
spend more time figuring out the elegant way than just copy-paste-change so
unless you need to leverage this elsewhere I'd say just brute-force it.

David J.

Can you elaborate the idea on brute-forcing this Sir?

Thanks,
J

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 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.

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 Wed, May 30, 2018 at 8:29 AM, Rob Sargent <robjsargent@gmail.com> wrote:

Is “current_timezone, current_timezone” just a typo? I think you need to
make the 117 data lines and load using \copy

Sorry, yeah, it's current_timestamp.

On May 29, 2018, at 6:32 PM, tango ward <tangoward15@gmail.com> wrote:

On Wed, May 30, 2018 at 8:29 AM, Rob Sargent <robjsargent@gmail.com <mailto:robjsargent@gmail.com>> wrote:

Is “current_timezone, current_timezone” just a typo? I think you need to make the 117 data lines and load using \copy

Sorry, yeah, it's current_timestamp.

David and I are suggesting the same thing. You have 117 unique student definitions so you need to explicitly define each of them. That can be in your python code or in a file your python code reads in and generates inserts or simply a psql script which reads the file using \copy tablename from file.

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, May 30, 2018 at 8:40 AM, Rob Sargent <robjsargent@gmail.com> wrote:

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)? ;)

Noted. Thank you Sirs. I love you both.

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