Hash of Hash to mysql

NothingInCommon has asked for the wisdom of the Perl Monks concerning the following question:

I recently got help on this site on how to create a data structure given a certain data input.

# Code that builds the structure
while ( my $line = <DATA> ) {
    chomp $line;
    
    if ( $line =~ m/^BS:\s+(\S+)/ ) {
        $result{$1} = { %queue };
        %queue = ();
    }
    else {
        my ( $name, $in_percent, $in_num, $out_percent, $out_num ) 
          = split m{ \(| / | of 1024 \| event | of 1024\)}, $line;

        $queue{$name} = {
            in_q_per  => $in_percent,
            in_q_num  => $in_num,
            out_q_per => $out_percent,
            out_q_num => $out_num,
        };
    }
}
[download]

Resulting Data structure output

# Resulting Data structure output
'111_DDD,' => {
                'AAA_AA2_DDD' => {
                       'in_q_per' => '0%',
                       'in_q_num' => '0%',
                       'out_q_per' => '0',
                       'out_q_num' => '0'
              },
                'AAA_AA1_DDD' => {
                       'in_q_per' => '0%',
                       'in_q_num' => '0%',
                       'out_q_per' => '0',
                       'out_q_num' => '0'
              },

              ...etc...
[download]

The code worked perfectly and when you print Data::Dumper it prints it out like it shows above. I have been trying to get this data structure into a mysql database for days with out much luck Can you give me some examples of how I can get it into a table like the one below. Ive already tried a foreach within a foreach but each time the I get stuff like this HASH(0x9c9d628) inserted.

#MYSQL TABLE
BS       queue_name   in_q_per in_q_num  out_q_per out_q_num
------------------------------------------------------------
111_DDD  AAA_AA1_DDD    0%        0          0%         0
111_DDD  AAA_AA2_DDD    0%        0          0%         0
222_DDD  BBB_BB1_DDD    0%        0          0%         0
222_DDD  BBB_BB1_DDD    0%        0          0%         0
333_DDD  CCC_CC1_DDD    0%        0          0%         0
333_DDD  CCC_CC1_DDD    0%        0          0%         0
[download]

__DATA INPUT__
AAA_AA1_DDD (0% / 0 of 1024 | event 0% / 0 of 1024)
AAA_AA2_DDD (0% / 0 of 1024 | event 0% / 0 of 1024)
BS: 111_DDD, QE: QQQ_DDD (additionnal QE)
BBB_BB1_DDD (0% / 0 of 1024 | event 0% / 0 of 1024)
BBB_BB2_DDD (0% / 0 of 1024 | event 0% / 0 of 1024)
BS: 222_DDD, QE: QQQ_DDD (additionnal QE)
CCC_CC1_DDD (0% / 0 of 1024 | event 0% / 0 of 1024)
CCC_CC2_DDD (0% / 0 of 1024 | event 0% / 0 of 1024)
BS: 333_DDD, QE: QQQ_DDD (additionnal QE)
[download]

Comment on Hash of Hash to mysql Select or Download Code

Replies are listed 'Best First'.
Re: Hash of Hash to mysql by graff (Chancellor) on Apr 27, 2009 at 01:31 UTC
A working solution will probably end up looking something like this: my @insert_cols = qw/BS queue_name in_q_per in_q_num out_q_per out_q_n +um/; my @inner_keys = @insert_cols[2..5]; my $dbh = DBI->connect( 'whatever...', 'name', 'pswd' ); my $insert_sth = $dbh->prepare( 'insert into my_table (' . join( ',', @insert_cols ) . ') values +(' . join( ',', ('?') x @insert_cols ) . ') +' ); for my $BS ( keys %HoH ) { for my $queue_name ( keys %{ $HoH{ $BS }} ) { my @nums = @{ $HoH{ $BS }{ $queue_name }} { @inner_keys }; $insert_sth->execute( $BS, $queue_name, @nums ); } } [download] (not tested, but it does compile) However, if there's lot's of data to be inserted, you might want to consider just printing the rows to a tab-delimited text file, and use whatever tool your database server provides for doing bulk inserts from such a file. DBI tends to be very slow with inserts, compared to a compiled utility that is native to the DB server -- we're talking about differences of 10-to-1 or more in wall-clock time. (If it's not a lot of data, it doesn't matter, but when it gets into thousands of rows, you'll really notice the difference.) Note that your perl script could both write the text file and then use "system()" to run the native bulk loader tool on that file, to keep the process fully integrated. It's a matter of replacing the `$sth->execute(...)` with `print TSV join( "\t",...),"\n"` and adding the necessary "open TSV, ...", "close TSV" and "system" calls around the for loop shown above.	[reply] [d/l] [select]
Re: Hash of Hash to mysql by sflitman (Hermit) on Apr 26, 2009 at 21:22 UTC
Please show us your code for inserting into the mysql database. I would assume you're using the DBI, right? When I've had HASH(0x-----) inserted as text it usually meant I was failing to dereference a hash with a % prefix. HTH, SSF	[reply]