LinuxQuestions.org - Perl script/mysql select query from a file

- Programming (http://www.linuxquestions.org/questions/programming-9/)

- - Perl script/mysql select query from a file (http://www.linuxquestions.org/questions/programming-9/perl-script-mysql-select-query-from-a-file-557859/)

mcdrr

05-30-2007 06:19 PM

Perl script/mysql select query from a file

Hello everyone, wondering if someone could help me out with the script I am trying to write. I am still pretty new to perl but I can somewhat get myself through some simple stuff. I am however stuck with the script below. I am trying to write a script where I will feed it a file with about 100+ entries and want to create a select statement where the file defined can be set as a varible in the SQL query.

For example below I am playing around with the MYSQL db. I am doing a select * from user where user = 'root';(except when I feed it via the ARGV)

What I was doing just to test/until I get the script working is I have created a file called sqlquery.txt with one entry in the file that says root. Now I when I run the script along with the ARGV of the sqlquery.txt file I get no error nor do I get and results. (./db1.pl sqlquery.txt)

Wondering if someone can please help me with the following
1) not sure if I am supposed to loop through the my @q1 = <FILE> so that all 100+ entries get queried. If I do need this how/where should this be done at?
2) How would I implement passing a variable to the $query. my $query = 'SELECT * FROM user where user = $VARIABLE/ARGV-FILE

#!/usr/bin/perl
use DBI;

my $username = 'root';
my $dsn = 'DBI:mysql:mysql';
my $conn = DBI->connect($dsn,$username) || die "Could not connect to the database";

open(FILE,$ARGV[0]) || die "This script requires a file to be defined along with the script";
my @q1 = <FILE>;

my $query = 'SELECT * FROM user where user = \'$q1\'';
my $query_prep = $conn->prepare($query) || die "Error preparing query" . $conn->errstr;

$query_prep->execute || die "Error executing query" . $query_prep->errstr;
my @results;
while (@results = $query_prep->fetchrow_array()) {
foreach(@results) {
print "$_\n";
}
}

thanks so much for the help in advance everyone.

mcdrr

05-30-2007 07:41 PM

This is another way I have tried it with no luck, not sure what the heck I am doing wrong. I am passing the $console variable to the query but I get no error or results. Again just to be clear what I am trying to do it send a list of entries from a file and feed it to the mysql query.

thanks again for the help

#!/usr/bin/perl

use strict;
use DBI;

my $username = 'root';
my $dsn = 'DBI:mysql:mysql';
my $conn = DBI->connect($dsn,$username) || die "Could not connect to the database";
my $console = $ARGV[0];

my $query = 'SELECT * FROM user where user = \'$console\'';
my $query1 = $conn->prepare($query) || die "Error preparing query" . $conn->errstr;

$query1->execute || die "Error executing query" . $query1->errstr;
print "$query";

my @results;
while (@results = $query1->fetchrow_array()) {
foreach(@results) {
print "$_\n";
}
}

if ($query1->rows == 0 ) {
print "No Records", "\n";
}

chrism01

05-31-2007 02:34 AM

