Perl - dostęp do bazy danych

W tym rozdziale dowiesz się, jak uzyskać dostęp do bazy danych w skrypcie Perl. Począwszy od Perla 5 bardzo łatwo jest pisać aplikacje bazodanowe przy użyciuDBImoduł. DBI oznaczaDatabase Independent Interface dla Perla, co oznacza, że ​​DBI zapewnia warstwę abstrakcji między kodem Perla a bazową bazą danych, umożliwiając naprawdę łatwe przełączanie implementacji bazy danych.

DBI to moduł dostępu do bazy danych dla języka programowania Perl. Udostępnia zestaw metod, zmiennych i konwencji, które zapewniają spójny interfejs bazy danych, niezależny od aktualnie używanej bazy danych.

Architektura aplikacji DBI

DBI jest niezależne od jakiejkolwiek bazy danych dostępnej w zapleczu. Możesz używać DBI niezależnie od tego, czy pracujesz z Oracle, MySQL czy Informix, itp. Wynika to jasno z poniższego diagramu architektury.

W tym przypadku DBI jest odpowiedzialny za przejęcie wszystkich poleceń SQL przez API (tj. Interfejs programowania aplikacji) i wysłanie ich do odpowiedniego sterownika w celu rzeczywistego wykonania. I wreszcie DBI jest odpowiedzialny za pobranie wyników od sterownika i zwrócenie ich wywołującemu scritpowi.

Notacja i konwencje

W tym rozdziale będą używane następujące oznaczenia i zaleca się, abyś również przestrzegał tej samej konwencji.

$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

Połączenie z bazą danych

Zakładając, że będziemy pracować z bazą danych MySQL. Przed połączeniem się z bazą danych upewnij się, że są spełnione następujące warunki. Możesz skorzystać z naszego samouczka MySQL, jeśli nie wiesz, jak utworzyć bazę danych i tabele w bazie danych MySQL.

• Utworzyłeś bazę danych o nazwie TESTDB.

• Utworzyłeś tabelę o nazwie TEST_TABLE w TESTDB.

• Ta tabela zawiera pola FIRST_NAME, LAST_NAME, AGE, SEX i INCOME.

• ID użytkownika „testuser” i hasło „test123” są ustawione na dostęp do bazy danych TESTDB.

• Perl Module DBI jest poprawnie zainstalowany na twoim komputerze.

• Przeszedłeś przez samouczek MySQL, aby zrozumieć podstawy MySQL.

Poniżej przykład połączenia z bazą danych 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;

Jeśli połączenie zostanie nawiązane ze źródłem danych, zwracany jest uchwyt bazy danych i zapisywany w $ dbh do dalszego użytku, w przeciwnym razie $ dbh jest ustawiana na wartość undef, a $ DBI :: errstr zwraca łańcuch błędu.

Operacja INSERT

Operacja INSERT jest wymagana, gdy chcesz utworzyć niektóre rekordy w tabeli. Tutaj używamy tabeli TEST_TABLE do tworzenia naszych rekordów. Po nawiązaniu połączenia z bazą danych jesteśmy gotowi do tworzenia rekordów w TEST_TABLE. Poniżej znajduje się procedura tworzenia pojedynczego rekordu w TEST_TABLE. Możesz utworzyć dowolną liczbę rekordów, używając tej samej koncepcji.

Tworzenie rekordu obejmuje następujące kroki -

• Przygotowanie instrukcji SQL z instrukcją INSERT. Zostanie to zrobione za pomocąprepare() API.

• Wykonywanie zapytania SQL w celu wybrania wszystkich wyników z bazy danych. Zostanie to zrobione za pomocąexecute() API.

• Zwalniam uchwyt Stattement. Zostanie to zrobione za pomocąfinish() API.

• Jeśli wszystko pójdzie dobrze commit ta operacja inaczej możesz rollbackzakończyć transakcję. Commit i Rollback są wyjaśnione w następnych sekcjach.

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;

Korzystanie z wartości powiązań

