Array of Hashes for Database Tables in Perl

Perl Two-Dimensional Structures – Part 3

Foreword: In this part of the series, I talk about Perl Array of Hashes structure for database tables and resultsets.

By: Chrysanthus Date Published: 2 Apr 2016

Introduction

This is part 3 of my series, Perl Two-Dimensional Structures. In this part of the series, I talk about Perl Array of Hashes structure for database tables and resultsets. A resultset table and a database table are similar. A Perl data structure is a combination of hashes and arrays. In this tutorial I talk about the Perl Array-of-Hashes structure. This is a 2D structure. You should have read the previous parts of the series, because this is a continuation.

Example Content
Imagine that you have a database table like the one given below. This is the same table I used in this volume for the two-dimensional array, in the Perl Professional course. In Perl, a 2D array is a general-purpose 2D data structure. You can use the 2D array for a spreadsheet or database table. However, the array-of-hashes is more appropriate for the database table (or resultset). The database table example I use in this tutorial is:

ProductID, ProductName, Category, Number, CostPrice, SellingPrice
1, TV Set, Entertainment, 50, 25, 30
2, VCD, Entertainment, 50, 20, 25
3, Clothe Box, Household, 45, 16, 21
4, Perfume, Beauty, 100, 2, 3
5, Banana, Fruit, 125, 5, 7
6, Pear, Fruit, 135, 3, 4

There is a header row for the table. The rest of the rows have data. In this table, there are 6 columns. The first cell of each column has a header for the column.

Coding with Perl
You can now have a Perl two-dimensional structure to hold the data for a database table. The most relevant is the array of hashes. In this case, you have one array, where each value of the array is a hash. The array is a one-dimensional array. The indices of the array are the indices of the rows; nothing stops you from using these indices as the Surrogate Key (auto-increment key). So, each index (array cell) has a hash. The data for each table row are the values of the hash. A hash consists of key/value pairs. A hash key of a key/value pair (element) takes the name of the corresponding column. So, this Perl data structure (array-of-hashes), does not need any header row. Remember, the value of the element (key/value pair) of a hash can be a number or a string.

Creating an Array of Hashes
You can create the array by declaration or by initialization. When the array is created by declaration it has one approach of accessing the elements. When it is created by initialization it has another approach of accessing the elements.

Structure by Declaration
You create Array-of-Hashes structure by declaration by declaring a simple array, e.g.

    my @arr;

The next thing is to populate the structure.

Accessing an Array Element
You access an element in a two dimensional array-of-hashes with the following syntax:

    $arrayName[index]{'hashKey'}

Populating the Structure declared using One-by-One Insertion
In this section I explain how you can populate the array-of-hashes structure, one element at a time. You can place elements into an array-of-hashes structure, one-by-one. You do this using the above syntax. The following code places the first three data rows (excluding the header) of the above table into the structure, one element at a time:

use strict;

    my @arr;

    $arr[0]{'ProductID'} = 1;
    $arr[0]{'ProductName'} = "TV Set";
    $arr[0]{'Category'} = "Entertainment";
    $arr[0]{'Number'} = 50;
    $arr[0]{'CostPrice'} = 25;
    $arr[0]{'SellingPrice'} = 30;
    $arr[1]{'ProductID'} = 2;
    $arr[1]{'ProductName'} = "VCD";
    $arr[1]{'Category'} = "Entertainment";
    $arr[1]{'Number'} = 25;
    $arr[1]{'CostPrice'} = 20;
    $arr[1]{'SellingPrice'} = 25;
    $arr[2]{'ProductID'} = 3;
    $arr[2]{'ProductName'} = "Clothe Box";
    $arr[2]{'Category'} = "Household";
    $arr[2]{'Number'} = 45;
    $arr[2]{'CostPrice'} = 16;
    $arr[2]{'SellingPrice'} = 21;

