Perl database connection

In this chapter we will introduce you to the Perl database connection.

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

The full English name of DBI is: Database Independent Interface, which is called Database Independent Interface in Chinese.

DBI is the standard interface for communicating with the database in the Perl language. It defines a series of methods, variables and constants, and provides a database persistence layer that is independent of the specific database platform.

DBI structure

DBI has nothing to do with the specific database platform. We can apply it to databases such as Oracle, MySQL or Informix.

DBI in the chart obtains the SQL data sent by all APIs (Application Programming Interface: Application Programming Interface), then distributes it to the corresponding driver for execution, and finally obtains the data and returns it .

Variable name convention

The following sets the more commonly used variable name naming methods:

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

Database connection

Next we use the MySQL database Take an example to demonstrate how Perl operates the database.

Here we create a php database in the MySQL database. The data table is Websites. The table structure and data are as shown below:

Download the data table: http ://static.php.cn/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 = "php";        # 数据库
# 驱动程序对象的句柄
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 operation

Execution steps :

Use prepare() API to prepare SQL statements.
Use execute() API to execute SQL statements.
Use finish() API to release the statement handle.
Finally, if everything goes well, the above execution operation will be submitted.

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;

Applications can also bind output and input parameters. The following example executes an insert query by replacing the ? placeholder position with a variable:

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

Execution steps:

Use prepare() API to prepare SQL statements.
Use execute() API to execute SQL statements.
Use finish() API to release the statement handle.
Finally, if everything goes well, the above execution operation will be submitted.

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 output and input parameters. The following example executes an update query by replacing the position of the ? placeholder with a variable:

$name = 'php中文网';

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 the values to be set, as shown below, modify the Alexa with country CN to 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

Execution steps:

Use prepare() API to prepare SQL statements.
Use execute() API to execute SQL statements.
Use finish() API to release the statement handle.
Finally, if everything goes well, the above execution operation will be submitted.

The following data will delete all data in Websites with alexa greater than 1000:

$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 the do statement

do The statement can perform UPDATE, INSERT, or DELETE operations. It is relatively short. If the execution is successful, it will return true. If it fails, it will return false. Example As follows:

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

COMMIT operation

commit is to commit the transaction and complete the database operation:

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

ROLLBACK operation

If in If an error occurs during SQL execution, the data can be rolled back without any changes:

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

Transaction

Like other languages, perl DBI also supports transaction processing for database operations. Its implementation is as follows Two:

1. Start a transaction when connecting to the database

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

The above code sets AutoCommit to false when connecting, which means that when you update the database At this time, it will not automatically write those updates directly to the database, but requires the program to actually write the data to the database through $dbh->commit, or $dbh->rollback to roll back the previous operation.

2. Start a transaction through the $dbh->begin_work() statement

This method does not require setting AutoCommit = 0 when connecting to the database.

You can perform multiple transaction operations with one database connection, without having to connect to the database at the beginning of each transaction.

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

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

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

Disconnect the database connection

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

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