user names are strings, so you should quote them properly using the built-in DBI method ->quote:
(http://search.cpan.org/~timb/DBI-1.56/DBI.pm#quote)

where user = ".$conn->quote($console)." ";

If you've got a bunch of names to check, you can either loop through them doing one at a time, or you can quote the lot and use the SQL keyword 'IN' eg:

where user IN (<your csv list of quoted names>)

In normal SQL that'd look like:
WHERE user IN ('user1', 'user2')

mcdrr

06-01-2007 08:41 PM

Chrism thanks for the help/pointer. I have followed your advice but now I am running into another small problem. Below is the new code. While testing the output of the $query I see that the the actually file is being read currently but since I am using the $_ in the mysql_quote it is appending the \n to each query that is being executed. Is there anyway to resolve that, if so how? Also is there a better way to do this if so I am open to suggestions (with example please).

For example in the file I have user1, user2, user3...etc each user is separated by a newline. So when I execute the query the results of the $query are as follow.

SELECT * FROM user where user = 'user1\n' SELECT * FROM user where user = 'user2\n'
SELECT * FROM user where user = 'user3\n'

#!/usr/bin/perl
use DBI;

# Step1 - create a connection object this will grant you access to the DB
my $username = 'root';
my $dsn = 'DBI:mysql:mysql';
my $conn = DBI->connect($dsn,$username) || die "Could not connect to the database";
#my $console = $ARGV[0];

# Step 2 - define the query you want to perform.
open(han1, "dbquery.txt") || die "Could not open file";
my @console = <han1>;
foreach (@console) {
my $query = "SELECT * FROM user where user = ".$conn->quote("$_")." ";
my $query1 = $conn->prepare($query) || die "Error preparing query" . $conn->errstr;
print "$query";
$query1->execute || die "Error executing query" . $query1->errstr;
my @results;
while (@results = $query1->fetchrow_array()) {
foreach(@results) {
print "$_\n";
}
}
}

THANKS FOR THE HELP AGAIN EVERYONE

msantinho

06-03-2007 08:28 AM

I'm not entirely sure on what you're trying to do. Anyway, here's my guess.

NOTE: This is untested code!

Code:

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

use File::Slurp;

use DataDumper;



# 1st, slurp the file

my @lines = read_file('/path/to/file');



# 2nd, setup the connection do the DataBase

my $db_user = q|dbuser|;

my $db_pass = q|dbpassword|;

my $dbname  = q|db_name|;

my $db_host = q|db_host|;



my $dbh = DBI->connect(

        qq|DBI:mysql:database=$db_name;host=$db_host|,

        $user, $pass

) or die(q|Ooopsss... something went wrong with the database connection.|);



$dbh->{RaiseError} = 1;



# 3rd, define the query

my $sql = q|SELECT * FROM tab_user WHERE user = ?|;

# Note the use of the placeholder (?), so, you don't need to escape evil characters



# 4th, prepare the query

my $sth = $dbh->prepare($sql);



# 5th, loop trought the lines and run the query

my @results; # This array will hold the results, if any



foreach my $line (@lines) {



        # execute it...

        $sth->execute($line);



        # ... and fetch the records

        push @results, $sth->fetchall_arrayref();

}



# 6th, dump the results in @results

print Dumper(\@results);

Miguel

mcdrr

06-03-2007 04:25 PM

Thanks for the code Miguel I have made some mods to what you have post in order to get query to loop. Here is what I have

#!/usr/bin/perl
use DBI;
use File::Slurp;
use Data::Dumper;

# 1st, slurp the file
my @lines = read_file('/root/perl/dbquery.txt');

# 2nd, setup the connection do the DataBase
my $db_user = q|root|;
my $db_pass = q|password|;
my $dbname = q|mysql|;
my $db_host = q|localhost|;

my $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$db_host", "$db_user",
"$db_pass", {PrintError => 1, RaiseError => 1, AutoCommit => 1});

foreach my $line (@lines) {
print "The line result is ", "$line\n";
my $sql = q|SELECT * FROM user WHERE user = ?|;
print "The sql_query is ", "$sql\n";
my $query = $dbh->prepare($sql) || die "Error prepraring query";
$query->execute($line);
my @sqlout;
while (@sqlout = $query->fetchrow_array()) {
foreach(@sqlout) {
print "$_";
}
}
}

# 6th, dump the results in @results
print Dumper(@sqlout);

For debugging purposes I am printing both the $line variable and the $sql query variables. When I run ./dbquery.pl I get the following as you can the variables are being read in correctly but not being applied to the query. I have tried a bunch of things but I am not getting anywhere hoping that someone can point me in the right direction. What I am trying to do is a SELECT query againt the MySQL db using a perl script. One of the things that I need to happen though is that when running the query for example [B]SELECT * FROM user WHERE user = "DATA_COMING_FROM_FILE";[\B] the WHERE user part the list of users will come read from a file which contains a list of users.

Here are the results of running the script on the shell.

The line result is user01
The sql_query is SELECT * FROM user WHERE user = ?
The line result is user02
The sql_query is SELECT * FROM user WHERE user = ?
The line result is user03
The sql_query is SELECT * FROM user WHERE user = ?

As you can see the query is SELECT * FROM user WHERE user = ?. For some reason the ? is not being replaced with one of the user0x entries.

Thanks for the help.

msantinho

06-03-2007 05:32 PM

Some comments and modifications on your code:
I'm assuming that your file is something like:

Code:

user1

user2

user3

userN

Code:



# Moved these variables outside the loop

my $sql = q|SELECT * FROM user WHERE user = ?|;

my $sth = $dbh->prepare($sql);



# create an array to store all the results

my @results;



