Perl - доступ к базе данных
В этой главе вы узнаете, как получить доступ к базе данных внутри вашего Perl-скрипта. Начиная с Perl 5, стало очень легко писать приложения для баз данных, используяDBIмодуль. DBI означаетDatabase Independent Interface для 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.
Ниже приведен пример подключения к базе данных 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. Это будет сделано с помощьюprepare() API.
Выполнение SQL-запроса для выбора всех результатов из базы данных. Это будет сделано с помощьюexecute() API.
Освобождение ручки Stattement. Это будет сделано с помощьюfinish() API.
Если все пойдет хорошо, тогда commit эта операция в противном случае вы можете rollbackзавершить транзакцию. Фиксация и откат описаны в следующих разделах.
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 используют вопросительный знак вместо фактического значения, а затем фактические значения передаются через execute () API во время выполнения. Ниже приведен пример -
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 на основе необходимых условий. Это будет сделано с помощьюprepare() API.
Выполнение SQL-запроса для выбора всех результатов из базы данных. Это будет сделано с помощьюexecute() API.
Получение всех результатов один за другим и их печать. Это будет выполняться с помощью fetchrow_array() API.
Освобождение ручки Stattement. Это будет сделано с помощьюfinish() API.
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();ОБНОВЛЕНИЕ Операция
UPDATE Операция с любой базой данных означает обновление одной или нескольких записей, уже доступных в таблицах базы данных. Ниже приведена процедура обновления всех записей, в которых SEX обозначается буквой «M». Здесь мы увеличим ВОЗРАСТ всех мужчин на один год. Это займет три шага -
Подготовка SQL-запроса на основе необходимых условий. Это будет сделано с помощьюprepare() API.
Выполнение SQL-запроса для выбора всех результатов из базы данных. Это будет сделано с помощьюexecute() API.
Освобождение ручки Stattement. Это будет сделано с помощьюfinish() API.
Если все пойдет хорошо, тогда commit эта операция в противном случае вы можете rollbackзавершить транзакцию. См. Следующий раздел для 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;Использование значений привязки
Возможен случай, когда условие заранее не указано. Таким образом, вы можете использовать переменные связывания, которые будут принимать необходимые значения во время выполнения. Модули Perl DBI используют вопросительный знак вместо фактического значения, а затем фактические значения передаются через execute () API во время выполнения. Ниже приведен пример -
$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-запроса на основе необходимых условий. Это будет сделано с помощьюprepare() API.
Выполнение SQL-запроса для удаления необходимых записей из базы данных. Это будет сделано с помощьюexecute() API.
Освобождение ручки Stattement. Это будет сделано с помощьюfinish() API.
Если все пойдет хорошо, тогда commit эта операция в противном случае вы можете rollback завершить транзакцию.
$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
Если вы выполняете UPDATE, INSERT или DELETE, данные, которые возвращаются из базы данных, не поступают, поэтому есть короткий путь для выполнения этой операции. Вы можете использоватьdo оператор для выполнения любой из следующих команд.
$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');doвозвращает истинное значение, если это удалось, и ложное значение, если это не удалось. Фактически, в случае успеха он возвращает количество затронутых строк. В этом примере он вернет количество фактически удаленных строк.
COMMIT Operation
Фиксация - это операция, которая дает зеленый сигнал базе данных о завершении изменений, и после этой операции никакие изменения не могут быть возвращены в исходное положение.
Вот простой пример для вызова commit API.
$dbh->commit or die $dbh->errstr;ROLLBACK Операция
Если вас не устраивают все изменения или вы столкнулись с ошибкой между выполнением какой-либо операции, вы можете отменить эти изменения, чтобы использовать rollback API.
Вот простой пример для вызова rollback API.
$dbh->rollback or die $dbh->errstr;Начать транзакцию
Многие базы данных поддерживают транзакции. Это означает, что вы можете сделать целый ряд запросов, которые изменят базы данных, но на самом деле ни одно из изменений не будет сделано. Затем, в конце, вы выполняете специальный SQL-запросCOMMIT, и все изменения вносятся одновременно. В качестве альтернативы вы можете выполнить запрос ROLLBACK, и в этом случае все изменения будут отброшены, а база данных останется без изменений.
Предоставляется модуль Perl DBI begin_workAPI, который разрешает транзакции (путем отключения AutoCommit) до следующего вызова для фиксации или отката. После следующей фиксации или отката AutoCommit снова автоматически включится.
$rc = $dbh->begin_work or die $dbh->errstr;Параметр AutoCommit
Если ваши транзакции просты, вы можете избавить себя от необходимости совершать много коммитов. При выполнении вызова соединения вы можете указатьAutoCommitопция, которая будет выполнять автоматическую операцию фиксации после каждого успешного запроса. Вот как это выглядит -
my $dbh = DBI->connect($dsn, $userid, $password,
{AutoCommit => 1})
or die $DBI::errstr;Здесь AutoCommit может принимать значение 1 или 0, где 1 означает, что AutoCommit включен, а 0 означает, что AutoCommit выключен.
Автоматическая обработка ошибок
Когда вы выполняете вызов подключения, вы можете указать параметр RaiseErrors, который автоматически обрабатывает ошибки. При возникновении ошибки DBI прервет вашу программу вместо того, чтобы вернуть код ошибки. Если все, что вам нужно, - это прервать программу в случае ошибки, это может быть удобно. Вот как это выглядит -
my $dbh = DBI->connect($dsn, $userid, $password,
{RaiseError => 1})
or die $DBI::errstr;Здесь RaiseError может принимать значение 1 или 0.
Отключение базы данных
Чтобы отключить соединение с базой данных, используйте disconnect API следующим образом -
$rc = $dbh->disconnect or warn $dbh->errstr;К сожалению, поведение транзакции метода отключения не определено. Некоторые системы баз данных (такие как Oracle и Ingres) автоматически фиксируют все незавершенные изменения, но другие (такие как Informix) откатывают любые невыполненные изменения. Приложения, не использующие AutoCommit, должны явно вызывать фиксацию или откат перед вызовом отключения.
Использование значений NULL
Неопределенные значения или undef используются для обозначения значений NULL. Вы можете вставлять и обновлять столбцы со значением NULL, как если бы значение не было NULL. Эти примеры вставляют и обновляют возраст столбца с помощью значения NULL -
$sth = $dbh->prepare(qq {
INSERT INTO TEST_TABLE (FIRST_NAME, AGE) VALUES (?, ?)
});
$sth->execute("Joe", undef);Вот qq{} используется для возврата строки в кавычках в prepareAPI. Однако следует соблюдать осторожность при попытке использовать значения NULL в предложении WHERE. Учтите -
SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?При привязке undef (NULL) к заполнителю не будут выбираться строки с возрастом NULL! По крайней мере, для движков баз данных, соответствующих стандарту SQL. Обратитесь к руководству по SQL для вашей СУБД или к любой книге по SQL для выяснения причин этого. Чтобы явно выбрать NULL, вы должны сказать «WHERE age IS NULL».
Распространенная проблема заключается в том, что фрагмент кода обрабатывает значение, которое может быть определено или undef (не 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 заблокирует предупреждение.
установленные_драйверы
%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 :: rows.
след
$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 ...
}Таким образом, не используйте интерполированный оператор, вместо этого используйте bind value подготовить динамический оператор SQL.