in reply to Select DB data into CSV file

That patch thing from perlancar is *wild* and I'm glad to learn of it. It might be best for your problem.

DBIx::Class is a little bit of a mismatch because CSV is closer to a View, as I see it, than a data format and to get DBIC to operate at that level, per row CSV writing to strings instead of a stream, it will have a large performance penalty. You could do something like–

my $rs = $schema->resultset("UrTable")->search({ ur => "search" });

my $csv = Text::CSV_XS->new({ binary => 1, auto_diag => 1 })
    or die "Cannot use CSV: ", Text::CSV_XS->error_diag;

my @headers = $rs->result_source->columns;
$csv->say( \*STDOUT, [ @headers ] );

my $cursor = $rs->cursor;
while ( my @vals = $cursor->next )
{
    $csv->say( \*STDOUT, [ @vals ] );
}

[download]

It's easy enough to drop in some processing in a as_cvs method in your App::Schema::Result::UrTable package but it would be an expensive way to get it. If I were going to break down and do it, I'd do a custom component class to inherit from; I've done this for JSON. You could do something more meta in App::Schema::ResultSet::UrTable… but I'm not sure what that would look like. I would look at the source of DBIx::Class::ResultClass::HashRefInflator as the basis for ideas.

Replies are listed 'Best First'.
Re^2: Select DB data into CSV file
by Tux (Canon) on Dec 21, 2018 at 07:24 UTC

    If performance is an issue, do not take a copy of the list every time, use the reference:

    my $rs = $schema->resultset ("UrTable")->search ({ ur => "search" });

my $csv = Text::CSV_XS->new ({ binary => 1, auto_diag => 1, eol => "\r
+\n" })
    or die "Cannot use CSV: ", Text::CSV_XS->error_diag;

my @headers = $rs->result_source->columns;
$csv->print (*STDOUT, \@headers);
#                     ^

my $cursor = $rs->cursor;
while (my @vals = $cursor->next) {
    $csv->print (*STDOUT, \@vals);
#                         ^
    }

    [download]

    I should also note that say has runtime overhead. For top-speed, use the eol attribute and print.

    *STDOUT does not require the backslach in Text::CSV_XS (I just verified with 5.8.1 and Text::CSV_XS-1.12).

    Performance-wise it is a shame that DBIx::Class::Cursor's next (got to DBIx::Class::ResultSet's next for the actual documentation) uses DBI's fetchrow_array instead of the faster and more efficient fetchrow_arrayref (or it's alias fetch), which could then directly be passed to $csv->print.


    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom