Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Re^3: Creating a csv from two database tables

by linuxer (Deacon)
on Apr 06, 2009 at 16:43 UTC ( #755785=note: print w/ replies, xml ) Need Help??


in reply to Re^2: Creating a csv from two database tables
in thread Creating a csv from two database tables

For creating the objects hash, I would rely upon autovivification.

And I would create a plain hash-of-hash of the data and create the csv format afterwards.

here's my example, as I tried to figure your problem without database access (So I used DBD::CSV to have a "database"). It's just my hack, needs some modifications before production usage, and I'm sure there's a more efficient/stylish way to do that ;o)

#! /usr/bin/perl use strict; use warnings; use DBI; my $dbh = DBI->connect( 'DBI:CSV:f_dir=.' ) or die "cannot connect: " +. $DBI::errstr; sub _get_param_fieldnames { my %field; my $sth = $dbh->prepare( 'SELECT param_id,name FROM x_param' ) or die "cannot prepare: " . $dbh->errstr; $sth->execute or die $dbh->errstr; while ( my $row = $sth->fetchrow_arrayref ) { $field{ $row->[0] } = $row->[1]; } $sth->finish; return \%field; } sub _get_object_entries { my %object; my $sth = $dbh->prepare( 'SELECT * FROM x_object' ) or die "cannot prepare: " . $dbh->errstr; $sth->execute or die $dbh->errstr; while ( my $row = $sth->fetchrow_arrayref ) { $object{ $row->[0] }->{ $row->[1] } = $row->[2]; } $sth->finish; return \%object; } # output stuff { my $field_by = _get_param_fieldnames(); my $object_by = _get_object_entries(); local $, = ','; local $\ = "\n"; # create csv output; check for proper modules print 'OBJECT_ID', map { $field_by->{$_} } sort { $a <=> $b } key +s %$field_by; for my $oid ( keys %$object_by ) { my @param_values; for my $pid ( sort { $a <=> $b } keys %$field_by ) { push @param_values, exists $object_by->{$oid}->{$pid} ? $o +bject_by->{$oid}->{$pid} : ''; } print $oid, @param_values; } } $dbh->disconnect; __END__


Comment on Re^3: Creating a csv from two database tables
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://755785]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (5)
As of 2014-12-20 21:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (99 votes), past polls