Perl - Accès aux bases de données
Ce chapitre vous apprend comment accéder à une base de données dans votre script Perl. À partir de Perl 5, il est devenu très facile d'écrire des applications de base de données en utilisantDBImodule. DBI signifieDatabase Independent Interface pour Perl, ce qui signifie que DBI fournit une couche d'abstraction entre le code Perl et la base de données sous-jacente, vous permettant de changer d'implémentation de base de données très facilement.
Le DBI est un module d'accès à la base de données pour le langage de programmation Perl. Il fournit un ensemble de méthodes, de variables et de conventions qui fournissent une interface de base de données cohérente, indépendante de la base de données réelle utilisée.
Architecture d'une application DBI
DBI est indépendant de toute base de données disponible en backend. Vous pouvez utiliser DBI que vous travailliez avec Oracle, MySQL ou Informix, etc. Cela ressort clairement du diagramme d'archivage suivant.
Ici, DBI est responsable de prendre toutes les commandes SQL via l'API (c'est-à-dire, l'interface de programmation d'application) et de les envoyer au pilote approprié pour une exécution réelle. Et enfin, DBI est chargé de prendre les résultats du pilote et de les restituer au scritp appelant.
Notation et conventions
Tout au long de ce chapitre, les notations suivantes seront utilisées et il est recommandé de suivre également la même convention.
$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 methodsConnexion à la base de données
En supposant que nous allons travailler avec la base de données MySQL. Avant de vous connecter à une base de données, assurez-vous des points suivants. Vous pouvez utiliser notre tutoriel MySQL au cas où vous ne sauriez pas comment créer une base de données et des tables dans la base de données MySQL.
Vous avez créé une base de données avec un nom TESTDB.
Vous avez créé une table avec un nom TEST_TABLE dans TESTDB.
Cette table contient les champs FIRST_NAME, LAST_NAME, AGE, SEX et INCOME.
L'ID utilisateur "testuser" et le mot de passe "test123" sont définis pour accéder à TESTDB.
Le module Perl DBI est correctement installé sur votre machine.
Vous avez parcouru le didacticiel MySQL pour comprendre les bases de MySQL.
Voici l'exemple de connexion avec la base de données 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;Si une connexion est établie avec la source de données, un handle de base de données est retourné et enregistré dans $ dbh pour une utilisation ultérieure, sinon $ dbh est défini sur une valeur undef et $ DBI :: errstr renvoie une chaîne d'erreur.
Opération INSERT
L'opération INSERT est requise lorsque vous souhaitez créer des enregistrements dans une table. Ici, nous utilisons la table TEST_TABLE pour créer nos enregistrements. Ainsi, une fois notre connexion à la base de données établie, nous sommes prêts à créer des enregistrements dans TEST_TABLE. Voici la procédure pour créer un enregistrement unique dans TEST_TABLE. Vous pouvez créer autant d'enregistrements que vous le souhaitez en utilisant le même concept.
La création d'un enregistrement nécessite les étapes suivantes -
Préparation de l'instruction SQL avec l'instruction INSERT. Cela se fera en utilisantprepare() API.
Exécution d'une requête SQL pour sélectionner tous les résultats de la base de données. Cela se fera en utilisantexecute() API.
Libération de la poignée de déclaration. Cela se fera en utilisantfinish() API.
Si tout va bien alors commit cette opération sinon vous pouvez rollbacktransaction complète. La validation et la restauration sont expliquées dans les sections suivantes.
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;Utilisation des valeurs de liaison
Il peut y avoir un cas où les valeurs à saisir ne sont pas données à l'avance. Vous pouvez donc utiliser des variables de liaison qui prendront les valeurs requises au moment de l'exécution. Les modules Perl DBI utilisent un point d'interrogation à la place de la valeur réelle, puis les valeurs réelles sont transmises via l'API execute () au moment de l'exécution. Voici l'exemple -
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;LIRE l'opération
LIRE L'opération sur n'importe quelle base de données signifie récupérer des informations utiles de la base de données, c'est-à-dire un ou plusieurs enregistrements d'une ou plusieurs tables. Ainsi, une fois notre connexion à la base de données établie, nous sommes prêts à effectuer une requête dans cette base de données. Voici la procédure pour interroger tous les enregistrements ayant un âge supérieur à 20. Cela prendra quatre étapes -
Préparation de la requête SQL SELECT en fonction des conditions requises. Cela se fera en utilisantprepare() API.
Exécution d'une requête SQL pour sélectionner tous les résultats de la base de données. Cela se fera en utilisantexecute() API.
Récupérer tous les résultats un par un et imprimer ces résultats. Cela se fera en utilisant fetchrow_array() API.
Libération de la poignée de déclaration. Cela se fera en utilisantfinish() 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();Utilisation des valeurs de liaison
Il peut y avoir un cas où la condition n'est pas donnée à l'avance. Vous pouvez donc utiliser des variables de liaison, qui prendront les valeurs requises au moment de l'exécution. Les modules Perl DBI utilisent un point d'interrogation à la place de la valeur réelle, puis les valeurs réelles sont transmises via l'API execute () au moment de l'exécution. Voici l'exemple -
$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();Opération UPDATE
UPDATE Opérer sur n'importe quelle base de données signifie mettre à jour un ou plusieurs enregistrements déjà disponibles dans les tables de la base de données. Voici la procédure pour mettre à jour tous les enregistrements ayant SEX comme «M». Ici, nous augmenterons l'âge de tous les hommes d'un an. Cela prendra trois étapes -
Préparation de la requête SQL en fonction des conditions requises. Cela se fera en utilisantprepare() API.
Exécution d'une requête SQL pour sélectionner tous les résultats de la base de données. Cela se fera en utilisantexecute() API.
Libération de la poignée de déclaration. Cela se fera en utilisantfinish() API.
Si tout va bien alors commit cette opération sinon vous pouvez rollbacktransaction complète. Voir la section suivante pour les API de validation et de restauration.
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;Utilisation des valeurs de liaison
Il peut y avoir un cas où la condition n'est pas donnée à l'avance. Vous pouvez donc utiliser des variables de liaison, qui prendront les valeurs requises au moment de l'exécution. Les modules Perl DBI utilisent un point d'interrogation à la place de la valeur réelle, puis les valeurs réelles sont transmises via l'API execute () au moment de l'exécution. Voici l'exemple -
$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;Dans certains cas, vous souhaitez définir une valeur, qui n'est pas donnée à l'avance afin que vous puissiez utiliser la valeur de liaison comme suit. Dans cet exemple, le revenu de tous les hommes sera défini sur 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();Opération DELETE
L'opération DELETE est requise lorsque vous souhaitez supprimer certains enregistrements de votre base de données. Voici la procédure pour supprimer tous les enregistrements de TEST_TABLE où AGE est égal à 30. Cette opération exécutera les étapes suivantes.
Préparation de la requête SQL en fonction des conditions requises. Cela se fera en utilisantprepare() API.
Exécution d'une requête SQL pour supprimer les enregistrements requis de la base de données. Cela se fera en utilisantexecute() API.
Libération de la poignée de déclaration. Cela se fera en utilisantfinish() API.
Si tout va bien alors commit cette opération sinon vous pouvez rollback transaction complète.
$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;Utilisation de l'instruction do
Si vous effectuez une UPDATE, INSERT ou DELETE, aucune donnée ne revient de la base de données, il existe donc un raccourci pour effectuer cette opération. Vous pouvez utiliserdo instruction pour exécuter l'une des commandes comme suit.
$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');dorenvoie une valeur vraie en cas de succès et une valeur fausse en cas d'échec. En fait, s'il réussit, il renvoie le nombre de lignes affectées. Dans l'exemple, il renverrait le nombre de lignes qui ont été réellement supprimées.
Opération COMMIT
Valider est l'opération qui donne un signal vert à la base de données pour finaliser les modifications et après cette opération, aucune modification ne peut revenir à sa position d'origine.
Voici un exemple simple à appeler commit API.
$dbh->commit or die $dbh->errstr;Fonctionnement ROLLBACK
Si vous n'êtes pas satisfait de toutes les modifications ou si vous rencontrez une erreur entre les opérations, vous pouvez annuler ces modifications pour les utiliser rollback API.
Voici un exemple simple à appeler rollback API.
$dbh->rollback or die $dbh->errstr;Commencer la transaction
De nombreuses bases de données prennent en charge les transactions. Cela signifie que vous pouvez faire tout un tas de requêtes qui modifieraient les bases de données, mais aucune des modifications n'est réellement effectuée. Puis à la fin, vous émettez la requête SQL spécialeCOMMIT, et toutes les modifications sont effectuées simultanément. Vous pouvez également lancer la requête ROLLBACK, auquel cas toutes les modifications sont supprimées et la base de données reste inchangée.
Module Perl DBI fourni begin_workAPI, qui active les transactions (en désactivant AutoCommit) jusqu'au prochain appel de validation ou de restauration. Après la prochaine validation ou restauration, AutoCommit sera automatiquement réactivé.
$rc = $dbh->begin_work or die $dbh->errstr;Option de validation automatique
Si vos transactions sont simples, vous pouvez vous éviter d'avoir à émettre beaucoup de commits. Lorsque vous effectuez l'appel de connexion, vous pouvez spécifier unAutoCommitoption qui effectuera une opération de validation automatique après chaque requête réussie. Voici à quoi ça ressemble -
my $dbh = DBI->connect($dsn, $userid, $password,
{AutoCommit => 1})
or die $DBI::errstr;Ici, AutoCommit peut prendre la valeur 1 ou 0, où 1 signifie qu'AutoCommit est activé et 0 signifie qu'AutoCommit est désactivé.
Traitement automatique des erreurs
Lorsque vous effectuez l'appel de connexion, vous pouvez spécifier une option RaiseErrors qui gère automatiquement les erreurs. Lorsqu'une erreur se produit, DBI abandonne votre programme au lieu de renvoyer un code d'erreur. Si tout ce que vous voulez est d'abandonner le programme en cas d'erreur, cela peut être pratique. Voici à quoi ça ressemble -
my $dbh = DBI->connect($dsn, $userid, $password,
{RaiseError => 1})
or die $DBI::errstr;Ici, RaiseError peut prendre la valeur 1 ou 0.
Déconnexion de la base de données
Pour déconnecter la connexion à la base de données, utilisez disconnect API comme suit -
$rc = $dbh->disconnect or warn $dbh->errstr;Le comportement de transaction de la méthode de déconnexion est, malheureusement, indéfini. Certains systèmes de base de données (tels qu'Oracle et Ingres) valideront automatiquement toutes les modifications en suspens, mais d'autres (comme Informix) annuleront toutes les modifications en suspens. Les applications n'utilisant pas AutoCommit doivent appeler explicitement commit ou rollback avant d'appeler disconnect.
Utilisation des valeurs NULL
Des valeurs non définies, ou undef, sont utilisées pour indiquer des valeurs NULL. Vous pouvez insérer et mettre à jour des colonnes avec une valeur NULL comme vous le feriez avec une valeur non NULL. Ces exemples insèrent et mettent à jour l'âge de la colonne avec une valeur NULL -
$sth = $dbh->prepare(qq {
INSERT INTO TEST_TABLE (FIRST_NAME, AGE) VALUES (?, ?)
});
$sth->execute("Joe", undef);Ici qq{} est utilisé pour renvoyer une chaîne entre guillemets à prepareAPI. Cependant, il faut faire attention lorsque vous essayez d'utiliser des valeurs NULL dans une clause WHERE. Considérez -
SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?Lier un undef (NULL) à l'espace réservé ne sélectionnera pas les lignes, qui ont un âge NULL! Au moins pour les moteurs de base de données conformes à la norme SQL. Reportez-vous au manuel SQL de votre moteur de base de données ou à n'importe quel livre SQL pour les raisons. Pour sélectionner explicitement NULL, vous devez dire "WHERE age IS NULL".
Un problème courant consiste à ce qu'un fragment de code gère une valeur qui pourrait être définie ou non (non NULL ou NULL) au moment de l'exécution. Une technique simple consiste à préparer l'instruction appropriée selon les besoins et à remplacer l'espace réservé pour les cas 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 : ());Quelques autres fonctions DBI
available_drivers
@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);Renvoie une liste de tous les pilotes disponibles en recherchant les modules DBD :: * dans les répertoires de @INC. Par défaut, un avertissement est donné si certains pilotes sont masqués par d'autres du même nom dans les répertoires précédents. Passer une valeur vraie pour $ quiet inhibera l'avertissement.
drivers_installés
%drivers = DBI->installed_drivers();Renvoie une liste de paires de noms de pilotes et de descripteurs de pilotes pour tous les pilotes «installés» (chargés) dans le processus en cours. Le nom du pilote n'inclut pas le préfixe «DBD ::».
les sources de données
@ary = DBI->data_sources($driver);Renvoie une liste de sources de données (bases de données) disponibles via le pilote nommé. Si $ driver est vide ou undef, la valeur de la variable d'environnement DBI_DRIVER est utilisée.
citation
$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);Citez un littéral de chaîne à utiliser comme valeur littérale dans une instruction SQL, en échappant tous les caractères spéciaux (tels que les guillemets) contenus dans la chaîne et en ajoutant le type requis de guillemets extérieurs.
$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
$dbh->quote("Don't");Pour la plupart des types de bases de données, quote renverrait «Don» (y compris les guillemets extérieurs). La méthode quote () peut renvoyer une expression SQL qui correspond à la chaîne souhaitée. Par exemple -
$quoted = $dbh->quote("one\ntwo\0three")
may produce results which will be equivalent to
CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')Méthodes communes à toutes les poignées
se tromper
$rv = $h->err;
or
$rv = $DBI::err
or
$rv = $h->errRenvoie le code d'erreur du moteur de base de données natif de la dernière méthode de pilote appelée. Le code est généralement un entier, mais vous ne devez pas le supposer. Ceci est équivalent à $ DBI :: err ou $ h-> err.
errstr
$str = $h->errstr;
or
$str = $DBI::errstr
or
$str = $h->errstrRenvoie le message d'erreur du moteur de base de données natif de la dernière méthode DBI appelée. Cela pose les mêmes problèmes de durée de vie que la méthode "err" décrite ci-dessus. Ceci est équivalent à $ DBI :: errstr ou $ h-> errstr.
Lignes
$rv = $h->rows;
or
$rv = $DBI::rowsCela renvoie le nombre de lignes effectuées par l'instruction SQL précédente et équivalent à $ DBI :: rows.
trace
$h->trace($trace_settings);DBI possède une capacité extrêmement utile pour générer des informations de traçage d'exécution de ce qu'il fait, ce qui peut être un gain de temps énorme lorsque vous essayez de localiser des problèmes étranges dans vos programmes DBI. Vous pouvez utiliser différentes valeurs pour définir le niveau de trace. Ces valeurs varient de 0 à 4. La valeur 0 signifie désactiver la trace et 4 signifie générer une trace complète.
Les déclarations interpolées sont interdites
Il est fortement recommandé de ne pas utiliser d'instructions interpolées comme suit -
while ($first_name = <>) {
my $sth = $dbh->prepare("SELECT *
FROM TEST_TABLE
WHERE FIRST_NAME = '$first_name'");
$sth->execute();
# and so on ...
}Ainsi, n'utilisez pas l'instruction interpolée à la place, utilisez bind value pour préparer une instruction SQL dynamique.