Acceso a la base de datos de Perl

Este tutorial le enseñará cómo acceder a una base de datos dentro de su script Perl. A partir de Perl 5, se ha vuelto muy fácil escribir aplicaciones de base de datos utilizandoDBImódulo. DBI significaDatabase Independent Interface para Perl, lo que significa que DBI proporciona una capa de abstracción entre el código Perl y la base de datos subyacente, lo que le permite cambiar las implementaciones de la base de datos con mucha facilidad.

El DBI es un módulo de acceso a la base de datos para el lenguaje de programación Perl. Proporciona un conjunto de métodos, variables y convenciones que proporcionan una interfaz de base de datos coherente, independientemente de la base de datos real que se esté utilizando.

Arquitectura de una aplicación DBI

DBI es independiente de cualquier base de datos disponible en el backend. Puede utilizar DBI ya sea que esté trabajando con Oracle, MySQL o Informix, etc. Esto queda claro en el siguiente diagrama de arquitectura.

Aquí DBI es responsable de tomar todos los comandos SQL a través de la API (es decir, interfaz de programación de aplicaciones) y enviarlos al controlador apropiado para su ejecución real. Y finalmente DBI es responsable de tomar los resultados del controlador y devolverlos al script de llamada.

Notación y convenciones

A lo largo de este capítulo se utilizarán las siguientes notaciones y se recomienda que también siga la misma convención.

$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

Conexión de base de datos

Asumiendo que vamos a trabajar con una base de datos MySQL. Antes de conectarse a una base de datos asegúrese de lo siguiente. Puede utilizar nuestro tutorial de MySQL en caso de que no sepa cómo crear bases de datos y tablas en la base de datos MySQL.

Ha creado una base de datos con el nombre TESTDB.
Ha creado una tabla con el nombre TEST_TABLE en TESTDB.
Esta tabla tiene campos FIRST_NAME, APELLIDO, EDAD, SEXO e INGRESOS.
El ID de usuario "testuser" y la contraseña "test123" están configurados para acceder a TESTDB
Perl Module DBI está instalado correctamente en su máquina.
Ha seguido el tutorial de MySQL para comprender los conceptos básicos de MySQL.

A continuación se muestra el ejemplo de conexión con la base de datos 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 se establece una conexión con la fuente de datos, se devuelve un identificador de base de datos y se guarda en $ dbh para su uso posterior; de lo contrario, $ dbh se establece en un valor indefinido y $ DBI :: errstr devuelve una cadena de error.

INSERTAR Operación

La operación INSERT es necesaria cuando desea crear algunos registros en una tabla. Aquí estamos usando la tabla TEST_TABLE para crear nuestros registros. Entonces, una vez que se establece nuestra conexión con la base de datos, estamos listos para crear registros en TEST_TABLE. A continuación se muestra el procedimiento para crear un solo registro en TEST_TABLE. Puede crear tantos registros como desee utilizando el mismo concepto.

La creación de registros toma los siguientes pasos

Preparando la instrucción SQL con la instrucción INSERT. Esto se hará usandoprepare() API.
Ejecutando consulta SQL para seleccionar todos los resultados de la base de datos. Esto se hará usandoexecute() API.
Manija de liberación de Stattement. Esto se hará usandofinish() API
Si todo va bien entonces commit esta operación de lo contrario puede rollbacktransacción completa. La confirmación y la reversión se explican en las siguientes secciones.

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;

Usar valores de enlace

Puede darse el caso de que los valores que se introduzcan no se proporcionen de antemano. Por lo tanto, puede usar variables de vinculación que tomarán los valores requeridos en tiempo de ejecución. Los módulos Perl DBI utilizan un signo de interrogación en lugar del valor real y luego los valores reales se pasan a través de la API de ejecución () en el tiempo de ejecución. A continuación se muestra el ejemplo:

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;

Operación LEER

READ Operar en cualquier base de datos significa obtener información útil de la base de datos, es decir, uno o más registros de una o más tablas. Entonces, una vez que se establezca nuestra conexión con la base de datos, estamos listos para realizar una consulta en esta base de datos. A continuación se muestra el procedimiento para consultar todos los registros que tengan una EDAD superior a 20. Esto tomará cuatro pasos

Preparación previa de la consulta SQL SELECT según las condiciones requeridas. Esto se hará usandoprepare() API.
Ejecutando consulta SQL para seleccionar todos los resultados de la base de datos. Esto se hará usandoexecute() API.
Obteniendo todos los resultados uno por uno e imprimiéndolos. Esto se hará usando fetchrow_array() API.
Manija de liberación de Stattement. Esto se hará 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();