foreach my $line (@lines) {



#   print "The line result is ", "$line\n";

    print qq|Current line says: $line\n|;



# Moved the next line outside the loop. What's the point in

# re-defining a variable with the same value for each line?

#   my $sql = q|SELECT * FROM user WHERE user = ?|;



#   print "The sql_query is ", "$sql\n";

   print q|The sql query is ";

# if you want to replace the ? with the user read from the file...

   my $_sql_ = $sql;

   $_sql_ =~s/\?/$line/gmx; # replace the ? with the username

   print qq|$_sql_\n\n|;



# Moved outside the loop for the same reason

#   my $query = $dbh->prepare($sql) || die "Error preparing query";



#   $query->execute($lines);

   $sth->execute($line);



# ------ Replace this with the line below ------- #

#   my @sqlout;

#   while (@sqlout = $query->fetchrow_array()) {

#      foreach(@sqlout) {

#         print "$_";

#      }

#   }



   push @results, $sth->fetchrow_arrayref();



}



print Dumper(\@results);

---------------------------

Another way to do the same, perhaps faster (if I understand right what you're trying to do).

If you have a file with user names you want to check, and get info, against a MySQL db, you can do this in one go:

Code:

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

use File::Slurp;

use DataDumper;



# 1st, slurp the file

my @lines = read_file('/path/to/file');



# 2nd, create and fill arrays to store the conditions

my @sql_qual;

my @sql_users;

foreach (@lines) {

   push @sql_qual, q| user = ? |;

   push @sql_bind, qq| $_ |;

}



# 3rd, setup the connection do the DataBase

my $db_user = q|dbuser|;

my $db_pass = q|dbpassword|;

my $dbname  = q|db_name|;

my $db_host = q|db_host|;



my $dbh = DBI->connect(

        qq|DBI:mysql:database=$db_name;host=$db_host|,

        $user, $pass

) or die(q|Ooopsss... something went wrong with the database connection.|);



$dbh->{RaiseError} = 1;



# Define the SQL clause

my $sql_clause = join q| OR |, @sql_qual;



# 5th, prepare the query

my $sth = $dbh->prepare(

   qq|SELECT * FROM user WHERE $sql_clause|;

);



# 6th, execute the query

$sth->execute(@sql_bind);



# 7th, fetch and add the results to @results

my @results = $sth->fetchall_arrayref;



# 8th, dump the results in @results

print Dumper(\@results);

Once again, this is all untested code.

Miguel

mcdrr

06-03-2007 08:52 PM

Miguel thanks again for sticking with me. I think we are getting closer. The query does look correct when being printed by the print qq|$_sql_\n\n|; But the results are not being printed out. All that is being printed out is the stuff below, results show show user info like phone, email etc...

Not sure what the $VAR1 = ... is. Also do I need to declare the quotes for after the = sign in the query and also the trailing ; at the end of the query. I am guessing I don't since the qq is smart enough to know.

Here are the results after running the script.

Current line says: user01

SELECT * FROM user WHERE user = user01

Current line says: user02

SELECT * FROM user WHERE user = user02

Current line says: user03

SELECT * FROM user WHERE user = user03

$VAR1 = [
[],
[],
[]
];

Here is what the script looks like after the changes.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use File::Slurp;
use Data::Dumper;

my @lines = read_file('/root/perl/dbquery.txt');

my $db_user = q|root|;
my $db_pass = q|password|;
my $dbname = q|mysql|;
my $db_host = q|localhost|;

my $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$db_host", "$db_user",
"$db_pass", {PrintError => 1, RaiseError => 1, AutoCommit => 1});

$dbh->{RaiseError} = 1;
my $sql = q|SELECT * FROM user WHERE user = ?|;
my $sth = $dbh->prepare($sql);
my @results;

foreach my $line (@lines) {
print qq|Current line says: $line\n|;
my $_sql_ = $sql;
$_sql_ =~s/\?/$line/gmx; # replace the ? with the username
print qq|$_sql_\n\n|;
$sth->execute($line);
push @results, $sth->fetchall_arrayref();
}
print Dumper(\@results);

THANKS AGAIN for your help.

chrism01

06-04-2007 02:22 AM

1. pls use code tags when show ing your code
2. you shouldn't need to regex replace the usernames, that's what the arg to execute() does if you loop through your username list
3. not sure if the FILE::slurp->read_file() method does this for you, but if you slurp a file the traditional way:

