Storing some values of an array to a DB: How does Perl talk to MySQL?

English Programming/Scripting
#1

Hello dear Community, good evening! :wink:

My first perl-parser runs very well. BTW: Well i am very very glad to be here - this is a great place and i have a nice learning curveā€¦This forums give me a great asset of learning!! Many many thanks to all that helped me here in this great place. I love these forums!!

**What is aimed today:**i am parsing site with a small table and some labels and values:

click the link and see the page with the Schulinformationen

Note:This page [and many others that are simmilar] is/are stored locally in the same folder as file t.html

Now i want to store the data in a MySQL-DB: i am figuring out how to do this:

here the code:


    #!/usr/bin/perl 
     
    use strict; use warnings; 
    use HTML::TableExtract; 
    use YAML; 
     
    my $te = HTML::TableExtract->new( 
        attribs => { class => 'bp_ergebnis_tab_info' }, 
    ); 
     
    $te->parse_file('t.html'); 
     
     
    foreach my $table ( $te->tables ) { 
            foreach my $row ($table->rows) { 
            my @values = grep {defined} @$row; 
            print "   ", join(',', @values), "
"; 
           } 
        }

Note - the output is very vey nice !
I currently work on the code that is needed to store the data in a MySQL-DB. I need a starting point for the creation of this db. Well see the labels and the values:


Schulnummer, 143960
Amtliche Bezeichnung,Franziskusschule Kath. Hauptschule Ahaus - Sekundarstufe I 
Strasse, Hof zum Ahaus 6
Plz und Ort, 48683 Ahaus
Telefon, 02561 4291990
Fax, 02561 42919920
E-Mail-Adresse, 3960@schule.nrw.de

Well, currently i am working on the interaction of the script with the database. Well, i look forward to some ideas - any and all ideas & hints for the starting point will be greatly appreciated.

Greetings
meta :wink:

#2

Hi,

I would suggest using the perl-DBI database abstraction module and perl-DBD-mysql driver module for MySQL.

There are plenty of examples available for this combination and there are perl-DBD drivers for many database types (including plain text and CSV).

I hope that helps.

Regards,
Neil Darlow

#3

Hello dear Neil, good evening!

Thx for your answer!

I am not very familiar with DBI. Neil i try out this one here: i am not so good and experience in using the DBI module - but i will try out this one:


#!/usr/bin/perl 
use strict; 
use warnings; 
use DBI; 
use HTML::TableExtract; 
 
my $dbname = ''; 
my $dbhost = 'localhost'; 
my $dbdsn = "DBI:mysql:$dbname;host=$dbhost"; 
my $dbusername = ''; 
my $dbpassword = ''; 
my $dbtable = ''; 
 
my $dbh = DBI->connect($dbdsn, $dbusername, $dbpassword, {RaiseError => 1, AutoCommit => 0}) or die "Failed to connect to database: " . DBI->errstr; 
my $statement = "INSERT INTO $dbtable (Column1, Column2) VALUES (?, ?)"; 
 
my $te = HTML::TableExtract->new(attribs => { class => 'bp_ergebnis_tab_info' }); 
$te->parse_file('t.html'); 
 
foreach my $table ($te->tables) { 
	foreach my $row ($table->rows) { 
		my @values = grep {defined} @$row; 
		#print join(',', @values), "
"; 
		my $sth = $dbh->do($statement, undef, @values) or die "Failed to prepare and execute statement: " . $dbh->errstr; 
	} 
} 
 
$dbh->commit(); 
$dbh->disconnect();

i will try out this one hereā€¦ And i will try to find how it worksā€¦