Re: reduce number of multiple values to be inserted

[Steven Lembark]

On Wed, 30 May 2018 08:10:05 +0800
tango ward  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::XSqw( Load );


# package variables


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

local $/;
Load 
};

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
lemb...@wrkhors.com+1 888 359 3508

Re: reduce number of multiple values to be inserted

[tango ward]

On Wed, May 30, 2018 at 8:40 AM, Rob Sargent  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.

Re: reduce number of multiple values to be inserted

[Rob Sargent]

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

Re: reduce number of multiple values to be inserted

[Adrian Klaver]

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.kla...@aklaver.com

Re: reduce number of multiple values to be inserted

[Rob Sargent]

> On May 29, 2018, at 6:32 PM, tango ward  wrote:
> 
> On Wed, May 30, 2018 at 8:29 AM, Rob Sargent  > 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.  

Re: reduce number of multiple values to be inserted

[tango ward]

On Wed, May 30, 2018 at 8:29 AM, Rob Sargent  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.

Re: reduce number of multiple values to be inserted

[Rob Sargent]

> On May 29, 2018, at 6: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.
> 
> 
> Thanks,
> J
> 
Is “current_timezone, current_timezone” just a typo?  I think you need to make 
the 117 data lines and load using \copy
 

Re: reduce number of multiple values to be inserted

[tango ward]

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

> On Tuesday, May 29, 2018, tango ward  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

Re: reduce number of multiple values to be inserted

[David G. Johnston]

On Tuesday, May 29, 2018, tango ward  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.

reduce number of multiple values to be inserted

[tango ward]

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