Perl Database Connection

From WikiOD

Perl database connection[edit | edit source]

In this chapter, we will introduce the connection of Perl database.

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

DBI English full name: Database Independent Interface, Chinese called Database Independent Interface.

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


DBI structure[edit | edit source]

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

Image 241.png


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

Variable name convention[edit | edit source]

The more commonly used variable name naming methods are set as follows:

 $dsn Handle of the driver object
 $dbh handle of a database object
 $sth a statement or a query object handle
 $h general handle ($dbh, $sth, or $drh), depending on the context
 The bush value returned by the $rc operation code (true or false)
 Integer value returned by the $rv operation code
 @ary The array (list) of a row of values ​​returned by the query
 The row value returned by the $rows operation code
 $fh file handle
 undef NULL value means undefined
 \%attr refers to the hash value of the attribute and passes it to the method
----

Database Connectivity[edit | edit source]

Next, we take MySQL database as an example to demonstrate how Perl operates the database.

Here we create the RUNOOB database in the MySQL database, the data table is Websites,

Download the data sheet: http://static.phpxs.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"; # Host address
 my $driver = "mysql"; # The default interface type is localhost
 my $database = "RUNOOB"; # Database
 # The handle of the driver object
 my $dsn = "DBI:$driver:database=$database:$host";  
 my $userid = "root"; # database user name
 my $password = "123456"; # database password
 
 # Connect to the database
 my $dbh = DBI->connect($dsn, $userid, $password) or die $DBI::errstr;
 my $sth = $dbh->prepare("SELECT * FROM Websites"); # Prepare SQL statement
 $sth->execute(); # Execute SQL operation
 
 # Note that this part uses bound value operations
 # $alexa = 20;
 # my $sth = $dbh->prepare("SELECT name, url
 # FROM Websites
 # WHERE alexa> ?");
 # $sth->execute( $alexa) or die $DBI::errstr;
 
 # Loop to output all data
 while (my @row = $sth->fetchrow_array())
 {
        print join('\t', @row)."\n";
 }
 
 $sth->finish();
 $dbh->disconnect();
----

Insert operation[edit | edit source]

Steps:

  • Use the prepare() API to prepare SQL statements.
  • Use execute() API to execute SQL statements.
  • Use the 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','<code>https://twitter.com/'</code>, 10,'USA')");
 $sth->execute() or die $DBI::errstr;
 $sth->finish();
 $dbh->commit or die $DBI::errstr;
The application can also bind output and input parameters. The following example executes an insert query by replacing the position of the? Placeholder with a variable:
 my $name = "Twitter";
 my $url = "<code>https://twitter.com/</code>";
 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[edit | edit source]

Steps:

  • Use the prepare() API to prepare SQL statements.
  • Use execute() API to execute SQL statements.
  • Use the 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 "Number of updated records:" + $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 ='Novice Tutorial';
 
 my $sth = $dbh->prepare("UPDATE Websites
                         SET alexa = alexa + 1 
                         WHERE name = ?");
 $sth->execute('$name') or die $DBI::errstr;
 print "Number of updated records:" + $sth->rows;
 $sth->finish();

Of course, we can also bind the value to be set, as shown below, modify the alexa whose country is 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 "Number of updated records:" + $sth->rows;
 $sth->finish();
----

Delete data[edit | edit source]

Steps:

  • Use the prepare() API to prepare SQL statements.
  • Use execute() API to execute SQL statements.
  • Use the 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 the data of alexa greater than 1000 in Websites:

 $alexa = 1000;
 my $sth = $dbh->prepare("DELETE FROM Websites
                         WHERE alexa = ?");
 $sth->execute( $alexa) or die $DBI::errstr;
 print "Number of deleted records:" + $sth->rows;
 $sth->finish();
 $dbh->commit or die $DBI::errstr;
----

Use the do statement[edit | edit source]

The do statement can perform UPDATE, INSERT, or DELETE operations. It is relatively short to use it. Successful execution will return true, and execution failure will return false. Examples are as follows:

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

COMMIT operation[edit | edit source]

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

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

ROLLBACK operation[edit | edit source]

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

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

Affairs

Like other languages, perl DBI also supports transaction processing for database operations. It can be implemented in two ways:

1. Start a transaction when connecting to the database[edit | edit source]
$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 will not automatically write those updates directly to the database. Instead, the program will use $dbh->commit. Make the data actually written to the database, or $dbh->rollback to roll back the previous operation.

2. Start a transaction through the $dbh->begin_work() statement[edit | edit source]

In this way, there is no need to set AutoCommit = 0 when connecting to the database.

You can perform multiple transaction operations with one database connection, instead of connecting to the database at the beginning of each transaction.

 $rc = $dbh->begin_work or die $dbh->errstr;
 
 ####################
 ##Perform some SQL operations here
 ####################
 
 $dbh->commit; # Operation after success
 -----------------------------
 $dbh->rollback; # Rollback after failure
----

Disconnect the database[edit | edit source]

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

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