Perl - доступ к базе данных

В этой главе вы узнаете, как получить доступ к базе данных внутри вашего скрипта Perl. Начиная с Perl 5 стало очень легко писать приложения баз данных с использованием модуля DBI . DBI означает независимый от базы данных интерфейс для Perl, что означает, что DBI обеспечивает уровень абстракции между кодом Perl и базовой базой данных, позволяя вам действительно легко переключать реализации базы данных.

DBI — это модуль доступа к базе данных для языка программирования Perl. Он предоставляет набор методов, переменных и соглашений, которые обеспечивают согласованный интерфейс базы данных, независимо от фактической используемой базы данных.

Архитектура приложения DBI

DBI не зависит от любой базы данных, доступной в бэкэнде. Вы можете использовать DBI независимо от того, работаете ли вы с Oracle, MySQL или Informix и т. Д. Это ясно из следующей диаграммы архитектуры.

Здесь DBI отвечает за принятие всех команд SQL через API (т. Е. Интерфейс прикладного программирования) и за отправку их соответствующему драйверу для фактического выполнения. И, наконец, DBI отвечает за получение результатов от драйвера и возврат их вызывающему скрипту.

Обозначения и условные обозначения

В этой главе будут использоваться следующие обозначения, и рекомендуется также придерживаться того же соглашения.

$dsn    Database source name
$dbh    Database handle object
$sth    Statement handle object
$h      Any of the handle types above ($dbh, $sth, or $drh)
$rc     General Return Code  (boolean: true=ok, false=error)
$rv     General Return Value (typically an integer)
@ary    List of values returned from the database.
$rows   Number of rows processed (if available, else -1)
$fh     A filehandle
undef   NULL values are represented by undefined values in Perl
\%attr  Reference to a hash of attribute values passed to methods

Подключение к базе данных

Предполагая, что мы собираемся работать с базой данных MySQL. Перед подключением к базе данных убедитесь в следующем. Вы можете воспользоваться помощью нашего учебника по MySQL, если вы не знаете, как создать базу данных и таблицы в базе данных MySQL.

Вы создали базу данных с именем TESTDB.
Вы создали таблицу с именем TEST_TABLE в TESTDB.
В этой таблице есть поля FIRST_NAME, LAST_NAME, AGE, SEX и INCOME.
Идентификатор пользователя «testuser» и пароль «test123» установлены для доступа к TESTDB.
Perl Module DBI правильно установлен на вашем компьютере.
Вы прошли учебник по MySQL, чтобы понять основы MySQL.

Вы создали базу данных с именем TESTDB.

Вы создали таблицу с именем TEST_TABLE в TESTDB.

В этой таблице есть поля FIRST_NAME, LAST_NAME, AGE, SEX и INCOME.

Идентификатор пользователя «testuser» и пароль «test123» установлены для доступа к TESTDB.

Perl Module DBI правильно установлен на вашем компьютере.

Вы прошли учебник по MySQL, чтобы понять основы MySQL.

Ниже приведен пример соединения с базой данных MySQL «TESTDB» —

#!/usr/bin/perl

use DBI
use strict;

my $driver = "mysql"; 
my $database = "TESTDB";
my $dsn = "DBI:$driver:database=$database";
my $userid = "testuser";
my $password = "test123";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

Если соединение с источником данных установлено, то дескриптор базы данных возвращается и сохраняется в $ dbh для дальнейшего использования, в противном случае $ dbh устанавливается в значение undef, а $ DBI :: errstr возвращает строку ошибки.

ВСТАВИТЬ Операция

Операция INSERT требуется, когда вы хотите создать несколько записей в таблице. Здесь мы используем таблицу TEST_TABLE для создания наших записей. Поэтому, когда наше соединение с базой данных установлено, мы готовы создавать записи в TEST_TABLE. Ниже приведена процедура для создания отдельной записи в TEST_TABLE. Вы можете создать столько записей, сколько захотите, используя одну и ту же концепцию.

Создание записи включает следующие шаги:

Подготовка оператора SQL с оператором INSERT. Это будет сделано с использованием API prepare () .
Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью execute () API.
Отпустив ручку уставки. Это будет сделано с использованием API finish () .
Если все в порядке, передайте эту операцию, в противном случае вы можете откатить завершенную транзакцию. Фиксация и откат описаны в следующих разделах.

Подготовка оператора SQL с оператором INSERT. Это будет сделано с использованием API prepare () .

Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью execute () API.

Отпустив ручку уставки. Это будет сделано с использованием API finish () .

Если все в порядке, передайте эту операцию, в противном случае вы можете откатить завершенную транзакцию. Фиксация и откат описаны в следующих разделах.