The first three row data elements (without the header row) of the table, have been inserted into the array. Because of the syntax of inserting the elements, you end up with an array-of-hashes. You can use this same syntax to change the elements (values).

Reading Values from an Array-of-Hashes One-by-One
The syntax to read a value from a two-dimensional array-of-hashes structure, into a variable is:

    $var = $arrayName[index]{'key'};

So you read a value in a similar way that you place the value. The following code would place the first three data rows of the above table into an array-of-hashes and then print them out from the structure.

use strict;

    my @arr;

    $arr[0]{'ProductID'} = 1;
    $arr[0]{'ProductName'} = "TV Set";
    $arr[0]{'Category'} = "Entertainment";
    $arr[0]{'Number'} = 50;
    $arr[0]{'CostPrice'} = 25;
    $arr[0]{'SellingPrice'} = 30;
    $arr[1]{'ProductID'} = 2;
    $arr[1]{'ProductName'} = "VCD";
    $arr[1]{'Category'} = "Entertainment";
    $arr[1]{'Number'} = 25;
    $arr[1]{'CostPrice'} = 20;
    $arr[1]{'SellingPrice'} = 25;
    $arr[2]{'ProductID'} = 3;
    $arr[2]{'ProductName'} = "Clothe Box";
    $arr[2]{'Category'} = "Household";
    $arr[2]{'Number'} = 45;
    $arr[2]{'CostPrice'} = 16;
    $arr[2]{'SellingPrice'} = 21;

    foreach my $i (0..2)
        {
            foreach my $var (@arr[$i])
                {
                    print $arr[$i]{'ProductID'},  ', ';
                    print $arr[$i]{'ProductName'},  ', ';
                    print $arr[$i]{'Category'},  ', ';
                    print $arr[$i]{'Number'},  ', ';
                    print $arr[$i]{'CostPrice'},  ', ';
                    print $arr[$i]{'SellingPrice'},  ', ';
                }
            print "\n";
        }

Read and try the code. The foreach loop nested in a for-loop is used to read the values of all the elements in the 2D structure. Note: @arr[$i] with the @ sign returns a list of references; and in the foreach word line, it needs to be in parentheses. After printing the last item in a row, you do not need a comma, so after the last print statement in the foreach loop, you do not need ', '. For my system, the output is:

1, TV Set, Entertainment, 50, 25, 30
2, VCD, Entertainment, 25, 20, 25
3, Clothe Box, Household, 45, 16, 21

Placing Elements into an Array-of-Hashes Row-by-Row
You can place elements into a 2D array-of-hashes one row at a time. The syntax to place a row into an array-of-hashes is:

    $arrayName[index] = {key1=>value1, key2=>value2, key3=>value3, …};

Remember, a row here is a hash and it returns a reference to the array indexed cell; that is, the reference of the hash is the value of the row cell; the row cell is determined by its index. Note that the elements of the hash are in curly brackets and not in arc or square brackets. The following program places all the rows of the above table into an array-of-hashes and then displays them (the data); the header row is not considered in the code because each hash element has its column header name as key.

