Thread: Stream data into Postgres via Perl

Stream data into Postgres via Perl

From

Kevin Old

Date:

29 October 2002, 15:54:39

Hello all,

I'm writing a script that will read pipe delimited data from a text file
and insert various fields into a Postgres table.  Below is some code I'm
trying to optimize:


while (<FHD>) {
    chomp; #removes \n
    chop; #removes trailing pipe

    @line = split(/\|/, $_, 502); #The line has 502 "fields" so
                      #them into an array
    $dbh->do("INSERT INTO cdl_16master VALUES(nextval('cdl_16_seq'),\'" .
join("\',\'",
$line[0],$line[4],$line[5],$line[6],$line[10],$line[11],$line[14],$line[18],$lin
e[22],$line[25]) . "\')");
 $dbh->commit();

} #end while


Just wondering if anyone has a better way of accessing the data in the
array or of storing the few fields I need temporarily until it gets
inserted into the database.

There's a better way to do this, but I'm just not thinking right.....any
suggestions are appreciated.

Thanks,

Kevin
kold@carolina.rr.com

Re: Stream data into Postgres via Perl

From

Garrett Bladow

Date:

29 October 2002, 17:00:46

# A better way would be to prepare you statement only once
# $dbh->do runs a prepare everytime, so that is un-needed processing time for the DBMS


$sql = "INSERT INTO cdl_16master VALUES(nextval('cdl_16_seq'),?,?,?,?,?,?,?,?,?,?)";
$sth = $dbh->prepare($sql);
while (<FHD>) {
       chomp; #removes \n
       chop; #removes trailing pipe

       @line = split(/\|/, $_, 502); #The line has 502 "fields" so
                                     #them into an array
    $sth->execute($line[0],$line[4],$line[5],$line[6],$line[10],$line[11],$line[14],$line[18],$line[22],$line[25]);

    $dbh->commit();
}
$sth->finish();

---- This is what you wrote me ----

:Hello all,
:
:I'm writing a script that will read pipe delimited data from a text file
:and insert various fields into a Postgres table.  Below is some code I'm
:trying to optimize:
:
:
:while (<FHD>) {
:    chomp; #removes \n
:    chop; #removes trailing pipe
:
:    @line = split(/\|/, $_, 502); #The line has 502 "fields" so
:                      #them into an array
:    $dbh->do("INSERT INTO cdl_16master VALUES(nextval('cdl_16_seq'),\'" .
:join("\',\'",
:$line[0],$line[4],$line[5],$line[6],$line[10],$line[11],$line[14],$line[18],$lin
:e[22],$line[25]) . "\')");
: $dbh->commit();
:
:} #end while
:
:
:Just wondering if anyone has a better way of accessing the data in the
:array or of storing the few fields I need temporarily until it gets
:inserted into the database.
:
:There's a better way to do this, but I'm just not thinking right.....any
:suggestions are appreciated.
:
:Thanks,
:
:Kevin
:kold@carolina.rr.com
:
:
:
:---------------------------(end of broadcast)---------------------------
:TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
:

Re: Stream data into Postgres via Perl

From

greg@turnstep.com

Date:

29 October 2002, 18:01:34

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message


The major thing you can do, besides using a prepare, is to not bother
splitting 502 times, but only enough times so that the last field you
are going to use is included. In this case, the highest element you are
using is [25], which is actually the 26th element to be split. Add one
more and 0-25 will be split, while 26 will be one giant field:

  my @line = split(/\|/, $_, 27);

Since you already know exactly which fields you want from the split
result, we can specify those directly like this:

  my @line = (split(/\|/, $_, 27))[0,4,5,6,10,11,14,18,22,25];

The parenthesis around the split call force it to be evaluated first,
and then we slice out the values we need. Now @line holds exactly the
elements you want to add in. Just pass that in to a prepared statement.

You can also remove the chomp and the chop, as the ends of the line are
being discarded anyways. Moving the commit outside of the loop should
help too. Here's the final result:


my $SQL = "INSERT INTO cdl_16master VALUES(nextval('cdl_16_seq'),?,?,?,?,?,?,?,?,?,?";
my $sth = $dbh->prepare($SQL);

while(<FHD>) {
  my @line = (split(/\|/, $_, 27))[0,4,5,6,10,11,14,18,22,25];
  $sth->execute(@line);
}
$dbh->commit();


On a final note, you may want to create tables in the future that
automagically populate columns with sequences like this:

CREATE TABLE cdl_16master (
  myid INTEGER NOT NULL DEFAULT nextval('cd_16_seq'),
  ...
);

The drawback is that you will need to specify the exact columns to be
filled in the INSERT command, but this is really a good practice to
get into anyway.

Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200210291455

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE9vunyvJuQZxSWSsgRAnFKAKD5Fkyl9TzEaRwrNTuX8dqvRa6SCACg3Bzd
pgaJNkoGC2hXvpE23Ko9CaE=
=wtsO
-----END PGP SIGNATURE-----

Re: Stream data into Postgres via Perl

From

Ashish Lahori

Date:

30 October 2002, 02:22:58

Hi,
I think the best way of doing the Insertion is to use the copy command of
postgres. You then have to insert Then nextval manually. this can be done by
replacing and '|' with ',' and save it as .CSV. Open the file in Excel and
insert the intial row as the way you want, i mean the starting index value,
save again as CSV.
If you are using Linux OS, save file (.CSV) as unix fileformat and use the
following command while you are in the postgres command prompt.
\copy table_name from 'filename' using delimiters ',' with null as '';


Hope this will Help you.
rgds
Ashish Lahori
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Kevin Old
Sent: Tuesday, 29 October 2002 11:23 PM
To: beginners@perl.org
Cc: pgsql
Subject: [GENERAL] Stream data into Postgres via Perl


Hello all,

I'm writing a script that will read pipe delimited data from a text file
and insert various fields into a Postgres table.  Below is some code I'm
trying to optimize:


while (<FHD>) {
    chomp; #removes \n
    chop; #removes trailing pipe

    @line = split(/\|/, $_, 502); #The line has 502 "fields" so
                      #them into an array
    $dbh->do("INSERT INTO cdl_16master VALUES(nextval('cdl_16_seq'),\'"
.
join("\',\'",
$line[0],$line[4],$line[5],$line[6],$line[10],$line[11],$line[14],$line[18],
$lin
e[22],$line[25]) . "\')");
 $dbh->commit();

} #end while


Just wondering if anyone has a better way of accessing the data in the
array or of storing the few fields I need temporarily until it gets
inserted into the database.

There's a better way to do this, but I'm just not thinking right.....any
suggestions are appreciated.

Thanks,

Kevin
kold@carolina.rr.com



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Stream data into Postgres via Perl

From

Medi Montaseri

Date:

30 October 2002, 16:30:48

Why you say using Excel is better, note how you just changed a batch
program that
could potentially run at 3:00 AM into an interactive operator needed
task ....

My vote is, stay with the automation, its hard at the begining but pays
later...

Ashish Lahori wrote:

>Hi,
>I think the best way of doing the Insertion is to use the copy command of
>postgres. You then have to insert Then nextval manually. this can be done by
>replacing and '|' with ',' and save it as .CSV. Open the file in Excel and
>insert the intial row as the way you want, i mean the starting index value,
>save again as CSV.
>If you are using Linux OS, save file (.CSV) as unix fileformat and use the
>following command while you are in the postgres command prompt.
>\copy table_name from 'filename' using delimiters ',' with null as '';
>
>
>Hope this will Help you.
>rgds
>Ashish Lahori
>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Kevin Old
>Sent: Tuesday, 29 October 2002 11:23 PM
>To: beginners@perl.org
>Cc: pgsql
>Subject: [GENERAL] Stream data into Postgres via Perl
>
>
>Hello all,
>
>I'm writing a script that will read pipe delimited data from a text file
>and insert various fields into a Postgres table.  Below is some code I'm
>trying to optimize:
>
>
>while (<FHD>) {
>    chomp; #removes \n
>    chop; #removes trailing pipe
>
>    @line = split(/\|/, $_, 502); #The line has 502 "fields" so
>                      #them into an array
>    $dbh->do("INSERT INTO cdl_16master VALUES(nextval('cdl_16_seq'),\'"
>.
>join("\',\'",
>$line[0],$line[4],$line[5],$line[6],$line[10],$line[11],$line[14],$line[18],
>$lin
>e[22],$line[25]) . "\')");
> $dbh->commit();
>
>} #end while
>
>
>Just wondering if anyone has a better way of accessing the data in the
>array or of storing the few fields I need temporarily until it gets
>inserted into the database.
>
>There's a better way to do this, but I'm just not thinking right.....any
>suggestions are appreciated.
>
>Thanks,
>
>Kevin
>kold@carolina.rr.com
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>