Może się zdarzyć, że wartości do wprowadzenia nie zostaną podane z góry. Możesz więc używać zmiennych wiążących, które przyjmą wymagane wartości w czasie wykonywania. Moduły Perl DBI używają znaku zapytania zamiast rzeczywistej wartości, a następnie rzeczywiste wartości są przekazywane przez funkcję API execute () w czasie wykonywania. Oto przykład -

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;

Operacja READ

READ Operacja na dowolnej bazie danych oznacza pobranie przydatnych informacji z bazy danych, tj. Jednego lub więcej rekordów z jednej lub więcej tabel. Więc po nawiązaniu połączenia z bazą danych jesteśmy gotowi do wysłania zapytania do tej bazy danych. Poniżej przedstawiono procedurę odpytywania wszystkich rekordów o WIEKU większym niż 20. To zajmie cztery kroki -

• Przygotowanie zapytania SQL SELECT na podstawie wymaganych warunków. Zostanie to zrobione za pomocąprepare() API.

• Wykonywanie zapytania SQL w celu wybrania wszystkich wyników z bazy danych. Zostanie to zrobione za pomocąexecute() API.

• Pobieranie wszystkich wyników jeden po drugim i drukowanie ich za pomocą fetchrow_array() API.

• Zwalniam uchwyt Stattement. Zostanie to zrobione za pomocą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();

Korzystanie z wartości powiązań

Może się zdarzyć, że warunek nie zostanie podany z góry. Możesz więc używać zmiennych wiążących, które przyjmą wymagane wartości w czasie wykonywania. Moduły Perl DBI używają znaku zapytania zamiast rzeczywistej wartości, a następnie rzeczywiste wartości są przekazywane przez funkcję API execute () w czasie wykonywania. Oto przykład -

$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();

Operacja UPDATE

UPDATE Operacja na dowolnej bazie danych oznacza aktualizację jednego lub więcej rekordów już dostępnych w tabelach bazy danych. Poniżej przedstawiono procedurę aktualizacji wszystkich rekordów, które mają PŁEĆ jako „M”. Tutaj zwiększymy WIEK wszystkich samców o jeden rok. To zajmie trzy kroki -

• Przygotowanie zapytania SQL na podstawie wymaganych warunków. Zostanie to zrobione za pomocąprepare() API.

• Wykonywanie zapytania SQL w celu wybrania wszystkich wyników z bazy danych. Zostanie to zrobione za pomocąexecute() API.

• Zwalniam uchwyt Stattement. Zostanie to zrobione za pomocąfinish() API.

• Jeśli wszystko pójdzie dobrze commit ta operacja inaczej możesz rollbackzakończyć transakcję. Zobacz następną sekcję dotyczącą interfejsów API zatwierdzania i wycofywania.

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;

Korzystanie z wartości powiązań

Może się zdarzyć, że warunek nie zostanie podany z góry. Możesz więc używać zmiennych wiążących, które przyjmą wymagane wartości w czasie wykonywania. Moduły Perl DBI używają znaku zapytania zamiast rzeczywistej wartości, a następnie rzeczywiste wartości są przekazywane przez funkcję API execute () w czasie wykonywania. Oto przykład -

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

W niektórych przypadkach chciałbyś ustawić wartość, która nie jest podana z góry, więc możesz użyć wartości wiążącej w następujący sposób. W tym przykładzie dochód wszystkich samców zostanie ustalony na 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();

Operacja USUŃ

Operacja DELETE jest wymagana, gdy chcesz usunąć niektóre rekordy z bazy danych. Poniżej przedstawiono procedurę usuwania wszystkich rekordów z TEST_TABLE, gdzie WIEK jest równy 30. Ta operacja obejmuje następujące kroki.

• Przygotowanie zapytania SQL na podstawie wymaganych warunków. Zostanie to zrobione za pomocąprepare() API.

• Wykonywanie zapytania SQL w celu usunięcia wymaganych rekordów z bazy danych. Zostanie to zrobione za pomocąexecute() API.

• Zwalniam uchwyt Stattement. Zostanie to zrobione za pomocąfinish() API.

