perl - 使用 Perl 的 DBI 批量插入

Question

我正在尝试将数据行批量插入到 postgres 数据库中。

我想我已经将数据填充到一个数组 ref 中。它是使用 perl 脚本从使用 Spreadsheet::BasicReadNamedColmodule 的空格分隔文件中获取的.获取数据的代码是

 $ss = new Spreadsheet::BasicReadNamedCol($xlsFileName) ||
 die "Could not open '$xlsFileName': $!";
 $ss->setColumns(@columnHeadings);

 my @array;

 my $row = 0;
 while (my $data = $ss->getNextRow())
 {
     $row++;

     push @array, "@$data";
 }

下面是数组ref的内容。

Cristan McX  123 W State Street North Aurora IL
William Sch  123 South Third St #367 Geneva IL
Kellie xxx  123 South East St. Gardner IL
John xx  321 Princeton Ct. Frankfort IL
Peter xxxxxxx  123 N Myrtle Avenue Elmhurst IL
Izabella xxx  321 S 3rd St. #367 Geneva IL

我用来执行插入操作的 Perl DBI 代码是:

my $dbh = DBI->connect("DBI:Pg:dbname=greenthumb;host=localhost;    port=5432","","", {'RaiseError' => 1});

my $sth = $dbh->prepare( 'INSERT INTO testtable (?, ?, ?, ?, ?, ?)' );

foreach(@array) { $sth->execute( @{$_} ); }
$sth->finish;

我得到的错误是:

Can't use string ("FirstName LastName BusinessName "...) as an ARRAY ref   while "strict refs" in use at ./f.pl line 38.

Answer 1

不是通用的 DBI 解决方案，但由于您使用的是 PostgreSQL，您还可以使用“COPY”进行高效的批量插入，在从电子表格中抓取时添加每一行:

...
$dbh->do("COPY testtable FROM STDIN");
while ( my $data = $ss->getNextRow ) {
    $dbh->pg_putcopydata(join("\t", @$data) . "\n");
}
$dbh->pg_putcopyend();