Inserting Data into MySQL with Perl

In the two previous posts, we looked at simply connecting to a MySQL database via Python and Perl. In this post, we will:

- use an insert statement to input data into a MySQL table via Perl
- use a select statement to view the same data to confirm our results

For this example, we will use a table named “address”. Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL,
  `address_city` varchar(30) NOT NULL,
  `address_state` varchar(20) NOT NULL,
  `address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

We are only going to insert one address line with our data. You can modify this script to loop the process and insert multiple rows of data.

- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;

use warnings;

$database = "scripting_mysql";

#----------------------------------------------------------------------
# insert the values into the database
#----------------------------------------------------------------------


# invoke the ConnectToMySQL sub-routine to make the database connection
$connection = ConnectToMySql($database);

# set the value of your SQL query
$query = "insert into address (name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code) 
			values (?, ?, ?, ?, ?, ?, ?) ";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query);

# execute your SQL statement
$statement->execute('John', 'Smith', '100 Main Street', 'Suite 500', 'Buffalo', 'NY', '14201');

#----------------------------------------------------------------------


#----------------------------------------------------------------------
# retrieve the values from the database
#----------------------------------------------------------------------

# set the value of your SQL query
$query2 = "select name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code from address where name_last = 'Smith'";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query2);

# execute your SQL statement
$statement->execute();

# we will loop through the returned results that are in the @data array
# even though, for this example, we will only be returning one row of data

   while (@data = $statement->fetchrow_array()) {
      $name_first = $data[0];
      $name_last = $data[1];
      $address_01 = $data[2];
      $address_02 = $data[3];
      $address_city = $data[4];
      $address_state = $data[5];
      $address_postal_code = $data[6];

print "RESULTS - $name_first, $name_last, $address_01, $address_02, $address_city, $address_state, $address_postal_code\n";

}

#----------------------------------------------------------------------


# exit the script
exit;

#--- start sub-routine ------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

# open the accessDB file to retrieve the database name, host name, user name and password
open(ACCESS_INFO, "<..\/accessAdd") || die "Can't access login credentials";

# assign the values in the accessDB file to the variables
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# assign the values to your connection variable
my $connectionInfo="dbi:mysql:$db;$host";

# close the accessDB file
close(ACCESS_INFO);

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);

# make connection to database
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);

# the value of this connection is returned by the sub-routine
return $l_connection;

}

#--- end sub-routine --------------------------------------------------

- - - - STOP SCRIPT - - - - (do not include this line in the script)

Now you are ready to run the script, connect to the database, and retrieve the MySQL server version information. Here is what the script looks like when I run it from the command line:

 

—————————————–

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.