• Jeśli wszystko pójdzie dobrze commit ta operacja inaczej możesz rollback zakończyć transakcję.

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

Korzystanie z instrukcji do

Jeśli wykonujesz AKTUALIZACJĘ, WSTAW lub USUŃ, nie ma danych, które wrócą z bazy danych, więc istnieje skrót do wykonania tej operacji. Możesz użyćdo instrukcja, aby wykonać dowolne z następujących poleceń.

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

dozwraca wartość true, jeśli się powiedzie, a wartość false, jeśli się nie powiedzie. W rzeczywistości, jeśli się powiedzie, zwraca liczbę wierszy, których to dotyczy. W tym przykładzie zwróci liczbę wierszy, które zostały faktycznie usunięte.

Operacja COMMIT

Zatwierdzenie to operacja, która daje zielony sygnał do bazy danych w celu sfinalizowania zmian i po tej operacji żadna zmiana nie może zostać przywrócona do pierwotnej pozycji.

Oto prosty przykład do zadzwonienia commit API.

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

Operacja ROLLBACK

Jeśli nie jesteś zadowolony ze wszystkich zmian lub napotkasz błąd między jakąkolwiek operacją, możesz przywrócić te zmiany do użycia rollback API.

Oto prosty przykład do zadzwonienia rollback API.

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

Rozpocznij transakcję

Wiele baz danych obsługuje transakcje. Oznacza to, że możesz wykonać całą masę zapytań, które zmodyfikowałyby bazy danych, ale żadna ze zmian nie jest faktycznie wprowadzana. Na koniec wysyłasz specjalne zapytanie SQLCOMMIT, a wszystkie zmiany są wprowadzane jednocześnie. Alternatywnie możesz wysłać zapytanie ROLLBACK, w którym to przypadku wszystkie zmiany zostaną odrzucone, a baza danych pozostanie niezmieniona.

Dostarczony moduł Perl DBI begin_workAPI, który umożliwia transakcje (poprzez wyłączenie AutoCommit) do następnego wywołania zatwierdzenia lub wycofania. Po następnym zatwierdzeniu lub wycofaniu AutoCommit zostanie automatycznie ponownie włączony.

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

Opcja AutoCommit

Jeśli Twoje transakcje są proste, możesz zaoszczędzić sobie kłopotów z koniecznością wystawiania wielu zatwierdzeń. Podczas nawiązywania połączenia możesz określićAutoCommitopcja, która wykona operację automatycznego zatwierdzenia po każdym pomyślnym zapytaniu. Oto, jak to wygląda -

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

Tutaj AutoCommit może przyjąć wartość 1 lub 0, gdzie 1 oznacza AutoCommit jest włączone, a 0 oznacza AutoCommit jest wyłączone.

Automatyczna obsługa błędów

Podczas nawiązywania połączenia możesz określić opcję RaiseErrors, która automatycznie obsługuje błędy. Gdy wystąpi błąd, DBI przerwie twój program zamiast zwrócić kod błędu. Jeśli chcesz tylko przerwać program z powodu błędu, może to być wygodne. Oto, jak to wygląda -

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

Tutaj RaiseError może przyjąć wartość 1 lub 0.

Odłączanie bazy danych

Aby rozłączyć połączenie z bazą danych, użyj disconnect API w następujący sposób -

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

Zachowanie transakcyjne metody rozłączenia jest niestety nieokreślone. Niektóre systemy baz danych (takie jak Oracle i Ingres) automatycznie zatwierdzą wszelkie zaległe zmiany, ale inne (takie jak Informix) wycofają wszelkie zaległe zmiany. Aplikacje, które nie używają AutoCommit, powinny jawnie wywoływać zatwierdzanie lub wycofywanie przed wywołaniem rozłączenia.

Korzystanie z wartości NULL

Niezdefiniowane wartości lub undef są używane do wskazania wartości NULL. Możesz wstawiać i aktualizować kolumny o wartości NULL, tak jak w przypadku wartości innej niż NULL. Te przykłady wstawiają i aktualizują wiek kolumny o wartość NULL -

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

