Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re: Database processing

by davidrw (Prior)
on Aug 06, 2005 at 01:16 UTC ( #481415=note: print w/replies, xml ) Need Help??


in reply to DBI::st=HASH output

You're already reading the file using DBD::CSV, so reading again w/Text::CSV is redundant. You can (db-independently) get the column names with a SELECT query, and then create another SELECT for the avg's (which you can do in one call). (note: untested)
my $sth = $dbh->prepare("SELECT * FROM results WHERE 1=0"); $sth->execute; my @cols = @{$sth->{NAME}}; my $avgSQL = 'SELECT ' . join(', ', map { "avg($_) as $_" } @cols ) . ' FROM results'; my $avgsHashRef = $dbh->selectrow_hashref($avgSQL); use Data::Dumper; print Dumper $avgsHashRef;

Note that in your code $avg = $dbh->prepare(...) line didn't actually execute the code. Simply doing $avg = $dbh->selectrow_array(...) instead work would have done want you intended.

Replies are listed 'Best First'.
Re^2: Database processing
by DrAxeman (Beadle) on Aug 06, 2005 at 02:14 UTC
    I'm getting an error that I can't figure out:
    SQL ERROR: Bad set function before FROM clause. Execution ERROR: No command found!. $VAR1 = undef;
    Here's the code, just to make sure I'm using it right.
    #!/usr/bin/perl use strict; use warnings; use DBI; #use Text::CSV; use Data::Dumper; # Connect to the database, (the directory containing our csv file( +s)) my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;"); # Associate our csv file with the table name 'results' $dbh->{'csv_tables'}->{'results'} = { 'file' => 'test.csv'}; my $sth = $dbh->prepare("SELECT * FROM results WHERE 1=0"); $sth->execute; my @cols = @{$sth->{NAME}}; my $avgSQL = 'SELECT ' . join(', ', map { "avg($_) as $_" } @cols ) . ' FROM results'; my $avgsHashRef = $dbh->selectrow_hashref($avgSQL); print Dumper $avgsHashRef;
      hmm... print out $avgSQL and make sure it's valid sql. If there's anything funky (/\W/) in the column names they'll need to be escaped.
        I printed $avgSQL and got:
        SELECT avg(PDH_CSV40_EasternDaylightTime_240_) as PDH_CSV40_EasternDay +lightTime_240_, avg(ERWWCOMMUNITIES_Memory_Pages_sec) as ERWWCOMMUNIT +IES_Memory_Pages_sec, avg(ERWWCOMMUNITIES_NetworkInterface_CompaqEthe +rnet_FastEthernetAdapter_Module_BytesTotalPERsec) as ERWWCOMMUNITIES_ +NetworkInterface_CompaqEthernet_FastEthernetAdapter_Module_BytesTotal +PERsec, avg(ERWWCOMMUNITIES_NetworkInterface_MSTCPLoopbackinterface_B +ytesTotalPERsec) as ERWWCOMMUNITIES_NetworkInterface_MSTCPLoopbackint +erface_BytesTotalPERsec, avg(ERWWCOMMUNITIES_NetworkInterface_CompaqE +thernet_FastEthernetAdapter_Module_CurrentBandwidth) as ERWWCOMMUNITI +ES_NetworkInterface_CompaqEthernet_FastEthernetAdapter_Module_Current +Bandwidth, avg(ERWWCOMMUNITIES_NetworkInterface_MSTCPLoopbackinterfac +e_CurrentBandwidth) as ERWWCOMMUNITIES_NetworkInterface_MSTCPLoopback +interface_CurrentBandwidth, avg(ERWWCOMMUNITIES_PhysicalDisk_0C_PCTDi +skTime) as ERWWCOMMUNITIES_PhysicalDisk_0C_PCTDiskTime, avg(ERWWCOMMU +NITIES_PhysicalDisk_1D_PCTDiskTime) as ERWWCOMMUNITIES_PhysicalDisk_1 +D_PCTDiskTime, avg(ERWWCOMMUNITIES_PhysicalDisk_Total_PCTDiskTime) as + ERWWCOMMUNITIES_PhysicalDisk_Total_PCTDiskTime, avg(ERWWCOMMUNITIES_ +PhysicalDisk_Total_AvgDiskQueueLength) as ERWWCOMMUNITIES_PhysicalDis +k_Total_AvgDiskQueueLength, avg(ERWWCOMMUNITIES_Processor_Total_PCTPr +ocessorTime) as ERWWCOMMUNITIES_Processor_Total_PCTProcessorTime, avg +(ERWWDC1_Memory_PagesPERsec) as ERWWDC1_Memory_PagesPERsec, avg(ERWWD +C1_NetworkInterface_IntelPRO_100NetworkConnection_BytesTotalPERsec) a +s ERWWDC1_NetworkInterface_IntelPRO_100NetworkConnection_BytesTotalPE +Rsec, avg(ERWWDC1_NetworkInterface_MSTCPLoopbackinterface_BytesTotalP +ERsec) as ERWWDC1_NetworkInterface_MSTCPLoopbackinterface_BytesTotalP +ERsec, avg(ERWWDC1_NetworkInterface_IntelPRO_100NetworkConnection_Cur +rentBandwidth) as ERWWDC1_NetworkInterface_IntelPRO_100NetworkConnect +ion_CurrentBandwidth, avg(ERWWDC1_NetworkInterface_MSTCPLoopbackinter +face_CurrentBandwidth) as ERWWDC1_NetworkInterface_MSTCPLoopbackinter +face_CurrentBandwidth, avg(ERWWDC1_PhysicalDisk_0C_PCTDiskTime) as ER +WWDC1_PhysicalDisk_0C_PCTDiskTime, avg(ERWWDC1_PhysicalDisk_Total_PCT +DiskTime) as ERWWDC1_PhysicalDisk_Total_PCTDiskTime, avg(ERWWDC1_Phys +icalDisk_Total_AvgDiskQueueLength) as ERWWDC1_PhysicalDisk_Total_AvgD +iskQueueLength, avg(ERWWDC1_Processor_Total_PCTProcessorTime) as ERWW +DC1_Processor_Total_PCTProcessorTime, avg(ERWWNAB_Memory_PagesPERsec) + as ERWWNAB_Memory_PagesPERsec, avg(ERWWNAB_NetworkInterface_Intel825 +5x_basedIntegratedFastEthernet_BytesTotalPERsec) as ERWWNAB_NetworkIn +terface_Intel8255x_basedIntegratedFastEthernet_BytesTotalPERsec, avg( +ERWWNAB_NetworkInterface_MSTCPLoopbackinterface_BytesTotalPERsec) as +ERWWNAB_NetworkInterface_MSTCPLoopbackinterface_BytesTotalPERsec, avg +(ERWWNAB_NetworkInterface_Intel8255x_basedIntegratedFastEthernet_Curr +entBandwidth) as ERWWNAB_NetworkInterface_Intel8255x_basedIntegratedF +astEthernet_CurrentBandwidth, avg(ERWWNAB_NetworkInterface_MSTCPLoopb +ackinterface_CurrentBandwidth) as ERWWNAB_NetworkInterface_MSTCPLoopb +ackinterface_CurrentBandwidth, avg(ERWWNAB_PhysicalDisk_0C_PCTDiskTim +e) as ERWWNAB_PhysicalDisk_0C_PCTDiskTime, avg(ERWWNAB_PhysicalDisk_T +otal_PCTDiskTime) as ERWWNAB_PhysicalDisk_Total_PCTDiskTime, avg(ERWW +NAB_PhysicalDisk_Total_AvgDiskQueueLength) as ERWWNAB_PhysicalDisk_To +tal_AvgDiskQueueLength, avg(ERWWNAB_Processor_Total_PCTProcessorTime) + as ERWWNAB_Processor_Total_PCTProcessorTime, avg(ERWWSEARCH_Memory_P +agesPERsec) as ERWWSEARCH_Memory_PagesPERsec, avg(ERWWSEARCH_NetworkI +nterface_BroadcomNetXtremeGigabitEthernetDriver_BytesTotalPERsec) as +ERWWSEARCH_NetworkInterface_BroadcomNetXtremeGigabitEthernetDriver_By +tesTotalPERsec, avg(ERWWSEARCH_NetworkInterface_IntelPRO_100NetworkCo +nnection_BytesTotalPERsec) as ERWWSEARCH_NetworkInterface_IntelPRO_10 +0NetworkConnection_BytesTotalPERsec, avg(ERWWSEARCH_NetworkInterface_ +MSTCPLoopbackinterface_BytesTotalPERsec) as ERWWSEARCH_NetworkInterfa +ce_MSTCPLoopbackinterface_BytesTotalPERsec, avg(ERWWSEARCH_NetworkInt +erface_BroadcomNetXtremeGigabitEthernetDriver_CurrentBandwidth) as ER +WWSEARCH_NetworkInterface_BroadcomNetXtremeGigabitEthernetDriver_Curr +entBandwidth, avg(ERWWSEARCH_NetworkInterface_IntelPRO_100NetworkConn +ection_CurrentBandwidth) as ERWWSEARCH_NetworkInterface_IntelPRO_100N +etworkConnection_CurrentBandwidth, avg(ERWWSEARCH_NetworkInterface_MS +TCPLoopbackinterface_CurrentBandwidth) as ERWWSEARCH_NetworkInterface +_MSTCPLoopbackinterface_CurrentBandwidth, avg(ERWWSEARCH_PhysicalDisk +_0CD_PCTDiskTime) as ERWWSEARCH_PhysicalDisk_0CD_PCTDiskTime, avg(ERW +WSEARCH_PhysicalDisk_Total_PCTDiskTime) as ERWWSEARCH_PhysicalDisk_To +tal_PCTDiskTime, avg(ERWWSEARCH_PhysicalDisk_Total_AvgDiskQueueLength +) as ERWWSEARCH_PhysicalDisk_Total_AvgDiskQueueLength, avg(ERWWSEARCH +_Processor_Total_PCTProcessorTime) as ERWWSEARCH_Processor_Total_PCTP +rocessorTime, avg(ERWWSQLWEB_Memory_PagesPERsec) as ERWWSQLWEB_Memory +_PagesPERsec, avg(ERWWSQLWEB_NetworkInterface_BroadcomNetXtremeGigabi +tEthernetDriver_BytesTotalPERsec) as ERWWSQLWEB_NetworkInterface_Broa +dcomNetXtremeGigabitEthernetDriver_BytesTotalPERsec, avg(ERWWSQLWEB_N +etworkInterface_MSTCPLoopbackinterface_BytesTotalPERsec) as ERWWSQLWE +B_NetworkInterface_MSTCPLoopbackinterface_BytesTotalPERsec, avg(ERWWS +QLWEB_NetworkInterface_BroadcomNetXtremeGigabitEthernetDriver_Current +Bandwidth) as ERWWSQLWEB_NetworkInterface_BroadcomNetXtremeGigabitEth +ernetDriver_CurrentBandwidth, avg(ERWWSQLWEB_NetworkInterface_MSTCPLo +opbackinterface_CurrentBandwidth) as ERWWSQLWEB_NetworkInterface_MSTC +PLoopbackinterface_CurrentBandwidth, avg(ERWWSQLWEB_PhysicalDisk_0CD_ +PCTDiskTime) as ERWWSQLWEB_PhysicalDisk_0CD_PCTDiskTime, avg(ERWWSQLW +EB_PhysicalDisk_Total_PCTDiskTime) as ERWWSQLWEB_PhysicalDisk_Total_P +CTDiskTime, avg(ERWWSQLWEB_PhysicalDisk_Total_AvgDiskQueueLength) as +ERWWSQLWEB_PhysicalDisk_Total_AvgDiskQueueLength, avg(ERWWSQLWEB_Proc +essor_Total_PCTProcessorTime) as ERWWSQLWEB_Processor_Total_PCTProces +sorTime FROM results
        It looks OK to me, but then again, I'm a novice at SQL.
        I do a lot of preprocessing to the csv files before I run them through the perl script. I remove spaces, , , (, ), etc... the list above has all column names in it. I'm wondering if this should be looped? These csv files will average about 30 columns and upto 6000 rows. Doing all the average calculations in one command may be a little taxing on my system.

        PS. I don't need the data after the average is calculated. Just the column name and the average. Also, I don't need the first column, that can just go away.
      Your script works for me (maintainer of DBD::CSV and SQL::Statement) with a smaller dataset. Could you let me know which version of SQL::Statement you have? If you have anything ealier than 1.14, you should probably upgrade.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://481415]
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (3)
As of 2017-12-17 09:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What programming language do you hate the most?




















    Results (462 votes). Check out past polls.

    Notices?