Perl - Accesso al database

Questo capitolo ti insegna come accedere a un database all'interno del tuo script Perl. A partire da Perl 5 è diventato molto facile scrivere applicazioni di database utilizzandoDBImodulo. DBI sta perDatabase Independent Interface per Perl, il che significa che DBI fornisce un livello di astrazione tra il codice Perl e il database sottostante, consentendo di cambiare le implementazioni del database molto facilmente.

Il DBI è un modulo di accesso al database per il linguaggio di programmazione Perl. Fornisce una serie di metodi, variabili e convenzioni che forniscono un'interfaccia di database coerente, indipendentemente dal database effettivamente utilizzato.

Architettura di un'applicazione DBI

DBI è indipendente da qualsiasi database disponibile nel backend. È possibile utilizzare DBI sia che si lavori con Oracle, MySQL o Informix, ecc. Questo è chiaro dal seguente diagramma dell'architettura.

Qui DBI è responsabile di prendere tutti i comandi SQL attraverso l'API, (cioè, Application Programming Interface) e di inviarli al driver appropriato per l'esecuzione effettiva. Infine, DBI è responsabile di prendere i risultati dal driver e restituirli allo scritp chiamante.

Notazione e convenzioni

In questo capitolo verranno utilizzate le seguenti notazioni e si raccomanda di seguire la stessa convenzione.

$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

Connessione al database

Supponendo che lavoreremo con il database MySQL. Prima di connettersi a un database assicurarsi di quanto segue. Puoi prendere l'aiuto del nostro tutorial MySQL nel caso in cui non sei a conoscenza di come creare database e tabelle nel database MySQL.

  • Hai creato un database con un nome TESTDB.

  • Hai creato una tabella con un nome TEST_TABLE in TESTDB.

  • Questa tabella contiene i campi FIRST_NAME, LAST_NAME, AGE, SEX e INCOME.

  • L'ID utente "testuser" e la password "test123" sono impostati per accedere a TESTDB.

  • Perl Module DBI è installato correttamente sulla macchina.

  • Hai seguito il tutorial su MySQL per comprendere le basi di MySQL.

Di seguito è riportato l'esempio di connessione con il database 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;

Se viene stabilita una connessione con l'origine dati, viene restituito un handle di database e salvato in $ dbh per un ulteriore utilizzo, altrimenti $ dbh viene impostato sul valore undef e $ DBI :: errstr restituisce una stringa di errore.

Operazione INSERT

L'operazione INSERT è necessaria quando si desidera creare alcuni record in una tabella. Qui stiamo usando la tabella TEST_TABLE per creare i nostri record. Quindi, una volta stabilita la connessione al database, siamo pronti per creare record in TEST_TABLE. Di seguito è riportata la procedura per creare un singolo record in TEST_TABLE. Puoi creare tutti i record che desideri utilizzando lo stesso concetto.

La creazione di record richiede i seguenti passaggi:

  • Preparazione dell'istruzione SQL con l'istruzione INSERT. Questo sarà fatto usandoprepare() API.

  • Esecuzione di query SQL per selezionare tutti i risultati dal database. Questo sarà fatto usandoexecute() API.

  • Rilascio della maniglia di stato. Questo sarà fatto usandofinish() API.

  • Se tutto va bene allora commit questa operazione altrimenti puoi rollbacktransazione completa. Commit e Rollback sono spiegati nelle sezioni successive.

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;

Utilizzo dei valori di associazione

Potrebbe esserci un caso in cui i valori da inserire non vengono forniti in anticipo. Quindi puoi usare le variabili di bind che prenderanno i valori richiesti in fase di esecuzione. I moduli Perl DBI fanno uso di un punto interrogativo al posto del valore effettivo e quindi i valori effettivi vengono passati tramite l'API execute () in fase di esecuzione. Di seguito è riportato l'esempio:

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;

Operazione READ

LEGGI Operare su una qualsiasi banca dati significa prelevare alcune informazioni utili dal database, ovvero uno o più record da una o più tabelle. Quindi, una volta stabilita la connessione al database, siamo pronti per eseguire una query in questo database. Di seguito è riportata la procedura per interrogare tutti i record con ETÀ maggiore di 20. Questo richiederà quattro passaggi:

  • Preparazione della query SQL SELECT in base alle condizioni richieste. Questo sarà fatto usandoprepare() API.

  • Esecuzione di query SQL per selezionare tutti i risultati dal database. Questo sarà fatto usandoexecute() API.

  • Recuperare tutti i risultati uno per uno e stamparli. Questo sarà fatto usando fetchrow_array() API.

  • Rilascio della maniglia di stato. Questo sarà fatto usandofinish() 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();

Utilizzo dei valori di associazione

Potrebbe esserci un caso in cui la condizione non viene fornita in anticipo. Quindi puoi usare le variabili di bind, che assumeranno i valori richiesti in fase di esecuzione. I moduli Perl DBI utilizzano un punto interrogativo al posto del valore effettivo e quindi i valori effettivi vengono passati tramite l'API execute () in fase di esecuzione. Di seguito è riportato l'esempio:

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

Operazione UPDATE

AGGIORNAMENTO Operare su qualsiasi database significa aggiornare uno o più record già disponibili nelle tabelle del database. Di seguito è riportata la procedura per aggiornare tutti i record che hanno SEX come "M". Qui aumenteremo l'ETÀ di tutti i maschi di un anno. Questo richiederà tre passaggi:

  • Preparazione di query SQL in base alle condizioni richieste. Questo sarà fatto usandoprepare() API.

  • Esecuzione di query SQL per selezionare tutti i risultati dal database. Questo sarà fatto usandoexecute() API.

  • Rilascio della maniglia di stato. Questo sarà fatto usandofinish() API.

  • Se tutto va bene allora commit questa operazione altrimenti puoi rollbacktransazione completa. Vedere la sezione successiva per le API di commit e rollback.

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;

Utilizzo dei valori di associazione

Potrebbe esserci un caso in cui la condizione non viene fornita in anticipo. Quindi puoi usare le variabili di bind, che assumeranno i valori richiesti in fase di esecuzione. I moduli Perl DBI fanno uso di un punto interrogativo al posto del valore effettivo e quindi i valori effettivi vengono passati tramite l'API execute () in fase di esecuzione. Di seguito è riportato l'esempio:

$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 alcuni casi si desidera impostare un valore, che non viene fornito in anticipo, quindi è possibile utilizzare il valore di associazione come segue. In questo esempio il reddito di tutti i maschi sarà impostato a 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();

Operazione DELETE

L'operazione DELETE è necessaria quando si desidera eliminare alcuni record dal database. Di seguito è riportata la procedura per eliminare tutti i record da TEST_TABLE dove AGE è uguale a 30. Questa operazione eseguirà i seguenti passaggi.

  • Preparazione di query SQL in base alle condizioni richieste. Questo sarà fatto usandoprepare() API.

  • Esecuzione di query SQL per eliminare i record richiesti dal database. Questo sarà fatto usandoexecute() API.

  • Rilascio della maniglia di stato. Questo sarà fatto usandofinish() API.

  • Se tutto va bene allora commit questa operazione altrimenti puoi rollback transazione completa.

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

Utilizzando do Statement

Se stai facendo un UPDATE, INSERT o DELETE non ci sono dati che tornano dal database, quindi c'è una scorciatoia per eseguire questa operazione. Puoi usaredo istruzione per eseguire uno qualsiasi dei comandi come segue.

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

dorestituisce un valore vero se è riuscito e un valore falso se non è riuscito. In realtà, se ha successo, restituisce il numero di righe interessate. Nell'esempio restituirà il numero di righe che sono state effettivamente eliminate.

Operazione COMMIT

Commit è l'operazione che dà un segnale verde al database per finalizzare le modifiche e dopo questa operazione nessuna modifica può essere riportata alla sua posizione originale.

Ecco un semplice esempio da chiamare commit API.

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

Operazione ROLLBACK

Se non sei soddisfatto di tutte le modifiche o riscontri un errore tra un'operazione e l'altra, puoi ripristinare tali modifiche per l'uso rollback API.

Ecco un semplice esempio da chiamare rollback API.

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

Inizia transazione

Molti database supportano le transazioni. Ciò significa che puoi fare un sacco di query che modificherebbero i database, ma nessuna delle modifiche viene effettivamente apportata. Quindi, alla fine, emetti la query SQL specialeCOMMITe tutte le modifiche vengono apportate contemporaneamente. In alternativa, puoi emettere la query ROLLBACK, nel qual caso tutte le modifiche vengono eliminate e il database rimane invariato.

Modulo DBI Perl fornito begin_workAPI, che abilita le transazioni (disattivando AutoCommit) fino alla chiamata successiva per il commit o il rollback. Dopo il successivo commit o rollback, AutoCommit verrà riattivato automaticamente.

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

Opzione AutoCommit

Se le tue transazioni sono semplici, puoi risparmiarti la fatica di dover emettere molti commit. Quando si effettua la chiamata di connessione, è possibile specificare un fileAutoCommitopzione che eseguirà un'operazione di commit automatico dopo ogni query riuscita. Ecco come appare:

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

Qui AutoCommit può assumere valore 1 o 0, dove 1 significa che AutoCommit è attivo e 0 significa che AutoCommit è disattivato.

Gestione automatica degli errori