Tutaj qq{} służy do zwracania ciągu znaków w cudzysłowie prepareAPI. Należy jednak zachować ostrożność podczas próby użycia wartości NULL w klauzuli WHERE. Rozważ -

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

Powiązanie undef (NULL) z symbolem zastępczym nie spowoduje wybrania wierszy, które mają NULL wiek! Przynajmniej dla silników baz danych zgodnych ze standardem SQL. Zapoznaj się z instrukcją SQL silnika bazy danych lub dowolną książką SQL, aby dowiedzieć się, dlaczego tak jest. Aby jawnie wybrać wartości NULL, musisz powiedzieć „WHERE age IS NULL”.

Częstym problemem jest to, że fragment kodu obsługuje wartość, która może być zdefiniowana lub undef (inna niż NULL lub NULL) w czasie wykonywania. Prostą techniką jest przygotowanie odpowiedniej instrukcji w razie potrzeby i zastąpienie symbolu zastępczego dla przypadków innych niż 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 : ());

Niektóre inne funkcje DBI

available_drivers

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

Zwraca listę wszystkich dostępnych sterowników, wyszukując moduły DBD :: * w katalogach w @INC. Domyślnie wyświetlane jest ostrzeżenie, jeśli niektóre sterowniki są ukryte przez inne o tej samej nazwie we wcześniejszych katalogach. Przekazanie wartości true dla $ quiet wstrzyma ostrzeżenie.

zainstalowane_ sterowniki

%drivers = DBI->installed_drivers();

Zwraca listę nazw sterowników i par uchwytów sterowników dla wszystkich „zainstalowanych” (załadowanych) sterowników w bieżącym procesie. Nazwa sterownika nie zawiera przedrostka „DBD ::”.

źródła danych

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

Zwraca listę źródeł danych (baz danych) dostępnych za pośrednictwem nazwanego sterownika. Jeśli $ driver jest pusty lub undef, to używana jest wartość zmiennej środowiskowej DBI_DRIVER.

zacytować

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

Cytuj literał ciągu, aby użyć go jako wartości literału w instrukcji SQL, unikając znaków specjalnych (takich jak cudzysłowy) zawartych w ciągu i dodając wymagany typ zewnętrznych cudzysłowów.

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

W przypadku większości typów baz danych cudzysłów zwróciłby „Don''t” (łącznie z cudzysłowami). Metoda quote () może zwracać wyrażenie SQL, którego wynikiem jest żądany ciąg. Na przykład -

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

may produce results which will be equivalent to

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

Metody wspólne dla wszystkich uchwytów

błądzić

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

Zwraca kod błędu natywnego silnika bazy danych z ostatniej wywołanej metody sterownika. Kod jest zazwyczaj liczbą całkowitą, ale nie należy tego zakładać. Jest to odpowiednik $ DBI :: err lub $ h-> err.

errstr

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

Zwraca komunikat o błędzie rodzimego silnika bazy danych z ostatniej wywołanej metody DBI. Ma to te same problemy z trwałością, co metoda „err” opisana powyżej. Jest to odpowiednik $ DBI :: errstr lub $ h-> errstr.

wydziwianie

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

Zwraca liczbę wierszy wykonanych przez poprzednią instrukcję SQL i równoważną $ DBI :: rows.

ślad

$h->trace($trace_settings);

DBI ma niezwykle przydatną zdolność do generowania informacji o tym, co robi w czasie wykonywania, co może być ogromną oszczędnością czasu, gdy próbuje się znaleźć dziwne problemy w programach DBI. Możesz użyć różnych wartości, aby ustawić poziom śledzenia. Wartości te wahają się od 0 do 4. Wartość 0 oznacza wyłączenie śledzenia, a 4 oznacza generowanie pełnego śledzenia.

Zabronione są stwierdzenia interpolowane

Zdecydowanie zaleca się, aby nie używać następujących instrukcji interpolowanych -

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

Dlatego nie używaj interpolowanej instrukcji, zamiast tego użyj bind value przygotowanie dynamicznych instrukcji SQL.