Usar valores de enlace

Puede haber un caso en el que la condición no se proporcione por adelantado. Por lo tanto, puede usar variables de vinculación que tomarán los valores requeridos en tiempo de ejecución. Los módulos Perl DBI utilizan un signo de interrogación en lugar del valor real y luego los valores reales se pasan a través de la API de ejecución () en el tiempo de ejecución. A continuación se muestra el ejemplo:

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

Operación ACTUALIZAR

ACTUALIZAR La operación en cualquier base de datos significa actualizar uno o más registros ya disponibles en las tablas de la base de datos. A continuación se muestra el procedimiento para actualizar todos los registros que tengan SEXO como 'M'. Aquí aumentaremos la EDAD de todos los machos en un año. Esto tomará tres pasos

Preparación de la consulta SQL según las condiciones requeridas. Esto se hará usandoprepare() API.
Ejecutando consulta SQL para seleccionar todos los resultados de la base de datos. Esto se hará usandoexecute() API.
Manija de liberación de Stattement. Esto se hará usandofinish() API
Si todo va bien entonces commit esta operación de lo contrario puede rollbacktransacción completa. Consulte la siguiente sección para conocer las API de confirmación y reversión.

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;

Usar valores de enlace

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

En algún caso, le gustaría establecer un valor que no se proporciona de antemano para que pueda utilizar el valor de enlace de la siguiente manera. En este ejemplo, los ingresos de todos los hombres se establecerán en 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();

Operación DELETE

La operación DELETE es necesaria cuando desea eliminar algunos registros de su base de datos. A continuación se muestra el procedimiento para eliminar todos los registros de TEST_TABLE donde EDAD es igual a 30. Esta operación llevará a cabo los siguientes pasos.

Preparación de la consulta SQL según las condiciones requeridas. Esto se hará usandoprepare() API.
Ejecución de una consulta SQL para eliminar los registros necesarios de la base de datos. Esto se hará usandoexecute() API.
Manija de liberación de Stattement. Esto se hará usandofinish() API
Si todo va bien entonces commit esta operación de lo contrario puede rollback transacción 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;

Utilizando do Declaración

Si está haciendo una ACTUALIZACIÓN, INSERTAR o ELIMINAR, no hay datos que regresen de la base de datos, por lo que hay un atajo para realizar esta operación. Puedes usardo instrucción para ejecutar cualquiera de los comandos de la siguiente manera.

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

dodevuelve un valor verdadero si tuvo éxito y un valor falso si falló. En realidad, si tiene éxito, devuelve el número de filas afectadas. En el ejemplo, devolvería el número de filas que realmente se eliminaron.

Operación COMMIT

Confirmar es la operación que da una señal verde a la base de datos para finalizar los cambios y después de esta operación ningún cambio puede ser revertido a su posición original.

Aquí hay un ejemplo simple para llamar commit API.

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

Operación ROLLBACK

Si no está satisfecho con todos los cambios o si encuentra un error entre cualquier operación, puede revertir esos cambios para usar rollback API.

Aquí hay un ejemplo simple para llamar rollback API.

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

Comenzar transacción

Muchas bases de datos admiten transacciones. Esto significa que puede realizar un montón de consultas que modificarían las bases de datos, pero ninguno de los cambios se realiza realmente. Luego, al final, emite la consulta SQL especialCOMMIT, y todos los cambios se realizan simultáneamente. Alternativamente, puede emitir la consulta ROLLBACK, en cuyo caso todos los cambios se desechan y la base de datos permanece sin cambios.

Módulo Perl DBI proporcionado begin_workAPI, que permite transacciones (desactivando AutoCommit) hasta la próxima llamada para confirmar o deshacer. Después de la siguiente confirmación o reversión, AutoCommit se volverá a activar automáticamente.

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

Opción AutoCommit

Si sus transacciones son simples, puede ahorrarse la molestia de tener que emitir muchas confirmaciones. Cuando realiza la llamada de conexión, puede especificar unAutoCommitopción que realizará una operación de confirmación automática después de cada consulta exitosa. Así es como se ve:

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

Aquí AutoCommit puede tomar el valor 1 o 0, donde 1 significa que AutoCommit está activado y 0 significa que AutoCommit está desactivado.

Manejo automático de errores

Cuando realiza la llamada de conexión, puede especificar una opción RaiseErrors que maneja los errores automáticamente. Cuando ocurre un error, DBI abortará su programa en lugar de devolver un código de falla. Si todo lo que desea es abortar el programa en caso de error, esto puede ser conveniente. Así es como se ve:

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

