home PYTHONJAVA
 

Perl database connection

In this chapter we will introduce you to the connection to the 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.

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


DBI structure

DBI is independent of the specific database platform, and we can apply it to databases such as Oracle, MySQL or Informix.

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

Variable name convention

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

$dsn Handle to Driver Objects
$dbh Handle to a database object
$sth A statement or a handle to a query object
$h Universal handles ($dbh, $sth,  or  $drh), dependent In context
$rc The Bush value returned by the action code (true  or  false)
$rv Integer value returned by the action code
@ary Query an array of rows of values ​​returned (list)
$rows The value of the row returned by the action code
$fh File handle
undef NULL value indicates undefined
\%attr Reference the hash of the attribute and pass it to the method

Database Connection

Next we will use the MySQL database as an example to demonstrate how Perl operates on the database.

Here we create the RUNOOB database in the MySQL database, the data table is Websites, the table structure and data are as shown below:

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

Instance

#!/usr/bin/perl -w use strict; use DBI; my $host = "localhost"; #host deal with my $driving force = "mysql"; # The interface kind defaults to localhost my $database = "Welookups"; # Database # take care of to the driver object my $dsn = "DBI:$driver:database=$database:$host" ; my $userid = "root"; # database username my $password = "123456"; # Database Password # Connect to the database my $dbh = DBI->join($dsn, $userid, $password ) or die $DBI::errstr; my $sth = $dbh->put together("SELECT * FROM Websites") ; # pre-processing SQL statements $sth->execute(); # Execute SQL operations # Comment This section uses the bind value movement # $alexa = 20; # my $sth = $dbh->put together("SELECT name, url # FROM Websites # WHERE alexa > ?"); # $sth->execute( $alexa ) or die $DBI::errstr; # looping through all statistics at the same time as ( my @row = $sth-> ;fetchrow_array() ) print be a part of('t', @row )."n"; $sth->end(); $dbh->disconnect();

Insert operation

Execution steps:

  • Preprocess SQL proclamations the utilization of the set up together() API.
  • Execute SQL proclamations the utilization of the execute() API.
  • Use the end() API to discharge the declaration deal with.
  • Finally, if all goes appropriately, set up the above usage.
my $sth = $dbh->prepare("INSERT INTO Websites 

(call, url, alexa, conutry ) 

Qualities 

('Twitter', 'https://twitter.Com/', 10, 'USA')"); 

$sth->execute() or die $DBI::errstr; 

$sth->finish(); 

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

Applications additionally can tie yield and info parameters. The accompanying case plays out an addition question by means of changing the area of the placeholder with a variable:

my $name = "Twitter"; 

my $url = "https://twitter.Com/"; 

my $alexa = 10; 

my $conutry = "USA"; 

my $sth = $dbh->prepare("INSERT INTO Websites 

(call, url, alexa, conutry ) 

Qualities 

(?,?,?,?)"); 

$sth->execute($name,$url,$alexa, $conutry ) 

or die $DBI::errstr; 

$sth->end(); 

$dbh->devote or die $DBI:: errstr;

Update operation

Execution steps:

  • Preprocess SQL proclamations the use of the plan() API.
  • Execute SQL proclamations the use of the execute() API.
  • Use the completion() API to dispatch the announcement handle.
  • Finally, if all is going legitimately, distribute the above execution.
my $sth = $dbh->put together("UPDATE Websites 

SET alexa = alexa + 1 

WHERE u . S . = 'CN'"); 

$sth->execute() or die $DBI::errstr; 

print "Updated Records:" + $sth->rows; 

$sth->finish(); 

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

The utility can likewise tie yield and enter parameters. The accompanying case plays an update question by method for changing the area of the placeholder with a variable:

$name = 'prograaming Tutorial'; 

my $sth = $dbh->put together("UPDATE Websites 

SET alexa = alexa + 1 

WHERE name = ?"); 

$sth->execute('$call') or die $DBI:: errstr; 

print "Updated Records:" + $sth->rows; 

$sth->finish();

Of course, we likewise can tie the cost to be set, and exchange the alexa with usa CN to 1000 as follows:

$usa = 'CN'; 

$alexa = one thousand:; 

my $sth = $dbh->put together("UPDATE Websites 

SET alexa = ? 

WHERE US of america = ?"); 

$sth->execute( $alexa, '$united states') or die $DBI::errstr; 

print "Updated Records:" + $sth->rows; 

$sth->finish();

Delete records

Execution steps:

  • Preprocess SQL articulations utilizing the set up together() API.
  • Execute SQL articulations utilizing the execute() API.
  • Use the completion() API to dispatch the announcement manage.
  • Finally, if all goes pleasantly, set up the above execution.

The following data discards information from alexa more noticeable than one thousand in Websites:

$alexa = a thousand; 

my $sth = $dbh->prepare("DELETE FROM Websites 

WHERE alexa = ?"); 

$sth->execute( $alexa ) or die $DBI::errstr; 

print "Number of erased statistics:" + $sth->rows; 

$sth->finish(); 

$dbh->dedicate or die $DBI:: errstr;

Use the do announcement

The do affirmation can play out an UPDATE, INSERT, or DELETE activity. It is shorter, the execution returns genuine, and the execution falls flat. The case is as follows:

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

COMMIT operation

commit is to devote the exchange, whole the activity of the database:

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

ROLLBACK operation

If a blunders happens at some phase in SQL execution, you could roll again the data without making any modifications:

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

Exchange

Like other languages, perl DBI also supports transaction processing for databases. It is implemented in two ways:

1, start a transaction when connecting to the database

$dbh = DBI< /span>->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, but the program will pass $dbh- >commit to make the data actually write to the database, or $dbh->rollback to roll back the operation.

2, start a transaction with the $dbh->begin_work() statement

This way you don't need to set AutoCommit = 0 when connecting to the database.

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

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

############################ 

##Do a couple SQL tasks here 

############################ 

$dbh->dedicate; # After a hit operation 

 - -  

$dbh->rollback; # Rollback after failure

Disconnect database connection

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

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





welookups is optimized for learning.© welookups. 2018 - 2019 All Right Reserved and you agree to have read and accepted our term and condition.