Perl MySQL Insert Data

Summary: in this tutorial, we will show you step-by-step how to insert data into a table by using Perl DBI.

We will use the links table created in the Perl MySQL creating tables tutorial. If you haven’t created the links table yet, we strongly recommend that you create it before going forward with this tutorial.

To insert a new row into a table by using Perl DBI, you need to perform the following steps:

• Parse the INSERT statement by calling the prepare() method of the database handle object. The  prepare() method returns a statement handle object that represents a statement within the MySQL database. In this step, Perl DBI validates the INSERT statement to make sure that it is valid. If there is an error in the INSERT statement e.g., it references a non-existent table, or it is an invalid SQL statement, the  prepare() statement returns a value of undef . In addition, Perl populates the error message into the  $DBI::errstr variable.
• Execute the INSERT statement by calling the  execute() method of the statement handle object. In this step, Perl executes the INSERT statement within the MySQL database. The  execute() method returns true on success and a value undef on failure. In case of failure, Perl also raises an exception via the die() function to abort the script immediately if the RaiseError attribute is enabled.

Notice that you could execute the UPDATE or DELETE statement by using these steps.

Perl MySQL INSERT example

The following script allows you to insert data into the links table:

#!/usr/bin/perl
use strict;
use warnings;
use v5.10; # for say() function

use DBI;

# MySQL database configurations
my $dsn = "DBI:mysql:perlmysqldb";
my $username = "root";
my $password = '';

say "Perl MySQL INSERT Demo";

# get user's input links
my @links = get_links();

# connect to MySQL database
my %attr = (PrintError=>0,RaiseError=>1 );
my $dbh = DBI->connect($dsn,$username,$password,\%attr);

# insert data into the links table
my $sql = "INSERT INTO links(title,url,target)
	   VALUES(?,?,?)";

my $stmt = $dbh->prepare($sql);

# execute the query
foreach my $link(@links){
  if($stmt->execute($link->{title}, $link->{url}, $link->{target})){
    say "link $link->{url} inserted successfully";
  }

}
$stmt->finish();

# disconnect from the MySQL database
$dbh->disconnect();

sub get_links{
   my $cmd = '';
   my @links;
   # get links from the command line
   my($title,$url,$target);

   # repeatedly ask for link data from command line
   do{
     say "title:";
     chomp($title = <STDIN>); 

     say "url:";
     chomp($url = <STDIN>);

     say "target:";
     chomp($target = <STDIN>);

     #
     my %link = (title=> $title, url=> $url, target=> $target);

     push(@links,\%link);

     print("\nDo you want to insert another link? (Y/N)?");
     chomp($cmd = <STDIN>);
     $cmd = uc($cmd);
   }until($cmd eq 'N');

   return @links;
}Code language: Perl (perl)

How it works.

• First, we created a new subroutine called get_links() to get link information from the user’s input. The  get_links() subroutine returns a list of links as an array.
• Next, we connected to the perlmysqldb database.
• Then, we prepared an INSERT statement that inserts data into the links table. The question marks ( ? ) are the placeholders that will be substituted by the corresponding values of title, URL, and target. We passed the  INSERT statement to the  prepare() method for preparing execution.
• After that, we iterated the  @links array and executed the INSERT statement. We displayed a message to specify if data is inserted into the links table successfully.
• Finally, we disconnected from the MySQL database by calling the  disconnect() method of the database handle object.

The following illustrates the output when we inserted 4 rows into the links table.

Perl MySQL INSERT Demo
title:
MySQL Tutorial
url:
httpx://www.mysqltutorial.org/
target:
_self
Do you want to insert another link? (Y/N)?Y
title:
Perl MySQL Insert Demo
url:
httpx://www.mysqltutorial.org/perl-mysql/perl-mysql-insert/
target:
_blank
Do you want to insert another link? (Y/N)?Y
title:
Perl
url:
httpx://www.perl.org
target:
_blank
Do you want to insert another link? (Y/N)?Y
title:
MySQL
url:
httpx://www.mysql.com
target:
_self
Do you want to insert another link? (Y/N)?N
link http://mysqltutorial.org/ inserted successfully
link https://www.mysqltutorial.org/perl-mysql/perl-mysql-insert/ inserted successfully
link http://www.perl.org inserted successfully
link http://www.mysql.com inserted successfullyCode language: PHP (php)

We can check the links table to verify the insert operations:

SELECT * FROM links;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use Perl DBI to insert data into a MySQL database table.