DBI Module Operations Database Example

One of the most important modules in Perl is the DBI module (Perl database Interface, DB interface). The DBI module provides a unified interface for many different databases. This interface makes it easy for Perl to manipulate the database. The structure diagram is as follows:

Here's how Perl operates on a MySQL database.

1. First make sure that the Perl and MySQL databases are installed. It is then installed using the CPAN or PPM graphical interface.

cpan> install DBI        #安装数据库模块cpan > Install dbd::mysql #安装数据库驱动  

2. Then prepare the test with the database script: (admin.sql)

C:\Program files\mysql\mysql Server 5.5\bin> mysql-u ROOT-PABCD <c:\admin.sqldrop database if exists admin;    #创建数据库, delete the CREATE DATABASE admin if it exists;            #创建数据库. Use admin;                        #打开数据库, be careful with semicolons. CREATE TABLE Admin_info           #建练习用表. (admin_id int (3) auto_increment NOT NULL primary key,  #主键, non-empty, self-growing. Admin_user  varchar () NOT NULL,                admin_pwd  varchar (()) is not null);          INSERT into Admin_info values (' 001 ', ' Tom ', ' 1234 ');     #插入两条数据, for testing. INSERT into Admin_info values (' 002 ', ' Jerry ', ' ABCD ');   #插入两条数据, for testing.

3. Prepare the Database script: (conn_mysql.pl)

#! c:\perl\bin\perl-w# Import DBI module use dbi;use utf8;use strict;use warnings; #连接数据库my $dbdrive = "MySQL"; my $dbname = "admin"; my $h Ostname = "localhost"; my $port = "3306";   #MySQL数据库缺省端口my $database = "DBI: $dbdrive: $dbname: $hostname: $port"; my $db _user = "root"; my $db _pwd = "ABCD"; my $dbh = dbi- >connect ($database, $db _user, $db _pwd) or Die dbi::errstr; #执行插入操作my $rows = $dbh->do ("INSERT INTO admin_info (admin _ID,ADMIN_USER,ADMIN_PWD)                     VALUES (003, ' root ', ' root ');p rint "$rows row (s) affected!\n"; #执行查询操作my $sth = $DBH- >prepare ("SELECT * from Admin_info"), $sth->execute (), #通过结果集进行迭代, and print while (my $ref = $sth->fetchrow_hashref () {print "admin_id: $ref->{' admin_id '}\n";p rint "Admin_user: $ref->{' Admin_user '}\n";p rint "admin_pwd: $ref- >{' admin_pwd '}\n ";p rint"----------n ";} #结束会话连接 $dbh->disconnect ();

4. The results are as follows:

Summary:

1. Use the Connect () function to establish a database connection.

2. Insert, query, delete, update operation using do () or prepare () and execute () functions

3. Using the Fetchrow_hashref () function, with while, the data will be traversed to facilitate the printing operation

4. Use the Disconnect () function to disconnect the database and end the file handle

In general, the database operation steps are similar to other languages.

Other logo Description:

Perl recommends using the following flags as conventions

  $DSN    Database Source name  $DBH    Database Processing object  $sth    processing Object declaration  $h      any of the above three processing types ($DBH, $sth, or $DRH)  $RC     the Universal Return code (BOOLEAN:TRUE=OK, False=error)  $rv     a generic return value (usually an integer)  @ary    The list of data returned in the database  $rows   the number of rows to process (if the number of rows does not exist, the return value is:-1)  $fh     file handle  undef   null value in Perl represents undefined value   \%attr  Referencing a hash property value to a method