perltutorial
GrandFather
<p>The following is intended as a very basic introduction to getting up
and going with databases using Perl and [mod://DBI]. SQLite (through
[mod://DBD::SQLite]) is assumed for all the sample code below because
it is very easy to get going (just install the module) and for many
people will be the only database they need for quite a while.</p>
<readmore>
<p>It is usual in this sort of tutorial to pass over the error handling.
Despite that I've generally included error handling in the samples
below. I also use transacted processing from the get go. Although this
tutorial is intended to introduce the very basics of databases, it is
also intended to demonstrate safe and robust technique right from the
start.</p>
<p>Ok, so lets dive straight in by creating a simple database containing
a single table of two columns:</p>
<code>
use strict;
use warnings;
use DBI;
#1
my $dbh = DBI->connect ("dbi:SQLite:Test.sqlite");
die "connect failed: $DBI::errstr" if ! $dbh;
#2
local $dbh->{AutoCommit} = 0; # Use transacted processing
local $dbh->{RaiseError} = 1; # die on processing errors
local $dbh->{PrintError} = 0; # Suppress printing errors
#3
my $sql =
qq{CREATE TABLE People (name VARCHAR(128), age INTEGER)};
#4
eval {
$dbh->do ($sql);
$dbh->commit ();
};
#5
if ($@) {
# An error occurred
$dbh->rollback ();
die $@;
}
</code>
<p>Assuming the script ran without errors you may be wondering if
anything actually happened? As a first check look in the directory you
ran the script from for a file called Test.sqlite. If the file exists the
database has been created. If you can't find it most likely the current
directory when you ran the script is not what you thought it was - I'll wait a
moment while you figure it out. </p>
<p>If an error did occur (maybe you don't have write permission for the
current directory) the file may have been generated in any case, but the
table probably wasn't created. Much more robust error handling provided by JavaFan in reply to the original meditation is shown below. Note too that
some databases don't provide roll back support for some operations including
table creation.</p>
<c>
local $dbh->{RaiseError} = 1;
local $dbh->{PrintError} = 0;
eval {
$dbh->begin_work;
... queries go here ...
$dbh->commit;
1;
} or do {
my $err = $@ || "Unknown error";
eval {
$dbh->rollback;
1;
} or do {
$err .= "[ROLLBACK FAILED: " . ($@ || "Unknown reasons") . "]";
}
die $err;
}
</c>
<p>With the file found you can use one of several tools to examine the
database. An internet search for "SQLite Browser" should find a copy
of SQLite Database Browser which will open the database file and allow
you to examine and manipulate it. If you use Firefox then the SQLite
Manager add-on may be the thing for you. In either case point the tool
at Test.sqlite and confirm that it contains a People table with name
and age columns. Such database management tools help enormously in
checking that your database software is doing as you expect and in
fixing the occasional processing glitch or performing adhoc queries.</p>
<p>You may have noticed that there is no mention of [mod://DBD::SQLite]
in the code. Not to worry, [mod://DBI] figures out from the connect
string (see <b>#1</b> and discussion below) which database driver it
should use. </p>
<p>The code following <b>#1</b> connects to the database (creating the
database file if it doesn't exist already). The connect string
specifies the database driver type and provides information to the
driver about the connection. For SQLite the driver uses the third part
of the connection string as the file name for the database.</p>
<p>The <b>#2</b> code then configures some error handling related stuff.
Setting <code>AutoCommit</code> to 0 sets the database to a mode where
changes to the database are effectively buffered until
<code>commit</code> is called. This allows a related set of changes to
the database to be effected in a single call or else easily undone by
a call to <code>rollback</code> (if something failed during processing
for example).</p>
<p>Setting <code>RaiseError</code> to 1 causes errors to be handled by
using die when the error happens rather than setting an error flag and
hoping that the calling code will notice the issue.</p>
<p>Taken together setting <code>AutoCommit</code> to 0 and
<code>RaiseError</code> to 1 help make database processing very robust
because errors get handled when they happen and the database doesn't
get left in a corrupt state if processing fails in some fashion.</p>
<p>Now take a look at <b>#3</b>. The string is the SQL statement that is
used to generate the <b>People</b> table. The table contains two
columns of data named <b>name</b> and <b>age</b>. The <b>name</b>
column is set to contain variable length strings up to 128 characters
long. The <b>age</b> column is set to contain integer values. Note
though that SQLite uses the type information provided in the create
statement as a hint to how the data is expected to be used, but
doesn't feel obliged to actually store it that way. In this regard
SQLite is much more forgiving than many other database engines. Note
too that SQL is case insensitive for key words, but by convention they
are given in upper case.</p>
<p>Tucked away in the safety of an [doc://eval] following <b>#4</b> are
the two statements that actually result in creating the table and
updating the database: the <code>do</code> and <code>commit</code>
lines. <code>do</code> is actually short hand for two statements:
<code>prepare</code> and <code>execute</code>. It can be used when a
statement handle is not returned (more about statement handles later)
and where the SQL statement only needs to be executed once.</p>
<p>Let's move on. Time to add some data to our table:</p>
<code>
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect ("dbi:SQLite:Test.sqlite");
die "connect failed: $DBI::errstr" if ! $dbh;
$dbh->{AutoCommit} = 0; # Use transacted processing
$dbh->{RaiseError} = 1; # die on processing errors
my %people = (
Fred => 23,
Jane => 22,
Boyd => 27,
Tania => 28,
Julie => 27,
Kyle => 21,
);
#1
my $sql = qq{INSERT INTO People (name, age) VALUES (?, ?)};
my $entries = 0;
eval {
my $sth = $dbh->prepare ($sql); #2
while (my ($name, $age) = each %people) { #3
++$entries if $sth->execute ($name, $age); #4
}
$dbh->commit (); #5
return 1;
} or do {
my $err = $@ || "Unknown error inserting data";
eval {$dbh->rollback ()} or $err .= "\n Rollback processing failed!";
die $err;
};
print "Added $entries rows to the People table\n";
</code>
<p>This code starts just the same as the previous script with connection
to the database and setting up of the error handling. In later sample
scripts this boilerplate code will be omitted, but you'll need to
provide it for the scripts to run.</p>
<p>Again you can use your SQLite browser/manager to confirm that the
script has done some work. Notice that there is now some data in the
<b>People</b> table, in fact the data given in the
<code>%people</code> hash in the script.</p>
<p>As in the first script a little SQL and a couple of function calls get
the work done. The SQL statement at <code>#1</code> is used to set up
a statement handle that is used to insert data into the table. The
VALUES part gives the values to be inserted. Notice that question
marks (place holders) are used in place of the actual data. It is
possible to supply the data in the SQL statement, but that should be
avoided for several reasons (to avoid quoting issues and injection
attacks for a start). The data to be inserted is supplied later in the
<code>execute</code> (<b>#4</b>).</p>
<p>The <code>prepare</code> call generates a statement handle that is
used to insert the data. The while loop (<b>#3</b>) iterates over the
key/value pairs in the <code>%people</code> hash. For each iteration
of the loop the <code>execute</code> (<b>#4</b>) inserts a name/age
pair. Notice that two parameters (<code>$name, $age</code>) are passed
in and match the two place holders (<code>?, ?</code>) in the SQL
statement. When the loop completes <code>commit</code> (<b>#5</b>) is
called to complete the transaction and commit the changes to the
database.</p>
<p>Actually the whole insert loop can be replaced with:</p>
<code>
$entries = $sth->execute_array ({}, [keys %people], [values %people]);
</code>
<p>which is both more compact and very likely more efficient, but only
works if you can efficiently build the parallel arrays of values. Notice
that an array reference is passed in for each place holder in the SQL
statement. For the moment you can ignore the empty hash reference
passed as the first parameter - it's there so you can pass attributes
in to execute, but much of the time it's not needed.</p>
<p>Thus far we have created a database, created a table and added some
data to it. But what a database is really about is getting the data
back out again. Most often we only want to get a subset of the data
out of a database by searching through the data using some criteria
for selecting the data we want. Say for example we wanted to search
the data we've added to the database for people whose name ends in the
letter 'e' and with an age less than 25?</p>
<code>
#1
my $sql = qq{SELECT name, age FROM People WHERE name LIKE ? AND age < ?};
eval {
#2
my $sth = $dbh->prepare ($sql);
$sth->execute ('%e', 25); #3
#4
while (my $row = $sth->fetchrow_hashref ()) {
printf "%-10s %3d\n", @{$row}{'name', 'age'};
}
};
</code>
<p>This time the script prints the results rather than updating the
database. The SQL (<b>#1</b>) is a select statement which will
retrieve rows containing name and age columns from the People table
where the name ends in the letter 'e' (% matches anything, rather like
.* in a Perl regular expression) and age is less than 25. The execute
(<b>#3</b>) fetches the data from the database and the while loop (<b>#4</b>)
iterates over the rows that were fetched one at a time to print the results.</p>
<p>Ok, that's got a simple single table database created, some data added
and a search made on it. Databases provide a vast amount more power
than we have looked at so far, and a lot of that comes when several
tables are involved in a search, but that is a tutorial for another
day. Mean time the documentation for the flavour of SQL understood by
SQLite at [http://www.sqlite.org/lang.html] is a good reference for
SQL.</p>
<p>Thank you [mje] and [JavaFan] for your contributions incorporated
in this tutorial which were provided in reply to the original
meditation (see [id://894254])</p>
<p><small><b>Update</b> Changed SELECT example to use place holders as "suggested" by [Tanktalus].</small></p>
<!-- Generated using PerlMonks editor version 1 -->
<div class="pmsig"><div class="pmsig-461912">
True laziness is hard work
</div></div>