Aquí RaiseError puede tomar el valor 1 o 0.

Desconexión de la base de datos

Para desconectar la conexión de la base de datos, use disconnect API de la siguiente manera:

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

El comportamiento de transacción del método de desconexión es, lamentablemente, indefinido. Algunos sistemas de bases de datos (como Oracle e Ingres) realizarán automáticamente cualquier cambio pendiente, pero otros (como Informix) revertirán cualquier cambio pendiente. Las aplicaciones que no usan AutoCommit deben llamar explícitamente a commit o rollback antes de llamar a desconectar.

Usando valores NULL

Los valores indefinidos o indefinidos se utilizan para indicar valores NULL. Puede insertar y actualizar columnas con un valor NULL como lo haría con un valor no NULL. Estos ejemplos insertan y actualizan la antigüedad de la columna con un valor NULL:

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

aquí qq{} se utiliza para devolver una cadena entre comillas a prepareAPI. Sin embargo, se debe tener cuidado al intentar utilizar valores NULL en una cláusula WHERE. Considerar:

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

¡Al vincular un indef (NULO) al marcador de posición no se seleccionarán filas que tengan una antigüedad NULL! Al menos para motores de bases de datos que cumplen con el estándar SQL. Consulte el manual de SQL de su motor de base de datos o cualquier libro de SQL para conocer las razones de esto. Para seleccionar explícitamente NULL, debe decir "DONDE LA EDAD ES NULA".

Un problema común es que un fragmento de código maneje un valor que podría estar definido o indef (no NULL o NULL) en tiempo de ejecución. Una técnica simple es preparar la declaración adecuada según sea necesario y sustituir el marcador de posición por casos que no sean 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 : ());

Algunas otras funciones DBI

conductores_disponibles

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

Devuelve una lista de todos los controladores disponibles buscando módulos DBD :: * a través de los directorios en @INC. De forma predeterminada, se emite una advertencia si algunos controladores están ocultos por otros con el mismo nombre en directorios anteriores. Pasar un valor verdadero para $ quiet inhibirá la advertencia.

controladores_instalados

%drivers = DBI->installed_drivers();

Devuelve una lista de pares de controladores y nombres de controladores para todos los controladores 'instalados' (cargados) en el proceso actual. El nombre del controlador no incluye el prefijo 'DBD ::'.

fuentes de datos

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

Devuelve una lista de fuentes de datos (bases de datos) disponibles a través del controlador nombrado. Si $ driver está vacío o es indefinido, se utiliza el valor de la variable de entorno DBI_DRIVER.

citar

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

Cite un literal de cadena para usarlo como valor literal en una declaración SQL, escapando cualquier carácter especial (como comillas) contenido dentro de la cadena y agregando el tipo requerido de comillas externas.

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

Para la mayoría de los tipos de bases de datos, la cita devolvería 'Don''t' (incluidas las comillas exteriores). Es válido que el método quote () devuelva una expresión SQL que se evalúe como la cadena deseada. Por ejemplo:

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

may produce results which will be equivalent to

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

Métodos comunes a todos los mangos

errar

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

Devuelve el código de error del motor de base de datos nativo del último método de controlador llamado. El código suele ser un número entero, pero no debe asumirlo. Esto es equivalente a $ DBI :: err o $ h-> err.

errstr

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

Devuelve el mensaje de error del motor de base de datos nativo del último método DBI llamado. Esto tiene los mismos problemas de vida útil que el método "err" descrito anteriormente. Esto es equivalente a $ DBI :: errstr o $ h-> errstr.

filas

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

Esto devuelve el número de filas efectuadas por la instrucción SQL anterior y equivalente a $ DBI :: filas.

rastro

$h->trace($trace_settings);

DBI tiene una capacidad extremadamente útil para generar información de seguimiento en tiempo de ejecución de lo que está haciendo, lo que puede suponer un gran ahorro de tiempo al intentar localizar problemas extraños en sus programas DBI. Puede utilizar diferentes valores para establecer el nivel de seguimiento. Estos valores varían de 0 a 4. El valor 0 significa deshabilitar rastreo y 4 significa generar rastreo completo.

Las declaraciones interpoladas están prohibidas

Se recomienda encarecidamente no utilizar declaraciones interpoladas de la siguiente manera:

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

Por lo tanto, no use una declaración interpolada en su lugar use bind value para preparar una declaración SQL dinámica.

↰ Previous page