Code:

open(han1, "dbquery.txt") || die "Could not open file";

my @console = <han1>;

close(han1);

you need to remove the invisible newline chars from each item before using them thus:

Code:

chomp(@console);

you may need to do this anyway, judging by your results...

mcdrr

06-04-2007 11:07 AM

Chris thanks for the tip. I've also tried it the way you mentioned and I get the same results. Below is the new code and also the results after running the script.

Code:

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

use File::Slurp;

use Data::Dumper;



open(han1, "/root/perl/dbquery.txt") || die "Could not open file";

my @console = <han1>;



my $db_user = q|root|;

my $db_pass = q|password|;

my $dbname  = q|mysql|;

my $db_host = q|localhost|;



#my $dbh = DBI->connect(qq|DBI:mysql:database=$dbname;host=$db_host,$db_user, $db_pass|) or die(q|Ooopsss... something went wrong with the database connection.|);

my $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$db_host", "$db_user",

"$db_pass", {PrintError => 1, RaiseError => 1, AutoCommit => 1});



$dbh->{RaiseError} = 1;

my $sql = q|SELECT * FROM user WHERE user = ?|;

my $sth = $dbh->prepare($sql);

my @results;



foreach my $con (@console) {

        chomp $con;

        print qq|Current line says: $con\n|;

        my $_sql_ = $sql;

        print qq|$_sql_\n\n|;

        $sth->execute($con);

        push @results, $sth->fetchall_arrayref();

}

print Dumper(\@results);

RESULTS- As you can see if I dont run the regex code the user = gets left with a ? and the file being read does not populate into the query. But I end up with the same results as with the previous code, once again not sure what $VAR1 = [... is.

./copy.pl
Current line says: user01
SELECT * FROM user WHERE user = ?

Current line says: user02
SELECT * FROM user WHERE user = ?

Current line says: user03
SELECT * FROM user WHERE user = ?

$VAR1 = [
[],
[],
[]
];

Thanks guys I really appreciate your help with this as I've been banging my head for a few days.

mcdrr

06-04-2007 12:06 PM

Chris actually after looking at your suggestions it appears as though it is working correctly.

Guys thanks so much to both Miguel and you Chris for your help in this I really appreciate it, I could not have figured it out without your help.

Thanks again.

msantinho

06-04-2007 12:18 PM

AH! I warned you :-) I didn't tested the code! When you slurp the file, it comes with the \n at the end of each line. That's the reason you're getting empty records when dumping the results.

Now, let's do it in another way.

Code:

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

use Data::Dumper;



my @users = get_users('/path/to/file');

my @results = fetch_data(

   {

      dbh => conn_db(

         {

            'username' => 'username',

            'password' => 'password',

            'host' => 'localhost',

            'database' => 'database_name'

         }

      )

   }

);



print Dumper(\@results);





sub get_users {



   my $file = shift;



   open my $FILE, '<', $file or die q|Error: can't open file!|;

   my @lines;

   while (<$FILE>) {chomp; push @lines, $_}

   close $FILE;

   return @lines;



}



sub fetch_data {



   my $self = shift;



   my @sql_qual;

   my @sql_bind;

   foreach (@users) {

      push @sql_qual, q| user = ? |;

      push @sql_bind, qq| $_ |;

   }



   my $sql_clause = join q| OR |, @sql_qual;



   my $sth = $self->{'dbh'}->prepare(qq|SELECT * FROM user WHERE $sql_clause|);



   $sth->execute(@sql_bind);

   my @_results = $sth->fetchall_arrayref;



   return \@_results;



}



sub conn_db {



   my $self = shift;



   my $db_user = $self->{'username'};

   my $db_pass = $self->{'password'};

   my $db_name  = $self->{'database'};

   my $db_host = $self->{'host'};



   my $_dbh = DBI->connect(

      qq|DBI:mysql:database=$db_name;host=$db_host|,

      $db_user, $db_pass

   ) or die(q|Ooopsss... something went wrong with the database connection.|);



   $_dbh->{RaiseError} = 1;



   return $_dbh;



}

Miguel

chrism01

06-05-2007 12:00 AM

FYI, as per my last example, it is legal to chomp(@array), which will in fact individually chomp all elements in one go, so you don't need to resort to the 'push...' line.
BTW, it would be nice for future qnrs if you show the working version.

All times are GMT -5. The time now is 02:13 PM.