Perl ABC

  1. Perl
    2. ›
  2. database
    3. ›
  3. here

Basics of database operation learned with SQLite

Databases using Perl have been a bit of a challenge for beginners to learn. First of all, I needed to build an SQL execution environment and could not meet the demand for studying immediately.

But now this situation has changed. Now, when you download ActivePerl, a module called DBD::SQLite for using SQLite is packaged.

A module called DBI, which is the interface part seen by the user for connecting to the database, is also packaged.

So you can just download the latest version of ActivePerl and start learning the database without building any environment.

Make sure DBD::SQLite and DBI are installed

First check if DBD::SQLite and DBI are installed. You can check if these modules are installed with the command "perldoc" to view the Perl documentation from the command prompt. 

perldoc DBD::SQLite

perldoc DBI

If these modules are not installed, you can install DBD::SQLite using a package manager for Windows called ppm. You may also want to install the latest version of ActivePerl.

Here are the steps to install a new ActivePerl.

Install ActivePerl

If you have the old ActivePerl installed, you will need to uninstall and then install the old ActivePerl from Control Panel> Add/Remove Programs, or rename the directory where you want to install Perl.

Once you have the latest ActivePerl installed, make sure you have "DBD::SQLite" and "DBI" installed.

Here are the steps to install in ppm. 

ppm install DBD-SQLite

ppm install DBI

Anyone using Unix or Linux Just install DBD::SQLite and DBI with the cpan command. (You may be able to install it on Windows using the cpan command, but DBD::SQLite may fail to compile.) 

cpan DBD::SQLite

cpan DBI

Now you are ready to learn the database.

Connect to database

Now let's connect to the database. Use the DBI module's connect method to connect to the database. The first argument of connect specifies the information to connect to the database called data source. The format is a bit special, but let's remember.

You can specify the database name to connect to in the data source. In SQLite, the database is simply created as a file, so specify any name you like as the database name. The return value is an object called the database handle that communicates with the database. 
use DBI;
my $database = 'test.db';
my $data_source = "dbi: SQLite: dbname = $database";
my $dbh = DBI->connect($data_source);

You should now have a file called test.db in your current directory.

Disconnect database

Let's remember here the process of disconnecting the database that is paired with the database connection. Use disconnect to disconnect the database. 

$dbh->disconnect;

It is wise to disconnect when you no longer need to connect to the database.

Execute SQL

SQL is the query language used to create tables, register data in databases, and retrieve data. I will explain the basic SQL from now on. Here, we will first explain how to execute SQL.

There are two ways to execute SQL.

  1. How to execute directly using the do method
  2. How to prepare SQL statement with the prepare method and then execute it with the execute method

The second method is used to execute what is called select statement in SQL. The first method is used to execute SQL other than select statement. Remember "select is prepare and execute" and "otherwise do".

Create a table

Now let's create the simplest SQL to create a table. Use the "create table" statement to create a table. In the "create table" statement, specify the name of the table to be created and the field names to be included in the table. "In SQLite, you don't necessarily have to specify the data type of the field, so I'll omit the type here for simplicity.

SQL that creates a table with the table name book and the fields title and author. 
create table book (
  title,
  author
);

Now let's actually create a table using the do method. 

my $create_table = <<'EOS';
create table book (
  title,
  author
)
EOS

$dbh->do($create_table);

You now have a table named book.

Drop the table

Now that you've learned how to create a table, let's also learn how to delete a table, which is the opposite operation. Use the "drop table" statement to drop a table. 

drop table table name

Let's drop the table using do. 

my $drop_table = "drop table book";
$dbh->do($drop_table);

Add record insert

Use insert statement to insert a record into the table. 

insert into table name (field name 1, field name 2, ..) values (value 1, value 2, value 3);

To add a record with "title" to "Perl" and "author" to "taro" to the table, the SQL would be: If the data is a string, enclose it in single quotes (although this is a very dangerous security method and a workaround will be explained later). 

insert into book (title, author) values ('Perl', 'taro');

Let's execute an insert statement using do. Please wait until select statement is explained a little further to confirm whether it has been inserted. 

my $insert = "insert into book (title, author) values ('Perl', 'taro');";
$dbh->do($insert);

This will add a record like the one below to the table. 

+ - -- --- + - -- -- -- +
# | title | author | header (this can be done when you create the table)
+ - -- --- + - -- -- -- +
# | Perl | taro | record(record added by insert statement)
+ - -- --- + - -- -- -- +

Record update update

Use update statement to update the data in the table. Generally, you can narrow down the update target by specifying the search conditions in the where section. As explained in select statement, multiple conditions can be specified in the where section. 

update table name set field name 1 = value 1, feel name 2 = value 2, .. where to search field name = value

The SQL to change the title value of a record whose "author" is "taro" to Ruby is as follows. 

update book set title = 'Ruby' where author = 'taro'

Let's execute update statement using do. 

my $update = "update book set title = 'Ruby' where author = 'taro'";
$dbh->do($update);

The previous record is updated and looks like this: 

