LinuxQuestions.org
Did you know LQ has a Linux Hardware Compatibility List?
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 05-30-2007, 06:19 PM   #1
mcdrr
LQ Newbie
 
Registered: Apr 2007
Posts: 13

Rep: Reputation: 0
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.
 
Old 05-30-2007, 07:41 PM   #2
mcdrr
LQ Newbie
 
Registered: Apr 2007
Posts: 13

Original Poster
Rep: Reputation: 0
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";
}
 
Old 05-31-2007, 02:34 AM   #3
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,280

Rep: Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032
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')

Last edited by chrism01; 05-31-2007 at 06:52 AM.
 
Old 06-01-2007, 08:41 PM   #4
mcdrr
LQ Newbie
 
Registered: Apr 2007
Posts: 13

Original Poster
Rep: Reputation: 0
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
 
Old 06-03-2007, 08:28 AM   #5
msantinho
Member
 
Registered: Oct 2005
Location: Lisbon
Distribution: Slackware
Posts: 51

Rep: Reputation: 15
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

Last edited by msantinho; 06-03-2007 at 08:35 AM.
 
Old 06-03-2007, 04:25 PM   #6
mcdrr
LQ Newbie
 
Registered: Apr 2007
Posts: 13

Original Poster
Rep: Reputation: 0
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:umper;

# 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.

Last edited by mcdrr; 06-03-2007 at 04:41 PM.
 
Old 06-03-2007, 05:32 PM   #7
msantinho
Member
 
Registered: Oct 2005
Location: Lisbon
Distribution: Slackware
Posts: 51

Rep: Reputation: 15
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
 
Old 06-03-2007, 08:52 PM   #8
mcdrr
LQ Newbie
 
Registered: Apr 2007
Posts: 13

Original Poster
Rep: Reputation: 0
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:umper;

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.
 
Old 06-04-2007, 02:22 AM   #9
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,280

Rep: Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032
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...
 
Old 06-04-2007, 11:07 AM   #10
mcdrr
LQ Newbie
 
Registered: Apr 2007
Posts: 13

Original Poster
Rep: Reputation: 0
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.
 
Old 06-04-2007, 12:06 PM   #11
mcdrr
LQ Newbie
 
Registered: Apr 2007
Posts: 13

Original Poster
Rep: Reputation: 0
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.
 
Old 06-04-2007, 12:18 PM   #12
msantinho
Member
 
Registered: Oct 2005
Location: Lisbon
Distribution: Slackware
Posts: 51

Rep: Reputation: 15
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
 
Old 06-05-2007, 12:00 AM   #13
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,280

Rep: Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032Reputation: 2032
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.
 
  


Reply

Tags
mysql, perl, scripting


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Perl Script /MySQL Problem domquem Programming 2 01-10-2006 06:57 AM
mysql query time into perl cgi tommasopiff Programming 0 03-25-2005 04:25 PM
Bizarre SQL select query acid_kewpie Programming 6 01-20-2004 12:47 PM
mySQL redirecting query result to a .txt file! buttersoft Linux - Software 8 12-12-2003 03:05 AM
Mysql Cron Script using select not working fallen Linux - General 2 11-04-2003 07:37 AM


All times are GMT -5. The time now is 03:44 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration