Perl - Datenbankzugriff

In diesem Kapitel erfahren Sie, wie Sie in Ihrem Perl-Skript auf eine Datenbank zugreifen. Ab Perl 5 ist es sehr einfach geworden, Datenbankanwendungen mit zu schreibenDBIModul. DBI steht fürDatabase Independent Interface Für Perl bedeutet dies, dass DBI eine Abstraktionsschicht zwischen dem Perl-Code und der zugrunde liegenden Datenbank bereitstellt, sodass Sie die Datenbankimplementierungen ganz einfach wechseln können.

Das DBI ist ein Datenbankzugriffsmodul für die Programmiersprache Perl. Es bietet eine Reihe von Methoden, Variablen und Konventionen, die unabhängig von der tatsächlich verwendeten Datenbank eine konsistente Datenbankschnittstelle bieten.

Architektur einer DBI-Anwendung

DBI ist unabhängig von jeder im Backend verfügbaren Datenbank. Sie können DBI verwenden, unabhängig davon, ob Sie mit Oracle, MySQL oder Informix usw. arbeiten. Dies geht aus dem folgenden Architekturdiagramm hervor.

Hier ist DBI dafür verantwortlich, alle SQL-Befehle über die API (dh die Anwendungsprogrammierschnittstelle) zu führen und sie zur tatsächlichen Ausführung an den entsprechenden Treiber zu senden. Und schließlich ist DBI dafür verantwortlich, die Ergebnisse vom Fahrer zu übernehmen und an den anrufenden Scritp zurückzugeben.

Notation und Konventionen

In diesem Kapitel werden folgende Notationen verwendet, und es wird empfohlen, dass Sie dieselbe Konvention befolgen.

$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

Datenbankverbindung

Angenommen, wir werden mit der MySQL-Datenbank arbeiten. Stellen Sie vor dem Herstellen einer Verbindung zu einer Datenbank Folgendes sicher. Sie können unser MySQL-Tutorial nutzen, falls Sie nicht wissen, wie Sie Datenbanken und Tabellen in der MySQL-Datenbank erstellen.

Sie haben eine Datenbank mit dem Namen TESTDB erstellt.
Sie haben in TESTDB eine Tabelle mit dem Namen TEST_TABLE erstellt.
Diese Tabelle enthält die Felder FIRST_NAME, LAST_NAME, AGE, SEX und INCOME.
Die Benutzer-ID "testuser" und das Kennwort "test123" sind für den Zugriff auf TESTDB festgelegt.
Perl Module DBI ist ordnungsgemäß auf Ihrem Computer installiert.
Sie haben das MySQL-Tutorial durchgearbeitet, um die MySQL-Grundlagen zu verstehen.

Es folgt das Beispiel für die Verbindung mit der MySQL-Datenbank "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;

Wenn eine Verbindung mit der Datenquelle hergestellt wird, wird ein Datenbankhandle zurückgegeben und zur weiteren Verwendung in $ dbh gespeichert. Andernfalls wird $ dbh auf den Wert undef gesetzt und $ DBI :: errstr gibt eine Fehlerzeichenfolge zurück.

INSERT-Betrieb

Die INSERT-Operation ist erforderlich, wenn Sie einige Datensätze in einer Tabelle erstellen möchten. Hier verwenden wir die Tabelle TEST_TABLE, um unsere Datensätze zu erstellen. Sobald unsere Datenbankverbindung hergestellt ist, können wir Datensätze in TEST_TABLE erstellen. Im Folgenden wird beschrieben, wie Sie einen einzelnen Datensatz in TEST_TABLE erstellen. Mit demselben Konzept können Sie beliebig viele Datensätze erstellen.

Die Datensatzerstellung umfasst die folgenden Schritte:

SQL-Anweisung mit INSERT-Anweisung vorbereiten. Dies erfolgt mitprepare() API.
Ausführen einer SQL-Abfrage, um alle Ergebnisse aus der Datenbank auszuwählen. Dies erfolgt mitexecute() API.
Stattement-Griff loslassen. Dies erfolgt mitfinish() API.
Wenn alles gut geht, dann commit diese Operation können Sie sonst rollbackTransaktion abschließen. Commit und Rollback werden in den nächsten Abschnitten erläutert.

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;

Bindungswerte verwenden

Es kann vorkommen, dass die einzugebenden Werte nicht im Voraus angegeben werden. Sie können also Bindevariablen verwenden, die zur Laufzeit die erforderlichen Werte annehmen. Perl-DBI-Module verwenden anstelle des tatsächlichen Werts ein Fragezeichen. Anschließend werden die tatsächlichen Werte zur Laufzeit über die execute () - API übergeben. Es folgt das Beispiel -

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;

LESEN Betrieb

READ Operation für eine beliebige Datenbank bedeutet, dass einige nützliche Informationen aus der Datenbank abgerufen werden, dh ein oder mehrere Datensätze aus einer oder mehreren Tabellen. Sobald unsere Datenbankverbindung hergestellt ist, können wir eine Abfrage in diese Datenbank durchführen. Im Folgenden wird beschrieben, wie alle Datensätze mit einem Alter von mehr als 20 Jahren abgefragt werden. Dies umfasst vier Schritte:

Vorbereiten der SQL SELECT-Abfrage basierend auf den erforderlichen Bedingungen. Dies erfolgt mitprepare() API.
Ausführen einer SQL-Abfrage, um alle Ergebnisse aus der Datenbank auszuwählen. Dies erfolgt mitexecute() API.
Abrufen aller Ergebnisse nacheinander und Drucken dieser Ergebnisse. Dies erfolgt mit fetchrow_array() API.
Stattement-Griff loslassen. Dies erfolgt mitfinish() 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();

Bindungswerte verwenden

Es kann vorkommen, dass die Bedingung nicht im Voraus gegeben wird. Sie können also Bindevariablen verwenden, die zur Laufzeit die erforderlichen Werte annehmen. Perl-DBI-Module verwenden anstelle des tatsächlichen Werts ein Fragezeichen. Anschließend werden die tatsächlichen Werte zur Laufzeit über die API execute () übergeben. Es folgt das Beispiel -

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

UPDATE Operation für eine beliebige Datenbank bedeutet, einen oder mehrere Datensätze zu aktualisieren, die bereits in den Datenbanktabellen verfügbar sind. Im Folgenden wird beschrieben, wie alle Datensätze mit SEX als 'M' aktualisiert werden. Hier werden wir das ALTER aller Männchen um ein Jahr erhöhen. Dies dauert drei Schritte -

Vorbereiten der SQL-Abfrage basierend auf den erforderlichen Bedingungen. Dies erfolgt mitprepare() API.
Ausführen einer SQL-Abfrage, um alle Ergebnisse aus der Datenbank auszuwählen. Dies erfolgt mitexecute() API.
Stattement-Griff loslassen. Dies erfolgt mitfinish() API.
Wenn alles gut geht, dann commit diese Operation können Sie sonst rollbackTransaktion abschließen. Im nächsten Abschnitt finden Sie Commit- und Rollback-APIs.

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;

Bindungswerte verwenden

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

In einigen Fällen möchten Sie einen Wert festlegen, der nicht im Voraus angegeben wird, damit Sie den verbindlichen Wert wie folgt verwenden können. In diesem Beispiel wird das Einkommen aller Männer auf 10000 festgelegt.

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

Der Vorgang LÖSCHEN ist erforderlich, wenn Sie einige Datensätze aus Ihrer Datenbank löschen möchten. Im Folgenden wird beschrieben, wie alle Datensätze aus TEST_TABLE gelöscht werden, wobei AGE gleich 30 ist. Dieser Vorgang führt die folgenden Schritte aus.

Vorbereiten der SQL-Abfrage basierend auf den erforderlichen Bedingungen. Dies erfolgt mitprepare() API.
Ausführen einer SQL-Abfrage zum Löschen der erforderlichen Datensätze aus der Datenbank. Dies erfolgt mitexecute() API.
Stattement-Griff loslassen. Dies erfolgt mitfinish() API.
Wenn alles gut geht, dann commit diese Operation können Sie sonst rollback Transaktion abschließen.

$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-Anweisung verwenden

Wenn Sie UPDATE, INSERT oder DELETE ausführen, werden keine Daten aus der Datenbank zurückgegeben. Daher gibt es eine Verknüpfung, um diesen Vorgang auszuführen. Sie können verwendendo Anweisung, um einen der Befehle wie folgt auszuführen.

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

doGibt einen wahren Wert zurück, wenn dies erfolgreich war, und einen falschen Wert, wenn dies fehlgeschlagen ist. Wenn dies erfolgreich ist, wird die Anzahl der betroffenen Zeilen zurückgegeben. Im Beispiel wird die Anzahl der tatsächlich gelöschten Zeilen zurückgegeben.

COMMIT-Betrieb

Commit ist die Operation, die der Datenbank ein grünes Signal gibt, um die Änderungen abzuschließen. Nach dieser Operation kann keine Änderung mehr in ihre ursprüngliche Position zurückgesetzt werden.

Hier ist ein einfaches Beispiel zum Aufrufen commit API.

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

ROLLBACK-Betrieb

Wenn Sie mit allen Änderungen nicht zufrieden sind oder zwischen den Vorgängen ein Fehler auftritt, können Sie diese Änderungen auf die Verwendung zurücksetzen rollback API.

Hier ist ein einfaches Beispiel zum Aufrufen rollback API.

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

Transaktion starten

Viele Datenbanken unterstützen Transaktionen. Dies bedeutet, dass Sie eine ganze Reihe von Abfragen durchführen können, die die Datenbanken ändern würden, aber keine der Änderungen tatsächlich vorgenommen wird. Am Ende geben Sie dann die spezielle SQL-Abfrage ausCOMMITund alle Änderungen werden gleichzeitig vorgenommen. Alternativ können Sie die Abfrage ROLLBACK ausgeben. In diesem Fall werden alle Änderungen verworfen und die Datenbank bleibt unverändert.

Perl DBI-Modul zur Verfügung gestellt begin_workAPI, die Transaktionen (durch Deaktivieren von AutoCommit) bis zum nächsten Aufruf zum Festschreiben oder Zurücksetzen ermöglicht. Nach dem nächsten Commit oder Rollback wird AutoCommit automatisch wieder aktiviert.

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

AutoCommit-Option

Wenn Ihre Transaktionen einfach sind, können Sie sich die Mühe ersparen, viele Commits ausführen zu müssen. Wenn Sie den Verbindungsaufruf tätigen, können Sie eine angebenAutoCommitOption, die nach jeder erfolgreichen Abfrage einen automatischen Festschreibungsvorgang ausführt. So sieht es aus -

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

Hier kann AutoCommit den Wert 1 oder 0 annehmen, wobei 1 bedeutet, dass AutoCommit aktiviert ist und 0 bedeutet, dass AutoCommit deaktiviert ist.

Automatische Fehlerbehandlung

Wenn Sie den Verbindungsaufruf tätigen, können Sie eine RaiseErrors-Option angeben, die Fehler automatisch für Sie behandelt. Wenn ein Fehler auftritt, bricht DBI Ihr Programm ab, anstatt einen Fehlercode zurückzugeben. Wenn Sie das Programm nur aufgrund eines Fehlers abbrechen möchten, kann dies praktisch sein. So sieht es aus -

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

Hier kann RaiseError den Wert 1 oder 0 annehmen.

Datenbank trennen

Verwenden Sie zum Trennen der Datenbankverbindung disconnect API wie folgt -

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

Das Transaktionsverhalten der Trennungsmethode ist leider undefiniert. Einige Datenbanksysteme (wie Oracle und Ingres) übernehmen automatisch alle ausstehenden Änderungen, andere (wie Informix) setzen alle ausstehenden Änderungen zurück. Anwendungen, die AutoCommit nicht verwenden, sollten Commit oder Rollback explizit aufrufen, bevor sie Disconnect aufrufen.

Verwenden von NULL-Werten

Undefinierte Werte oder undef werden verwendet, um NULL-Werte anzugeben. Sie können Spalten mit einem NULL-Wert wie einen Nicht-NULL-Wert einfügen und aktualisieren. In diesen Beispielen wird das Spaltenalter eingefügt und mit einem NULL-Wert aktualisiert.

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

Hier qq{} wird verwendet, um eine Zeichenfolge in Anführungszeichen an zurückzugeben prepareAPI. Bei der Verwendung von NULL-Werten in einer WHERE-Klausel ist jedoch Vorsicht geboten. Betrachten Sie -

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

Durch das Binden eines undef (NULL) an den Platzhalter werden keine Zeilen ausgewählt, die ein NULL-Alter haben! Zumindest für Datenbankmodule, die dem SQL-Standard entsprechen. Die Gründe hierfür finden Sie im SQL-Handbuch für Ihre Datenbank-Engine oder in einem SQL-Buch. Um NULL explizit auszuwählen, müssen Sie "WHERE age IS NULL" sagen.

Ein häufiges Problem besteht darin, dass ein Codefragment zur Laufzeit einen Wert verarbeitet, der entweder definiert oder undef (nicht NULL oder NULL) sein kann. Eine einfache Technik besteht darin, die entsprechende Anweisung nach Bedarf vorzubereiten und Nicht-NULL-Fälle durch den Platzhalter zu ersetzen.

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

Einige andere DBI-Funktionen

verfügbare_Treiber

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

Gibt eine Liste aller verfügbaren Treiber zurück, indem in den Verzeichnissen in @INC nach DBD :: * -Modulen gesucht wird. Standardmäßig wird eine Warnung ausgegeben, wenn einige Treiber in früheren Verzeichnissen von anderen mit demselben Namen ausgeblendet werden. Wenn Sie einen wahren Wert für $ quiet übergeben, wird die Warnung deaktiviert.

installierte_Treiber

%drivers = DBI->installed_drivers();

Gibt eine Liste von Treibernamen- und Treiberhandle-Paaren für alle im aktuellen Prozess 'installierten' (geladenen) Treiber zurück. Der Treibername enthält nicht das Präfix 'DBD ::'.

Datenquellen

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

Gibt eine Liste der Datenquellen (Datenbanken) zurück, die über den genannten Treiber verfügbar sind. Wenn $ driver leer oder undef ist, wird der Wert der Umgebungsvariablen DBI_DRIVER verwendet.

Zitat

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

Zitieren Sie ein Zeichenfolgenliteral zur Verwendung als Literalwert in einer SQL-Anweisung, indem Sie alle in der Zeichenfolge enthaltenen Sonderzeichen (z. B. Anführungszeichen) maskieren und den erforderlichen Typ der äußeren Anführungszeichen hinzufügen.

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

Bei den meisten Datenbanktypen würde das Anführungszeichen "Nicht" (einschließlich der äußeren Anführungszeichen) zurückgeben. Es ist gültig, dass die quote () -Methode einen SQL-Ausdruck zurückgibt, der die gewünschte Zeichenfolge ergibt. Zum Beispiel -

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

may produce results which will be equivalent to

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

Allen Handles gemeinsame Methoden

irren

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

Gibt den Fehlercode des nativen Datenbankmoduls von der zuletzt aufgerufenen Treibermethode zurück. Der Code ist normalerweise eine Ganzzahl, aber das sollten Sie nicht annehmen. Dies entspricht $ DBI :: err oder $ h-> err.

errstr

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

Gibt die native Datenbankmodul-Fehlermeldung der zuletzt aufgerufenen DBI-Methode zurück. Dies hat die gleichen Probleme mit der Lebensdauer wie die oben beschriebene "err" -Methode. Dies entspricht $ DBI :: errstr oder $ h-> errstr.

Reihen

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

Dies gibt die Anzahl der Zeilen zurück, die von der vorherigen SQL-Anweisung ausgeführt wurden und $ DBI :: Zeilen entsprechen.

Spur

$h->trace($trace_settings);

DBI bietet eine äußerst nützliche Möglichkeit, Informationen zur Laufzeitverfolgung zu generieren. Dies kann eine enorme Zeitersparnis bedeuten, wenn Sie versuchen, seltsame Probleme in Ihren DBI-Programmen aufzuspüren. Sie können verschiedene Werte verwenden, um die Ablaufverfolgungsstufe festzulegen. Diese Werte variieren von 0 bis 4. Der Wert 0 bedeutet Trace deaktivieren und 4 bedeutet vollständige Trace generieren.

Interpolierte Anweisungen sind verboten

Es wird dringend empfohlen, keine interpolierten Anweisungen wie folgt zu verwenden:

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

Verwenden Sie daher keine interpolierte Anweisung, sondern verwenden Sie bind value dynamische SQL-Anweisung vorzubereiten.