my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                       (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                         values
                       ('john', 'poul', 'M', 30, 13000)");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Использование значений привязки

Возможен случай, когда вводимые значения не указаны заранее. Таким образом, вы можете использовать переменные связывания, которые будут принимать необходимые значения во время выполнения. Модули Perl DBI используют знак вопроса вместо фактического значения, а затем фактические значения передаются через API execute () во время выполнения. Ниже приведен пример —

my $first_name = "john";
my $last_name = "poul";
my $sex = "M";
my $income = 13000;
my $age = 30;
my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                        (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                          values
                        (?,?,?,?)");
$sth->execute($first_name,$last_name,$sex, $age, $income) 
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

ЧИТАЙТЕ Операцию

Операция READ для любого блока данных означает получение некоторой полезной информации из базы данных, то есть одной или нескольких записей из одной или нескольких таблиц. Поэтому, когда наше соединение с базой данных установлено, мы готовы сделать запрос в эту базу данных. Ниже приведена процедура для запроса всех записей, имеющих возраст больше 20. Это будет состоять из четырех шагов:

Подготовка запроса SQL SELECT на основе необходимых условий. Это будет сделано с использованием API prepare () .
Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью execute () API.
Извлечение всех результатов по одному и печать этих результатов. Это будет сделано с помощью API fetchrow_array () .
Отпустив ручку уставки. Это будет сделано с использованием API finish () .

Подготовка запроса SQL SELECT на основе необходимых условий. Это будет сделано с использованием API prepare () .

Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью execute () API.

Извлечение всех результатов по одному и печать этих результатов. Это будет сделано с помощью API fetchrow_array () .

Отпустив ручку уставки. Это будет сделано с использованием API finish () .

my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE 
                        WHERE AGE > 20");
$sth->execute() or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

Использование значений привязки

Возможен случай, когда условие не указано заранее. Таким образом, вы можете использовать переменные связывания, которые будут принимать необходимые значения во время выполнения. Модули Perl DBI используют знак вопроса вместо фактического значения, а затем фактические значения передаются через API execute () во время выполнения. Ниже приведен пример —

$age = 20;
my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE
                        WHERE AGE > ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

ОБНОВЛЕНИЕ Операция

ОБНОВЛЕНИЕ Операция над любой базой данных означает обновление одной или нескольких записей, уже доступных в таблицах базы данных. Ниже приведена процедура обновления всех записей, имеющих SEX как «M». Здесь мы увеличим ВОЗРАСТ всех мужчин на один год. Это займет три шага —

Подготовка SQL-запроса на основе обязательных условий. Это будет сделано с использованием API prepare () .
Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью execute () API.
Отпустив ручку уставки. Это будет сделано с использованием API finish () .
Если все в порядке, передайте эту операцию, в противном случае вы можете откатить завершенную транзакцию. В следующем разделе приведены API для фиксации и отката.

Подготовка SQL-запроса на основе обязательных условий. Это будет сделано с использованием API prepare () .

Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью execute () API.

Отпустив ручку уставки. Это будет сделано с использованием API finish () .

Если все в порядке, передайте эту операцию, в противном случае вы можете откатить завершенную транзакцию. В следующем разделе приведены API для фиксации и отката.

my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1 
                        WHERE SEX = 'M'");
$sth->execute() or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Использование значений привязки

$sex = 'M';
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1
                        WHERE SEX = ?");
$sth->execute('$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

В некоторых случаях вы хотели бы установить значение, которое не указано заранее, поэтому вы можете использовать значение привязки следующим образом. В этом примере доход всех мужчин будет установлен на 10000.

$sex = 'M';
$income = 10000;
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   INCOME = ?
                        WHERE SEX = ?");
$sth->execute( $income, '$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();

УДАЛЕНИЕ Операция

Операция DELETE требуется, когда вы хотите удалить некоторые записи из вашей базы данных. Ниже приведена процедура удаления всех записей из TEST_TABLE, где AGE равно 30. Эта операция будет выполнять следующие шаги.

Подготовка SQL-запроса на основе обязательных условий. Это будет сделано с использованием API prepare () .
Выполнение SQL-запроса для удаления необходимых записей из базы данных. Это будет сделано с помощью execute () API.
Отпустив ручку уставки. Это будет сделано с использованием API finish () .
Если все в порядке, передайте эту операцию, иначе вы можете откатить завершенную транзакцию.

Подготовка SQL-запроса на основе обязательных условий. Это будет сделано с использованием API prepare () .

Выполнение SQL-запроса для удаления необходимых записей из базы данных. Это будет сделано с помощью execute () API.

Отпустив ручку уставки. Это будет сделано с использованием API finish () .

Если все в порядке, передайте эту операцию, иначе вы можете откатить завершенную транзакцию.

$age = 30;
my $sth = $dbh->prepare("DELETE FROM TEST_TABLE
                         WHERE AGE = ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Использование do Statement

Если вы выполняете UPDATE, INSERT или DELETE, данные не возвращаются из базы данных, поэтому существует быстрый путь для выполнения этой операции. Вы можете использовать оператор do для выполнения любой команды следующим образом.

$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');

do возвращает истинное значение, если это удалось, и ложное значение, если это не удалось. Фактически, если это успешно, это возвращает число затронутых рядов. В этом примере будет возвращено количество фактически удаленных строк.

Операция COMMIT

Фиксация — это операция, которая дает зеленый сигнал базе данных для завершения изменений, и после этой операции никакие изменения не могут быть возвращены в исходное положение.

Вот простой пример вызова commit API.

$dbh->commit or die $dbh->errstr;

ROLLBACK Операция

Если вы не удовлетворены всеми изменениями или вы столкнулись с ошибкой между операциями, вы можете отменить эти изменения, чтобы использовать API отката .

Вот простой пример вызова API отката .

$dbh->rollback or die $dbh->errstr;

Начать транзакцию

Многие базы данных поддерживают транзакции. Это означает, что вы можете сделать целую кучу запросов, которые изменили бы базы данных, но на самом деле ни одно из этих изменений не было сделано. Затем в конце вы выдаете специальный SQL-запрос COMMIT , и все изменения вносятся одновременно. В качестве альтернативы вы можете выполнить запрос ROLLBACK, и в этом случае все изменения будут отброшены, а база данных останется неизменной.

Модуль Perl DBI предоставил API begin_work , который разрешает транзакции (отключая AutoCommit) до следующего вызова для фиксации или отката. После следующей фиксации или отката AutoCommit автоматически включится снова.

$rc  = $dbh->begin_work  or die $dbh->errstr;

Опция AutoCommit

Если ваши транзакции просты, вы можете избавить себя от необходимости совершать много коммитов. Когда вы делаете вызов connect, вы можете указать опцию AutoCommit, которая будет выполнять операцию автоматической фиксации после каждого успешного запроса. Вот как это выглядит —

my $dbh = DBI->connect($dsn, $userid, $password,
              {AutoCommit => 1}) 
              or die $DBI::errstr;

Здесь AutoCommit может принимать значение 1 или 0, где 1 означает, что AutoCommit включен, а 0 означает, что AutoCommit выключен.

Автоматическая обработка ошибок

Когда вы делаете вызов Connect, вы можете указать опцию RaiseErrors, которая автоматически обрабатывает ошибки. При возникновении ошибки DBI прерывает вашу программу, а не возвращает код ошибки. Если все, что вам нужно, это прервать программу из-за ошибки, это может быть удобно. Вот как это выглядит —

my $dbh = DBI->connect($dsn, $userid, $password,
              {RaiseError => 1})
              or die $DBI::errstr;

Здесь RaiseError может принимать значение 1 или 0.

Отключение базы данных

Чтобы отключить соединение с базой данных, используйте API отключения следующим образом:

$rc = $dbh->disconnect  or warn $dbh->errstr;

Поведение транзакции метода отсоединения, к сожалению, не определено. Некоторые системы баз данных (такие как Oracle и Ingres) будут автоматически фиксировать любые ожидающие изменения, но другие (такие как Informix) будут откатывать любые ожидающие изменения. Приложения, не использующие AutoCommit, должны явно вызывать commit или rollback перед вызовом connect.

Использование значений NULL

Неопределенные значения или undef используются для обозначения значений NULL. Вы можете вставлять и обновлять столбцы со значением NULL, как если бы они отличались от NULL. Эти примеры вставляют и обновляют возраст столбца со значением NULL —

$sth = $dbh->prepare(qq {
         INSERT INTO TEST_TABLE (FIRST_NAME, AGE) VALUES (?, ?)
       });
$sth->execute("Joe", undef);

Здесь qq {} используется для возврата строки в кавычках для подготовки API. Однако следует соблюдать осторожность при попытке использовать значения NULL в предложении WHERE. Рассмотрим —

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

Привязка undef (NULL) к заполнителю не будет выбирать строки с нулевым возрастом! По крайней мере, для баз данных, которые соответствуют стандарту SQL. По причинам этого обратитесь к руководству по SQL для вашего движка базы данных или к любой книге по SQL. Чтобы явно выбрать NULL, вы должны сказать «ГДЕ возраст NULL».

Распространенной проблемой является наличие фрагмента кода для обработки значения, которое может быть определено или не определено (не NULL или NULL) во время выполнения. Простой метод состоит в том, чтобы подготовить соответствующее утверждение по мере необходимости и заменить заполнитель для случаев, отличных от NULL.

$sql_clause = defined $age? "age = ?" : "age IS NULL";
$sth = $dbh->prepare(qq {
         SELECT FIRST_NAME FROM TEST_TABLE WHERE $sql_clause
       });
$sth->execute(defined $age ? $age : ());

Некоторые другие функции DBI

available_drivers

@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);

Возвращает список всех доступных драйверов путем поиска модулей DBD :: * по каталогам в @INC. По умолчанию выдается предупреждение, если некоторые драйверы скрыты другими с таким же именем в более ранних каталогах. Передача истинного значения $ quiet запретит предупреждение.

installed_drivers

%drivers = DBI->installed_drivers();

Возвращает список пар имени драйвера и дескриптора драйвера для всех драйверов, «установленных» (загруженных) в текущий процесс. Имя драйвера не включает префикс «DBD ::».

источники данных

@ary = DBI->data_sources($driver);

Возвращает список источников данных (баз данных), доступных через указанный драйвер. Если $ driver пуст или undef, то используется значение переменной среды DBI_DRIVER.

котировка

$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);

Заключите в кавычки строковый литерал для использования в качестве литерального значения в инструкции SQL, экранируя любые специальные символы (такие как кавычки), содержащиеся в строке, и добавляя требуемый тип внешних кавычек.

$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
                $dbh->quote("Don't");

Для большинства типов баз данных цитата будет возвращать «Не делай» (включая внешние кавычки). Для метода quote () допустимо возвращать выражение SQL, которое вычисляется в нужную строку. Например —

$quoted = $dbh->quote("one\ntwo\0three")

may produce results which will be equivalent to

CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')

Методы, общие для всех дескрипторов

заблуждаться

$rv = $h->err;
or
$rv = $DBI::err
or
$rv = $h->err

Возвращает собственный код ошибки ядра базы данных из последнего вызванного метода драйвера. Код обычно является целым числом, но вы не должны предполагать это. Это эквивалентно $ DBI :: err или $ h-> err.

ErrStr

$str = $h->errstr;
or
$str = $DBI::errstr
or
$str = $h->errstr

Возвращает собственное сообщение об ошибке ядра базы данных из последнего вызванного метода DBI. Это имеет те же проблемы с продолжительностью жизни, что и описанный выше метод «err». Это эквивалентно $ DBI :: errstr или $ h-> errstr.

строки

$rv = $h->rows;
or
$rv = $DBI::rows

Это возвращает количество строк, на которые воздействовал предыдущий оператор SQL, и эквивалентно $ DBI :: row.

след

$h->trace($trace_settings);

DBI обладает чрезвычайно полезной способностью генерировать информацию трассировки во время выполнения того, что он делает, что может значительно сэкономить время при попытке отследить странные проблемы в ваших программах DBI. Вы можете использовать разные значения, чтобы установить уровень трассировки. Эти значения варьируются от 0 до 4. Значение 0 означает отключить трассировку, а 4 означает генерацию полной трассировки.

Интерполированные заявления запрещены

Настоятельно рекомендуется не использовать интерполированные операторы следующим образом:

while ($first_name = <>) {
   my $sth = $dbh->prepare("SELECT * 
                          FROM TEST_TABLE 
                          WHERE FIRST_NAME = '$first_name'");
   $sth->execute();
   # and so on ...
}

Поэтому не используйте интерполированный оператор, вместо этого используйте значение связывания для подготовки динамического оператора SQL.

Perl — доступ к базе данных

Архитектура приложения DBI

Обозначения и условные обозначения

Подключение к базе данных

ВСТАВИТЬ Операция

Использование значений привязки

ЧИТАЙТЕ Операцию

Использование значений привязки

ОБНОВЛЕНИЕ Операция

Использование значений привязки

УДАЛЕНИЕ Операция

Использование do Statement

Операция COMMIT

ROLLBACK Операция

Начать транзакцию

Опция AutoCommit

Автоматическая обработка ошибок

Отключение базы данных

Использование значений NULL

Некоторые другие функции DBI

available_drivers

installed_drivers

источники данных

котировка

Методы, общие для всех дескрипторов

заблуждаться

ErrStr

строки

след

Интерполированные заявления запрещены

Популярные уроки и статьи

Инициализаторы экземпляра в Java объяснены

AJAX - Краткое руководство

TempDB для производительности