Perl database connection

This chapter we will introduce you to connect Perl database.

Perl 5, we can use the DBI module to connect to the database.

DBI English name: Database Independent Interface, Chinese called database-independent interface.

As DBI and the Perl language database interface communication standard, which defines a set of methods, variables and constants, and provide a specific database platform-independent database persistence layer.

DBI structure

DBI and particular database platform-independent, we can apply it in Oracle, MySQL or Informix, and other databases.

Get all the chart DBI API (Application Programming Interface: Application Programming Interface) sent from SQL data, and then distributed to the corresponding driver on the implementation, and finally get the data back.

Variable name conventions

The following set of the more common name of the variable naming:

$dsn    驱动程序对象的句柄
$dbh    一个数据库对象的句柄
$sth    一个语句或者一个查询对象的句柄
$h      通用的句柄 ($dbh, $sth, 或 $drh)，依赖于上下文
$rc     操作代码返回的布什值（true 或 false）
$rv     操作代码返回的整数值
@ary    查询返回的一行值的数组（列表）
$rows   操作代码返回的行数值
$fh     文件句柄
undef   NULL 值表示未定义
\%attr  引用属性的哈希值并传到方法上

Database Connectivity

Next, we demonstrate Perl with MySQL database as an example of how the database operations.

Here we create a database in MySQL database w3big data table Websites, table structure and data is shown below:

Download the data sheet: http://static.w3big.com/download/websites_perl.sql

Next, we use the following code to connect to the database:

#!/usr/bin/perl -w

use strict;
use DBI;

my $host = "localhost";         # 主机地址
my $driver = "mysql";           # 接口类型 默认为 localhost
my $database = "w3big";        # 数据库
# 驱动程序对象的句柄
my $dsn = "DBI:$driver:database=$database:$host";  
my $userid = "root";            # 数据库用户名
my $password = "123456";        # 数据库密码

# 连接数据库
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
my $sth = $dbh->prepare("SELECT * FROM Websites");   # 预处理 SQL  语句
$sth->execute();    # 执行 SQL 操作

# 注释这部分使用的是绑定值操作
# $alexa = 20;
# my $sth = $dbh->prepare("SELECT name, url
#                        FROM Websites
#                        WHERE alexa > ?");
# $sth->execute( $alexa ) or die $DBI::errstr;

# 循环输出所有数据
while ( my @row = $sth->fetchrow_array() )
{
       print join('\t', @row)."\n";
}

$sth->finish();
$dbh->disconnect();

Insert

Steps:

• Use prepare () API prepared SQL statements.
• Use execute () API to execute SQL statements.
• Use finish () API to release the statement handle.
• Finally, if all goes well it will be submitted over the operation.

my $sth = $dbh->prepare("INSERT INTO Websites
                       (name, url, alexa, conutry )
                        values
                       ('Twitter', 'https://twitter.com/', 10, 'USA')");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

The application can also bind input and output parameters, following the example by substituting variable placeholders to execute an insert query?:

my $name = "Twitter";
my $url = "https://twitter.com/";
my $alexa = 10;
my $conutry = "USA";
my $sth = $dbh->prepare("INSERT INTO Websites
                       (name, url, alexa, conutry )
                        values
                       (?,?,?,?)");
$sth->execute($name,$url,$alexa, $conutry) 
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Update operation

Steps:

• Use prepare () API prepared SQL statements.
• Use execute () API to execute SQL statements.
• Use finish () API to release the statement handle.
• Finally, if all goes well it will be submitted over the operation.

my $sth = $dbh->prepare("UPDATE Websites
                        SET   alexa = alexa + 1 
                        WHERE country = 'CN'");
$sth->execute() or die $DBI::errstr;
print "更新的记录数 :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

The application can also bind input and output parameters, following the example by substituting variable placeholders to perform an update query?:

$name = '本教程';

my $sth = $dbh->prepare("UPDATE Websites
                        SET   alexa = alexa + 1 
                        WHERE name = ?");
$sth->execute('$name') or die $DBI::errstr;
print "更新的记录数 :" + $sth->rows;
$sth->finish();

Of course, we can also bind value to be set as follows for the country will have to modify the CN alexa 1000:

$country = 'CN';
$alexa = 1000：;
my $sth = $dbh->prepare("UPDATE Websites
                        SET   alexa = ?
                        WHERE country = ?");
$sth->execute( $alexa, '$country') or die $DBI::errstr;
print "更新的记录数 :" + $sth->rows;
$sth->finish();

delete data

Steps:

• Use prepare () API prepared SQL statements.
• Use execute () API to execute SQL statements.
• Use finish () API to release the statement handle.
• Finally, if all goes well it will be submitted over the operation.

The following data will Websites in the alexa data is greater than 1000 are deleted:

$alexa = 1000;
my $sth = $dbh->prepare("DELETE FROM Websites
                        WHERE alexa = ?");
$sth->execute( $alexa ) or die $DBI::errstr;
print "删除的记录数 :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Use do statement

do statements can perform UPDATE, INSERT, or DELETE operation, he is quite short, successful execution returns true, fails it returns false, examples are as follows:

$dbh->do('DELETE FROM Websites WHERE alexa>1000');

COMMIT operation

commit to commit the transaction to complete the operation of the database:

$dbh->commit or die $dbh->errstr;

ROLLBACK operation

If an error occurs during the execution of SQL, you can roll back the data without any change:

$dbh->rollback or die $dbh->errstr;

And other languages, perl DBI operation of the database also supports transaction processing, there are two ways to achieve it:

1, in connection to the database when it started a transaction

$dbh = DBI->connect($dsn, $userid, $password, {AutoCommit => 0}) or die $DBI::errstr;

The above code at the time of connection set AutoCommit is false, that is, when you update the database operation, it does not automatically update those written directly to the database, but to the program by $ dbh-> commit to so that the data actually written to the database, or $ dbh-> rollback to roll back the operation.

2, by $ dbh-> begin_work () statement to start a transaction

In this way there is no need to set AutoCommit = 0 in the database connection time.

You can connect a database transaction operation several times, not every transaction are beginning to connect to a database.

$rc  = $dbh->begin_work  or die $dbh->errstr;

#####################
##这里执行一些 SQL 操作
#####################

$dbh->commit;    # 成功后操作
-----------------------------
$dbh->rollback;  # 失败后回滚

Disconnect the database connection

If we need to disconnect from the database, you can use the disconnect API:

$rc = $dbh->disconnect  or warn $dbh->errstr;