Quando si effettua la chiamata di connessione, è possibile specificare un'opzione RaiseErrors che gestisce gli errori automaticamente. Quando si verifica un errore, DBI interromperà il programma invece di restituire un codice di errore. Se tutto ciò che vuoi è interrompere il programma in caso di errore, questo può essere conveniente. Ecco come appare:

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

Qui RaiseError può assumere valore 1 o 0.

Disconnessione del database

Per disconnettere la connessione al database, utilizzare disconnect API come segue -

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

Il comportamento della transazione del metodo di disconnessione è, purtroppo, indefinito. Alcuni sistemi di database (come Oracle e Ingres) eseguiranno automaticamente il commit delle modifiche in sospeso, ma altri (come Informix) eseguiranno il rollback di tutte le modifiche in sospeso. Le applicazioni che non utilizzano AutoCommit devono chiamare esplicitamente il commit o il rollback prima di chiamare la disconnessione.

Utilizzo di valori NULL

I valori indefiniti, o undef, vengono utilizzati per indicare i valori NULL. È possibile inserire e aggiornare colonne con un valore NULL come si farebbe con un valore non NULL. Questi esempi inseriscono e aggiornano l'età della colonna con un valore NULL -

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

Qui qq{} viene utilizzato per restituire una stringa tra virgolette prepareAPI. Tuttavia, è necessario prestare attenzione quando si tenta di utilizzare valori NULL in una clausola WHERE. Considera -

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

Associare un undef (NULL) al segnaposto non selezionerà le righe, che hanno un'età NULL! Almeno per i motori di database conformi allo standard SQL. Fare riferimento al manuale SQL del motore di database o qualsiasi libro SQL per le ragioni di ciò. Per selezionare esplicitamente NULL devi dire "WHERE age IS NULL".

Un problema comune è che un frammento di codice gestisca un valore che può essere definito o undef (non NULL o NULL) in fase di esecuzione. Una tecnica semplice consiste nel preparare l'istruzione appropriata in base alle esigenze e sostituire il segnaposto per i casi non 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 : ());

Alcune altre funzioni DBI

available_drivers

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

Restituisce un elenco di tutti i driver disponibili cercando i moduli DBD :: * nelle directory in @INC. Per impostazione predefinita, viene fornito un avviso se alcuni driver sono nascosti da altri con lo stesso nome nelle directory precedenti. Il passaggio di un valore vero per $ quiet inibirà l'avviso.

driver_installati

%drivers = DBI->installed_drivers();

Restituisce un elenco di nomi di driver e coppie di handle di driver per tutti i driver "installati" (caricati) nel processo corrente. Il nome del driver non include il prefisso "DBD ::".

Origine dei dati

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

Restituisce un elenco di origini dati (database) disponibili tramite il driver denominato. Se $ driver è vuoto o undef, viene utilizzato il valore della variabile di ambiente DBI_DRIVER.

citazione

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

Indicare un valore letterale stringa da utilizzare come valore letterale in un'istruzione SQL, eseguendo l'escape di eventuali caratteri speciali (come le virgolette) contenuti nella stringa e aggiungendo il tipo richiesto di virgolette esterne.

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

Per la maggior parte dei tipi di database, le virgolette restituiscono "Non" (comprese le virgolette esterne). È valido per il metodo quote () restituire un'espressione SQL che restituisce la stringa desiderata. Ad esempio:

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

may produce results which will be equivalent to

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

Metodi comuni a tutte le maniglie

err

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

Restituisce il codice di errore del motore di database nativo dall'ultimo metodo del driver chiamato. Il codice è in genere un numero intero, ma non dovresti assumerlo. È equivalente a $ DBI :: err o $ h-> err.

errstr

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

Restituisce il messaggio di errore del motore di database nativo dall'ultimo metodo DBI chiamato. Questo ha gli stessi problemi di durata del metodo "err" descritto sopra. Questo è equivalente a $ DBI :: errstr o $ h-> errstr.

righe

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

Restituisce il numero di righe influenzate dalla precedente istruzione SQL ed è equivalente a $ DBI :: righe.

traccia

$h->trace($trace_settings);

DBI mette in mostra una capacità estremamente utile di generare informazioni di tracciamento del runtime di ciò che sta facendo, che può essere un enorme risparmio di tempo quando si cerca di rintracciare strani problemi nei programmi DBI. È possibile utilizzare valori diversi per impostare il livello di traccia. Questi valori variano da 0 a 4. Il valore 0 significa disabilitare la traccia e 4 significa generare la traccia completa.

Le dichiarazioni interpolate sono vietate

Si consiglia vivamente di non utilizzare istruzioni interpolate come segue:

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

Quindi non usare l'istruzione interpolata invece usa bind value per preparare l'istruzione SQL dinamica.