MySQL::RunSQL - Run SQL queries against a MySQL database and return the results to STDOUT or XLSX file.
use MySQL::RunSQL; my $runsql = MySQL::RunSQL->new( group_name => <mysql_group>, #[blockname] to look under in my.cnf file database => <database>, # Database to run query against my_cnf => <my_cnf_file>, # File the contains user/pass information host => <mysql_server> # MySQL server to connect to ); my $sqlfile = "/tmp/employees.sql"; $runsql->runsql("SELECT col1 as 'Col 1', col2 as 'Col 2'..."); $runsql->runsql_fromfile($sqlfile); my $xlsx = $runsql_report("SELECT col1 as 'Col 1', col2 as 'Col 2'..."); my $xlsx = $runsql_report_fromfile($sqlfile);
MySQL::RunSQL provides a simple object oriented interface to DBD::mysql for running a queries against a MySQL database. Data is returned to STDOUT as a comma delimited list or is written out to an XLSX file.
#!/usr/bin/perl use strict; use MySQL::RunSQL; use Env qw/HOME/; my $db = "test"; my $cnf = "$HOME/.my.cnf"; my $group = "test"; my $host = "localhost"; # Create our MySQL::RunSQL Object my $rs = MySQL::RunSQL->new( database => $db, my_cnf => $cnf, group_name => $group, host => $host ); # The report will use the as "NAME" as the header for the column #if this is omitted then it will use the column identifier my $sql = <<"SQL"; SELECT employee_id as 'ID', CONCAT(first_name,' ', last_name) as 'Name', address as 'Address' FROM emloyeeDB WHERE start_date like "%2002%" SQL # Run the report & write out to excel my $report = $rs->runsql_report->($sql); # Print the file location print "\n\tYour report is ready: $report\n\n"; exit 0;
Returns a MySQL::RunSQL object. The new method is invoked with the following options:
host => mysql_server database => database port => mysql port default=3306 my_cnf => mysql my.cnf file with both user & password set group_name => under which [group] in the my.cnf file can the login information be found host, database, my_cnf, & group_name are all required.
Takes a sql query and prints to STDOUT a comma delimited list of rows returned
Takes a file containing a sql query and prints to STDOUT a comma delimited list of rows returned.
Takes a sql query and prints output to an xlsx file. Returns the file name and location.
Takes a file containing a sql query and prints output to an xlsx file and returns the file location
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.10.0 or, at your option, any later version of Perl 5 you may have available.
Melissa A. VandenBrink, <geeklady@cpan.org>
<geeklady@cpan.org>
MySQL Database http://www.mysql.con/
To install MySQL::RunSQL, copy and paste the appropriate command in to your terminal.
cpanm
cpanm MySQL::RunSQL
CPAN shell
perl -MCPAN -e shell install MySQL::RunSQL
For more information on module installation, please visit the detailed CPAN module installation guide.