Обсуждение: perl and insert

Поиск
Список
Период
Сортировка

perl and insert

От
Hrishikesh Deshmukh
Дата:
Hi All,

Anybody knows how to use perl dbi to read a file line by line and
insert into db!
The books which i have tell you exclusively on running queries.

Thanks,
Hrishi

Re: perl and insert

От
Rich Doughty
Дата:
On 17 May 2005, Hrishikesh Deshmukh wrote:
> Hi All,
>
> Anybody knows how to use perl dbi to read a file line by line and
> insert into db!
> The books which i have tell you exclusively on running queries.

it depends on what you need to achieve, but a good place to start would be
something like:

   while (my $line = <FILE>)
   {
      $dbh->do ('INSERT INTO table (line) VALUES (?)', undef, $line);
   }

Where FILE is your open filehandle, and $dbh is your DBI connection, and
you've modified the SQL as necessary.

If performance is an issue, you may want to try this (although the
performance gains depend on database you're using)

   my $st = $dbh->prepare ('INSERT INTO table (line) VALUES (?)');

   while (my $line = <FILE>)
   {
      $st->execute ($line);
   }




  - Rich Doughty

Re: perl and insert

От
Harald Fuchs
Дата:
In article <20050517161956.GA23179@europa.cosmos.opusvl.com>,
Rich Doughty <rich@opusvl.com> writes:

> On 17 May 2005, Hrishikesh Deshmukh wrote:
>> Hi All,
>>
>> Anybody knows how to use perl dbi to read a file line by line and
>> insert into db!
>> The books which i have tell you exclusively on running queries.

> it depends on what you need to achieve, but a good place to start would be
> something like:

>    while (my $line = <FILE>)
>    {
>       $dbh->do ('INSERT INTO table (line) VALUES (?)', undef, $line);
>    }

> Where FILE is your open filehandle, and $dbh is your DBI connection, and
> you've modified the SQL as necessary.

> If performance is an issue, you may want to try this (although the
> performance gains depend on database you're using)

>    my $st = $dbh->prepare ('INSERT INTO table (line) VALUES (?)');

>    while (my $line = <FILE>)
>    {
>       $st->execute ($line);
>    }

If there are many lines in the file, Hrishikesh might be better off
using COPY instead of INSERT.  "perldoc DBD::Pg" says how to do that.