+ - -- --- + - -- -- -- +
# | title | author | header (this can be done when you create the table)
+ - -- --- + - -- -- -- +
# | Ruby | taro | record(record updated by update statement)
+ - -- --- + - -- -- -- +

Delete record delete

Use delete statement to delete a record. Generally, the search condition is specified in the where section to narrow down the deletion target. 

delete from table name where field name to search = value

The SQL to delete a record whose "author" is "taro" looks like this: 

delete from book where author = 'taro';

Let's execute delete statement using do. 

my $delete = "delete from book where author = 'taro';";
$dbh->do($delete);

The record will be deleted.

Get record select

It's finally select statement. Up to this point, you couldn't check the added record or deleted record, but you can check it by remembering select statement. Now let's write the simplest select statement. To get all the columns in the specified table: * Means all fields. 

select * from table name;

To get all the records in book, the SQL is as follows. 

select * from book;

Execution of select statement cannot be done with do. To execute select statement, you need to prepare SQL statement with a method called prepare and then execute it with execute. The prepare method returns what is called a statement handle that contains the prepared SQL. Execute execute from this statement handle.

Now let's actually execute select statement. 

my $select = "select * from book;";
my $sth = $dbh->prepare($select);
$sth->execute;

You can now execute select statement. The next thing you need to do is actually get each row by an operation called fetch. To fetch a row and assign it to an array, use a method called fetchrow_array from the statement handle. 

$sth->fetchrow_array;

This is usually repeated using while statement until you get all the rows. 

while (my @row = $sth->fetchrow_array) {
  ...
}

In SQLite, it is convenient to create a database in memory

SQLite allows you to create a database in memory. You don't need to specify a file name, and the table will be deleted automatically when you disconnect the database, which is very convenient if you want to check the operation a little. For most of the explanations here, I will create a database in memory and explain it.

To create a database in memory, specify ": memory:" as the database name of the data source. 

my $dbh = DBI->connect("dbi: SQLite: dbname =: memory:");

You can now connect to the database in memory.

Example code for insert, update, delete, select that can actually be run

So far, I've only explained, so I think it was difficult to get an image. Here are some examples. Try running the source code and get used to working with records in the database. If you understand this, you have mastered the basics of database basics. 

use strict;
use warnings;

use DBI;

# Connect to database in memory
my $dbh = DBI->connect("dbi: SQLite: dbname =: memory:");

# Create table
my $create_table = <<'EOS';
create table book (
  title,
  author
)
EOS

$dbh->do($create_table);

# Execution of insert statement
$dbh->do("insert into book (title, author) values ('Perl', 'taro');");
$dbh->do("insert into book (title, author) values ('C ++', 'taro');");
$dbh->do("insert into book (title, author) values ('Python', 'ken');");
$dbh->do("insert into book (title, author) values ('Java', 'mamoru');");

# Execution of update statement
$dbh->do("update book set title = 'Ruby' where author = 'mamoru'");

# Execution of delete statement
$dbh->do("delete from book where author = 'ken';");

# Execution of select statement
my $sth = $dbh->prepare("select * from book;");
$sth->execute;

# Fetch each row
while (my @row = $sth->fetchrow_array) {
  # Output each line
  print join(',', @row), "\n";
}

# Database disconnection
$dbh->disconnect;

You will get output similar to the following: 

Perl, taro
C ++, taro
Ruby, mamoru

Connect by specifying the user name and password

No username or password is required to connect to the database created by SQLite. However, standard databases such as MySQL, PostgreSQL, and Oracle require a username and password to be specified when connecting.

To connect using DBI with a username and password: Specify the user name in the second argument and the password in the third argument. 

my $dbh = DBI->connect($data_source, $user, $password);

Standard settings for DBI options

In DBI, you can specify DBI options in the 4th argument of the connect method. 

DBI->connect($data_source, $user, $password, {opt1 => $val1, opt2 => $val2, ..});

DBI's default behavior is to print a warning to standard error if an error occurs.

However, I think it is better to output an error message instead of a warning and exit the program.

To prevent the warning from being output, do the following. 

PrintError => 0

To output an error message and terminate the program, do the following. 

RaiseError => 1

DBI also has an option called AutoCommit. This is an option that decides whether or not to do something called commit automatically. The default value may differ depending on the database driver, so it is better to specify it explicitly. In my case I want it to commit automatically and set AutoCommit to 0 when a transaction is needed.

Therefore, when connecting to the database based on this, it may be better to write as follows. 

my $dbh = DBI->connect(
  $data_source,
  $user,
  $password,
  {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
  }
);

If you don't need a username or password and just want to specify options, do the following: 

my $dbh = DBI->connect(
  $data_source,
  undef,
  undef,
  {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
  }
);

Install SQLite client sqlite3

If you install SQLite on Windows, you will be able to execute SQL directly from the command prompt. SQL can be executed programmatically using DBI, but it is better to have an environment where it can be executed interactively for learning SQL.

How to install SQLite on Windows is explained here.

Install SQLite on Windows

Related Informatrion

Perl Club