http://qs321.pair.com?node_id=896028

The following is intended as a very basic introduction to getting up and going with databases using Perl and DBI. SQLite (through 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.

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.

Ok, so lets dive straight in by creating a simple database containing a single table of two columns:

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 $@; }

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.

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.

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; }

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.

You may have noticed that there is no mention of DBD::SQLite in the code. Not to worry, DBI figures out from the connect string (see #1 and discussion below) which database driver it should use.

The code following #1 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.

The #2 code then configures some error handling related stuff. Setting AutoCommit to 0 sets the database to a mode where changes to the database are effectively buffered until commit 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 rollback (if something failed during processing for example).

Setting RaiseError 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.

Taken together setting AutoCommit to 0 and RaiseError 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.

Now take a look at #3. The string is the SQL statement that is used to generate the People table. The table contains two columns of data named name and age. The name column is set to contain variable length strings up to 128 characters long. The age 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.

Tucked away in the safety of an eval following #4 are the two statements that actually result in creating the table and updating the database: the do and commit lines. do is actually short hand for two statements: prepare and execute. 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.

Let's move on. Time to add some data to our table:

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 fail +ed!"; die $err; }; print "Added $entries rows to the People table\n";

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.

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 People table, in fact the data given in the %people hash in the script.

As in the first script a little SQL and a couple of function calls get the work done. The SQL statement at #1 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 execute (#4).

The prepare call generates a statement handle that is used to insert the data. The while loop (#3) iterates over the key/value pairs in the %people hash. For each iteration of the loop the execute (#4) inserts a name/age pair. Notice that two parameters ($name, $age) are passed in and match the two place holders (?, ?) in the SQL statement. When the loop completes commit (#5) is called to complete the transaction and commit the changes to the database.

Actually the whole insert loop can be replaced with:

$entries = $sth->execute_array ({}, [keys %people], [values %people]);

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.

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?

#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'}; } };

This time the script prints the results rather than updating the database. The SQL (#1) 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 (#3) fetches the data from the database and the while loop (#4) iterates over the rows that were fetched one at a time to print the results.

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.

Thank you mje and JavaFan for your contributions incorporated in this tutorial which were provided in reply to the original meditation (see RFC: Databases made easy)

Update Changed SELECT example to use place holders as "suggested" by Tanktalus.

True laziness is hard work

Replies are listed 'Best First'.
Re: Databases made easy
by Tanktalus (Canon) on Apr 03, 2011 at 02:07 UTC

    Tutorials on DBI are definitely needed and welcome - though I'd like to point out that any tutorial that doesn't use placeholders is likely doing a disservice. It doesn't take much more code to use placeholders, and helps newbies get used to them. In working with a number of developers over the years, in multiple languages, including some developers who don't merely write code against database but actually write databases, I see too many of them not using placeholders. Sometimes, as in your example, it doesn't matter. Sometimes they start putting user input directly into their SQL.

    It doesn't hurt (much) to use placeholders for constant values, but it shows how it's done so that people following your (otherwise excellent and useful) example know how to interpolate their variables into their SQL (by not interpolating). For those new to using databases, it might seem obvious to say my $sql = qq{SELECT name, age, FROM People WHERE name LIKE '%$suffix' AND age < $maxage}; just based on your example. If, however, your example were using placeholders, it would merely seem obvious to replace the bound values with the variables they want to use. I've had to explain this to three different co-workers in the last year alone.

    Thanks for the example :-)

Re: Databases made easy (interview Richard Hipp)
by erix (Prior) on Jul 04, 2021 at 09:41 UTC

    OT, perhaps, but I thought this was fun: an interview with Richard Hipp, the creator of SQLite, with lots of details from the early days of SQLite. A podcast, but with transcript (July 2021).

    SQLite with Richard Hipp

    PS

    As a postgres aficionado, I especially like his story about the huge test-suite of SQL-statements that crashed all RDBMS-comers *except* PostgreSQL. But it also shows how well tested SQLite is. Hipp:

    "Shane Harrelson did this for us about 10 years ago. He came up with this huge corpus of SQL statements, and he ran them against every database engine that he could get his hands on. We wanted to make sure everybody got the same answer, and he managed to segfault every single database engine he tried, including SQLite, except for Postgres. Postgres always ran and gave the correct answer. We were never able to find a fault in that. The Postgres people tell me that we just weren’t trying hard enough. It is possible to fault Postgres, but we were very impressed.

    We crashed Oracle, including commercial versions of Oracle. We crashed DB2. Anything we could get our hands on, we tried it and we managed to crash it, but the point was that we wanted to make sure that SQLite got the same answers for all of these queries."
Re: Databases made easy
by erix (Prior) on Apr 26, 2012 at 17:45 UTC

    SQLite remains a toy-database, its limitations honestly admitted by the sqlite crew on the sqlite site.

    IMHO, one might as well start out with a real database and conquer the teething problems that might be caused by installing PostgreSQL. You won't regret it.

    To use PostgreSQL, replace the SQLite connection string:

    dbi:SQLite:Test.sqlite

    with a connection string for PostgreSQL, for instance:

    dbi:Pg:port=5421;db=testdb

    The three parts in the above tutorial (create table, insert rows, select rows) will run the same on this postgresql $dbh (data base handle).

    The PostgreSQL flavour of SQL is here: sql-commands.html

      In the very first paragraph of the OP I explain why I use SQLite and PostgreSQL is an excellent example of why I suggest people start with SQLite.

      To suggest using PostgreSQL (or MySQL, or any of myriad other database engines) is a simple as replacing the connect string with a different connect string amounts to deliberate infliction of pain and suffering. None of the non-"toy" databases are trivial to install and get going. I have no experience with PostgreSQL, but I bet it is little different to install and configure than MySQL, and that can be a right royal PITA.

      The intent of the OP was to provide an easy introduction to database use and SQLite, with its trivial install and very easy configuration (i.e. none), is excellent for the purpose. Toys have their place and if SQLite is a toy, then it is an superb one where most of its widgets and gadgets work just like the real thing that grown-ups use.

      People almost always start learning on a toy or reduced functionality version of anything to reduce the barrier to learning. Should we make it harder to learn to use a database by requiring extra layers of set up before you can write a single line of code just so you can say you are using a professional strength version?

      True laziness is hard work