use strict;

    my @arr;

    @arr[0] = {ProductID=>1, ProductName=>"TV Set", Category=> "Entertainment", Number=>50, CostPrice=>25, SellingPrice=>30};
    @arr[1] = {ProductID=>1, ProductName=>"VCD", Category=>"Entertainment", Number=>50, CostPrice=>20, SellingPrice=>25};
    @arr[2] = {ProductID=>3, ProductName=>"Clothe Box", Category=>"Household", Number=>45, CostPrice=>16, SellingPrice=>21};
    @arr[3] = {ProductID=>4, ProductName=>"Perfume", Category=>"Beauty", Number=>100, CostPrice=>2, SellingPrice=>3};
    @arr[4] = {ProductID=>5, ProductName=>"Banana", Category=>"Fruit", Number=>125, CostPrice=>5, SellingPrice=>7};
    @arr[5] = {ProductID=>6, ProductName=>"Pear", Category=>"Fruit", Number=>135, CostPrice=>3, SellingPrice=>4};

    foreach my $i (0..$#arr)
        {
            foreach my $var (@arr[$i])
                {
                    print $arr[$i]{'ProductID'},  ', ';
                    print $arr[$i]{'ProductName'},  ', ';
                    print $arr[$i]{'Category'},  ', ';
                    print $arr[$i]{'Number'},  ', ';
                    print $arr[$i]{'CostPrice'},  ', ';
                    print $arr[$i]{'SellingPrice'},  ', ';
                }
            print "\n";
        }

The vertical length of the 2D array-of-hashes structure is the length of the 1D array, and is returned by @arr.

Structure by Initialization
If you try to learn Perl from the specification or manual you would probably find the learning process difficult. Specifications or manuals are written for experts. A teacher like me has to read the manual and then present the knowledge in learnable form. That, I believe is what I have done for this Perl Advanced Course (link below), which I assume you are going through.

Back to 2D array-of-hashes: “Array-of-Hashes by Initialization” is a phrase I have borrowed from C++. It means creating an array-of-hashes structure where the left hand operand to the assignment operator is a variable and the right hand operand is the content (value) of the structure. The content is within special symbols.

With Perl, for the 2D initialization array-of-hashes, you have the variable, this time with the scalar symbol, $ and not the array symbol, @. Note, we are creating an array-of-hashes by initialization and not by the normal way; so there is a difference. So you would have something like,

    my $aohRef;

for the array declaration, where aohRef is the array-of-hashes (aoh) name. $aohRef is actually a reference (scalar variable that would hold a reference to an array).

The right hand operand begins with [ and ends with ] immediately followed by “;”. Inside the square brackets pair, you have rows. Each row is a one-dimensional hash. A normal hash is a 1D structure even though it has two columns for the key/value pairs. Each 1D row is delimited by curly brackets pair. The 1D rows are separated by commas (,) and not semicolons (;). Within each 1D hash, the elements are separated by commas. There is no comma after the last element in a 1D row and no comma after the last 1D row. So, the square brackets actually consist of hashes, where each hash is a row. Read and try the following example that illustrates this:

use strict;

    my $aohRef = [
                    {ProductID=>1, ProductName=>"TV Set", Category=> "Entertainment", Number=>50, CostPrice=>25, SellingPrice=>30},
                    {ProductID=>1, ProductName=>"VCD", Category=>"Entertainment", Number=>50, CostPrice=>20, SellingPrice=>25},
                    {ProductID=>3, ProductName=>"Clothe Box", Category=>"Household", Number=>45, CostPrice=>16, SellingPrice=>21},
                    {ProductID=>4, ProductName=>"Perfume", Category=>"Beauty", Number=>100, CostPrice=>2, SellingPrice=>3},
                    {ProductID=>5, ProductName=>"Banana", Category=>"Fruit", Number=>125, CostPrice=>5, SellingPrice=>7},
                    {ProductID=>6, ProductName=>"Pear", Category=>"Fruit", Number=>135, CostPrice=>3, SellingPrice=>4}
                 ];

    print $$aohRef [1]{'ProductName'};

To access an element, you type the variable name preceded by two consecutive $ symbols. You use the two different types of brackets as expected.

Do not confuse between the creation of an array by initialization and an array-of-hashes by initialization; the constructs and accessing methods are different.

Conclusion
To create a data structure of an array of hashes, you start with an array that runs down in one dimension. Each row of the structure is a hash that fits into an element (index) of the array. A hash is actually the effective value of each array element. The value of the array element (index) is a reference to a hash. There is no need to code the header of the database table in the structure; this is because each element of the structure is a key/value pair and the key is the name of its column (header).

That is it for this part of the series. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Array of Hashes for Database Tables in Perl

Perl Two-Dimensional Structures – Part 3

Introduction

Related Links

Comments