DBI::CSV using a variable to request columns

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

Hello Monks. I would like to use a variable "$columns" to describe the columns I wish to extract. Perl will not allow me to store, for example, $column = "sid = $row->{sid}"; because it demands that I declare $row

I have pasted the troublesome code below. Thank you, in advance for your perls!

use strict;
use warnings;
use Text::CSV;
use DBI;
use Data::Dumper;
  
# Connect to the database, (the directory containing our csv file(s))
my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
      f_dir                       => ".",
      f_ext                       => ".csv",
      f_encoding                  => "utf-8",
      RaiseError                => 1,
      }) or die $DBI::errstr;

  
# Output using sql query
my $query = (qq(select * from newCsv.csv WHERE gender ='male' AND geno
+type ='a' ));
# my $columns = "sid = $row->{sid} \tgender = $row->{gender}\n"; # per
+l is seeking declaration of $row
my $queryResult;
my $sth = $dbh->prepare($query);
$sth->execute;

while (my $row = $sth->fetchrow_hashref) {
my $col= "sid = $row->{sid} \tgender = $row->{gender}\n";  # cannot ch
+ange this to my $col=$columns
$queryResult = $queryResult.$col; 
}    

$sth->finish();
$dbh->disconnect(); 
print "Result \n\n$queryResult";
[download]

Comment on DBI::CSV using a variable to request columns Download Code

Replies are listed 'Best First'.
Re: DBI::CSV using a variable to request columns by chacham (Prior) on May 20, 2016 at 14:30 UTC
select from newCsv.csv WHERE gender ='male' AND genotype ='a'* As an aside: Please specify the column names instead of select . Specifying column names protects against column additions, subtractions, and changes in column order. Furthermore, it is self-documenting. "select " ought to be relegated to exists() and ad hoc queries.	[reply]
Re^2: DBI::CSV using a variable to request columns by Sandy_Bio_Perl (Beadle) on May 20, 2016 at 14:42 UTC
Thank you chacham. The select * is working without any problems. I will paste the headings somewhere below for reference. My problem is that I cannot store the headings "sid = $row->{sid} \tgender = $row->{gender}\n"; into the variable $columns and then use that variable to describe the columns I wish to see in the output Headings <code>sid name genotype gender tgroup <\code> Many thanks for responding	[reply]
Re^2: DBI::CSV using a variable to request columns by LanX (Saint) on May 22, 2016 at 15:34 UTC
I think we had this discussion already... > against column additions, subtractions, and changes I wonder how is this relevant if one uses `->fetchrow_hashref` ? (like the OP does) update Re^2: Database connection issue Cheers Rolf _{(addicted to the Perl Programming Language and ☆☆☆☆ :) Je suis Charlie!}	[reply] [d/l]
Re^3: DBI::CSV using a variable to request columns by chacham (Prior) on May 23, 2016 at 13:26 UTC
I think we had this discussion already... I do not see how that changes the advice. There are ways to avoid some of the errors (disregarding if that is good practice), but the self-documenting part is still true. I wonder how is this relevant It isn't. Hence the introductory, "As an aside".	[reply]
Re^4: DBI::CSV using a variable to request columns by LanX (Saint) on May 23, 2016 at 13:44 UTC
Re^5: DBI::CSV using a variable to request columns by chacham (Prior) on May 23, 2016 at 16:20 UTC
Re: DBI::CSV using a variable to request columns by stevieb (Canon) on May 20, 2016 at 14:49 UTC
You're trying to populate `$columns` before you've even populated `$row` out of the DB. You need to do this within the `while()` loop (perhaps with a count variable so that it only runs on the first pass). Also, you declare `$queryResult`, but then later use it in a very strange way. What is your goal for `$queryResult`? Is it to add the headers, then append details from every row?	[reply] [d/l] [select]
Re^2: DBI::CSV using a variable to request columns by Sandy_Bio_Perl (Beadle) on May 20, 2016 at 15:01 UTC
I suspect my novice skills are beginning to show! I want the user, probably via a web based form, to be able to create a query e.g. select records from men infected with viral genotype a and display user defined results such as subject ID, gender, test group, viral dna levels etc (e.g. the columns) I thought that I might display $queryResult in a web page.	[reply]
Re^3: DBI::CSV using a variable to request columns by soonix (Chancellor) on May 20, 2016 at 19:51 UTC
DBI can give you the column names, but only after the `->execute`, you can find this e.g. in the doc's section on Statement Handle Attributes. I thought that I might display $queryResult in a web page. For this purpose, you could use HTML::Table::FromDatabase, which could replace your `while` loop with a simple call to `my $table = HTML::Table::FromDatabase->new( -sth => $sth, -html => 'escape', -border => 1, ); print "<html><head><title>this is the page title</title></head><body>< +h1>this is the header</h1>"; print $table->getTable; print "this is the end</body></html>";` [download]	[reply] [d/l] [select]
Re^4: DBI::CSV using a variable to request columns by Sandy_Bio_Perl (Beadle) on May 20, 2016 at 21:42 UTC
Re^3: DBI::CSV using a variable to request columns by Corion (Patriarch) on May 20, 2016 at 20:00 UTC
Also see Querylet and Querylet::CGI for quickly running SQL queries and displaying tabular results.	[reply]
Re^4: DBI::CSV using a variable to request columns by Sandy_Bio_Perl (Beadle) on May 22, 2016 at 14:53 UTC
Re: DBI::CSV using a variable to request columns by Anonymous Monk on May 20, 2016 at 16:22 UTC
If I understand you, you want arbitrary columns from a database query. I think I would approach this by using an array of column names rather than a scalar containing (more or less) the code to be executed. That is, instead of your `$columns`, do something like `my @columns = qw{ sid gender };`, and then accumulate your `$queryResult` using something like $queryResult .= join( "\t", map { "$_ = $row->{$_}" } @columns ) . "\n"; Note that I have not worried about validation of column names, but if you are getting them from user input you will need to.	[reply] [d/l] [select]
Re^2: DBI::CSV using a variable to request columns by Sandy_Bio_Perl (Beadle) on May 20, 2016 at 16:56 UTC
Thanks for your help	[reply]

update