PERL / DBI / MYSQL - [Select | Insert]

mysql> create table pessoa (id int(10) primary key auto_increment, nome varchar(45) , telefone int(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into pessoa (nome,telefone) values ('Command Line',0000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pessoa;
+----+--------------+----------+
| id | nome         | telefone |
+----+--------------+----------+
|  1 | Command Line |        0 |
+----+--------------+----------+
1 row in set (0.00 sec)

mysql> desc pessoa;

+----------+-------------+------+-----+---------+----------------+

| Field      | Type         | Null   | Key   | Default | Extra            |

+----------+-------------+------+-----+---------+----------------+

| id          | int(10)        | NO   | PRI | NULL    | auto_increment |

| nome     | varchar(45) | YES  |       | NULL    |                      |

| telefone  | int(10)       | YES  |       | NULL    |                       |

+----------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

file.txt

Kaila   1111

Kalila  2222

Kaori   3333

Karen   4444

Karina  5555

Karine  6666

Karla   7777

Karoline        8888

Kássia 9999

Kate    1010

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

#SELECT 

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

my $database = 'Agenda';

my $user = 'java';

my $password = 'java';

my $dbh = DBI->connect("DBI:mysql:$database", "$user", "$password" ) || die "Could not connect to database: $DBI::errstr";

my $sth = $dbh->prepare('SELECT *  FROM pessoa');

$sth->execute();

while(my @result = $sth->fetchrow_array() ){

        print join ("\t",@result),"\n";

}

$dbh->disconnect();

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

als:/blog$ perl select_mysql_PERL.pl 

1 Command Line 0

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

#INSERT

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

my $database = 'Agenda';

my $user = 'java';

my $password = 'java';

my $dbh = DBI->connect("DBI:mysql:$database", "$user", "$password" ) || die "Could not connect to database: $DBI::errstr";

my $sth = $dbh->prepare( q{ INSERT INTO pessoa (nome,telefone) VALUES (?, ?)}) or die $dbh->errstr;

open (File,"file.txt") or die $!;

while (<File>) {

        chomp;

        my ($nome,$telefone) = split (/\t/,$_);

        $sth->execute($nome,$telefone) or die $dbh->errstr;

}

close();

$dbh->disconnect();

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

als:/blog$ perl insert_mysql_PERL.pl 

als:/blog$ perl select_mysql_PERL.pl 

1 Command Line 0

2 Kaila 1111

3 Kalila 2222

4 Kaori 3333

5 Karen 4444

6 Karina 5555

7 Karine 6666

8 Karla 7777

9 Karoline 8888

10 Kássia 9999

11 Kate 1010

mysql> select * from pessoa;

+----+--------------+----------+

| id | nome         | telefone |

+----+--------------+----------+

|  1 | Command Line |        0 |

|  2 | Kaila        |     1111 |

|  3 | Kalila       |     2222 |

|  4 | Kaori        |     3333 |

|  5 | Karen        |     4444 |

|  6 | Karina       |     5555 |

|  7 | Karine       |     6666 |

|  8 | Karla        |     7777 |

|  9 | Karoline     |     8888 |

| 10 | Kássia      |     9999 |

| 11 | Kate         |     1010 |

+----+--------------+----------+

11 rows in set (0.00 sec)

It shares.