Using an AoA from Text::CSV_XS is very inefficient. Please read the examples that show you how to do this streaming.

I should update that section to make use of the relative recent undef_str attribute.

Sorry, sloppy writing. I meant this:

# using the csv function, all in memory
csv (out => "foo.csv", in => $dbh->selectall_arrayref ($sql));
[download]



The way forward always starts with a minimal test.

This is fine until your table outgrows your memory: selectall_arrayref will read all rows into memory and then pass it to csv (), whereas using a fetch handle has virtually no memory overhead.

my $sth = $dbh->prepare ("select * from foo");
$sth->execute;
csv (out => "foo.csv", in => sub { $sth->fetch }, undef_str => "\\N");
[download]


Enjoy, Have FUN! H.Merijn

Hi 1nickt,

Thanks for the mention. I linked DBIx::CSV to Text::Table::CSV instead of Text::CSV_XS directly because DBIx::CSV is just a special case of DBIx::TextTableAny to output to various forms of text tables, which CSV is one of.

I wrote a short blog post on DBIx::CSV here and noted that you can also do something like:

use Text::CSV;
my $csv = Text::CSV->new;

...
$csv->print(\*STDOUT, $sth->{NAME});
while (my $row = $sth->fetchrow_arrayref) {
     $csv->say(\*STDOUT, $row);
}
[download]

which doesn't require any additional module and not much extra code anyway. But since you do not use DBI directly, that's a bit of a problem as well.

I haven't looked into DBIx::Class to know if it's easily extensible to do what you want. But how about a patch-based solution like Patch::DBI::WriteCSV (use this if the link doesn't exist yet). As long as something like DBIx::Class calls one of DBI's fetchrow_{array,arrayref,hashref} or fetchall_{arrayref,hashref}, it will work. I've tested it with the simple sample database from the DBIx::Class documentation. This code:

Patch::DBI::WriteCSV->import;
my @all_artists = $schema->resultset('Artist')->all;
Patch::DBI::WriteCSV->unimport;
[download]

artistid,name
1,Enya
2,"Mariah Carey"

If you don't want to depend on my module, you can just use the underlying code and integrate that to your own code base. As for the "one-person ecosystem" remark: there's a line between NIH syndrome and "not finding what I want/need on CPAN" and I can say that I am still on the second camp. But I can't help if what I want/need is not on CPAN and thus write a module for it :-) I do use other people's modules. Of my 1512 distributions on CPAN, I depend on 610 modules written by others.

% lcpan dists --author PERLANCAR | xargs lcpan deps | grep -v PERLANCAR | wc -l

LOL perlancar, you are amazing. I didn't expect to get a new distro out of my question! Thanks for your reply which I will study this evening, very kind.

( P.S. The blog posted you linked to was how I found DBIx::CSV in the first place ;-) )



The way forward always starts with a minimal test.

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.

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

Mysql's "INTO OUTFILE"

SELECT ... INTO OUTFILE writes to a file on the database server's filesystem (if permitted), whereas a classic SELECT + Text::CSV as proposed in Re^3: Select DB data into CSV file writes to the client's filesystem. And SELECT ... INTO OUTFILE is not portable across databases.

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

Sure, but the original post mentions the team is considering it, just with the unsafe assumption that TAB characters won't be in the data.