CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
storing some some values to a DB: how does Perl talk to MySQL?

 



dilbert
User

Dec 20, 2010, 7:58 AM

Post #1 of 3 (552 views)
storing some some values to a DB: how does Perl talk to MySQL? Can't Post

 
Hello dear Community,


My first perl-parser runs very well - (thanks to the support of some friends here : Many many thanks @ Chris who helped alot!).

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!!

What is aimed: i am parsing site with a small table

http://www.schulministerium.nrw.de/BP/SchuleSuchen?action=799.601437941842&SchulAdresseMapDO=142359

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:

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), "\n";
}
}


Now i want to store the output in a MySQL-DB.

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


I look forward to some ideas - any and all ideas & hints for the starting point will be greatly appreciated.

Greetings
dbert


Zhris
Enthusiast

Dec 20, 2010, 1:06 PM

Post #2 of 3 (549 views)
Re: [dilbert] storing some some values to a DB: how does Perl talk to MySQL? [In reply to] Can't Post

Hello again,

For this, you will want to use the DBI module (http://search.cpan.org/~timb/DBI-1.615/DBI.pm).

Here is an entirely untested example, merely written to point you in the right direction, it may not work correctly, and of course I don't know how you wish to store the data in the table. I suggest you ask someone who is experienced in using the DBI module if you encounter difficulties:


Code
#!/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), "\n";
my $sth = $dbh->do($statement, undef, @values) or die "Failed to prepare and execute statement: " . $dbh->errstr;
}
}

$dbh->commit();
$dbh->disconnect();


Goodluck,

Chris


(This post was edited by Zhris on Dec 20, 2010, 2:27 PM)


dilbert
User

Dec 20, 2010, 2:28 PM

Post #3 of 3 (540 views)
Re: [Zhris] storing some some values to a DB: how does Perl talk to MySQL? [In reply to] Can't Post

Hello Chris,

many many thanks for the reply! Glad to hear from you! That is a great thread. You give me a great starting-point!

i will try out your code in the next days!!

I will report all my findings here.

Greetings dilbert

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives