Perl database connection

In this section, we'll show you the connection to the Perl database.

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

The full name of the DBI: Database Independent Interface, Chinese called a database-independent interface.

DBI, as the standard interface for communication with databases in the Perl language, defines a range of methods, variables, and constants, providing a database persistence layer independent of the specific database platform.

DBI structure

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

Perl database connection

In the chart, the DBI gets all the SQL data sent by the API (Application Programming Interface: Application Interface) and distributes it to the corresponding driver for execution, and finally gets the data back.

Variable name convention

Here are some of 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  引用属性的哈希值并传到方法上

The database connection

Next, let's use the MySQL database as an example to demonstrate how Perl operates on the database.

Here we create the w3cschool database in the MySQL database, the data table is Websites, and the table structure and data are shown in the following image:

Perl database connection

Download the data sheet: websites_perl.sql

Next, let's connect the database using the following code: 

#!/usr/bin/perl -w

use strict;
use DBI;

my $host = "localhost";         # 主机地址
my $driver = "mysql";           # 接口类型 默认为 localhost
my $database = "W3CSCHOOL";        # 数据库
# 驱动程序对象的句柄
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();

The insertion operation

To perform:

  • Use the prepare() API to preprocess SQL statements.
  • Use the execute() API to execute SQL statements.
  • Release the statement handle using the finish() API.
  • Finally, if all goes well, the above actions 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, as shown in the following example by replacing ? The location of the placeholder 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;

The update operation

To perform:

  • Use the prepare() API to preprocess SQL statements.
  • Use the execute() API to execute SQL statements.
  • Release the statement handle using the finish() API.
  • Finally, if all goes well, the above actions 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;

Applications can also bind output and input parameters, as shown in the following example by replacing ? The location of the placeholder to perform an update query: 

$name = 'W3Cschool教程';

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 we want to set, and modify alexa with country as CN to 1000 as follows: 

$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 the data

To perform:

  • Use the prepare() API to preprocess SQL statements.
  • Use the execute() API to execute SQL statements.
  • Release the statement handle using the finish() API.
  • Finally, if all goes well, the above actions will be submitted.

The following data removes data in The Websites that alexa is 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

The do statement can perform UPDATE, INSERT, or DELETE operations, using him to be short, to perform a successful return to true, to perform a failed return false, as follows: 

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

COMMIT operation

Commit completes the operation of the database in order to commit the transaction: 

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

ROLLBACK operation

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

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

As with other languages, perl DBI's operation on the database also supports transaction processing, which is implemented in two ways:

1. Start a transaction when you connect 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, it does not automatically write those updates directly to the database, but rather to the program through $dbh-gt;commit to make the data really write to the database, or $dbh-gt;rollback back and forth just the operation.

2. Start a transaction by using the begin_work dbh-gt;

This way, you don't need to set AutoCommit to 0 when you connect to the database.

You can connect a database connection multiple transactions at a time, without having to connect to the database once at the beginning of each transaction. 

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

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

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

Disconnect the database

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

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