Ruby Acceso a bases de datos - Tutorial DBI

En este capítulo se explicará cómo usar Ruby para acceder a bases de datos.Ruby DBIEl módulo DBI proporciona a los scripts de Ruby una interfaz independiente de la base de datos, similar al módulo Perl DBI.

DBI significa Database Independent Interface, que representa la interfaz independiente de la base de datos de Ruby. DBI proporciona una capa de abstracción entre el código Ruby y la base de datos subyacente, lo que le permite cambiar de base de datos fácilmente. Define una serie de métodos, variables y especificaciones, ofreciendo una interfaz de base de datos consistente e independiente de la base de datos.

DBI puede interactuar con lo siguiente:

  • ADO (ActiveX Data Objects)
  • DB2
  • Frontbase
  • mSQL
  • MySQL
  • ODBC
  • Oracle
  • OCI8 (Oracle)
  • PostgreSQL
  • Proxy/Server
  • SQLite
  • SQLRelay

Arquitectura de la aplicación DBI

DBI es independiente de cualquier base de datos disponible en segundo plano. Ya sea que use Oracle, MySQL o Informix, puede usar DBI. El siguiente diagrama de arquitectura lo ilustra claramente.

Ruby DBI 架构

La arquitectura general de Ruby DBI utiliza dos capas:

  • Capa de interfaz de base de datos (DBI). Esta capa es independiente de la base de datos y proporciona una serie de métodos de acceso públicos, cuyo uso no depende del tipo de servidor de base de datos.
  • Capa de controlador de base de datos (DBD). Esta capa depende de la base de datos; diferentes controladores proporcionan acceso a diferentes motores de base de datos. MySQL, PostgreSQL, InterBase, Oracle, etc. usan controladores distintos. Cada controlador se encarga de interpretar las solicitudes de la capa DBI y mapear esas solicitudes a solicitudes adecuadas para un tipo determinado de servidor de base de datos.

Instalación

Si desea escribir scripts de Ruby para acceder a bases de datos MySQL, primero debe instalar el módulo MySQL de Ruby.

Instalar el paquete de desarrollo de MySQL

# Ubuntu sudo apt-get install mysql-client sudo apt-get install libmysqlclient15-dev # Centos yum install mysql-devel

En Mac OS, debe modificar el archivo ~/.bash_profile o ~/.profile y agregar el siguiente código:

MYSQL=/usr/local/mysql/bin export PATH=$PATH:$MYSQL export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH

O usar un enlace simbólico:

sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib

Instalar DBI con RubyGems (recomendado)

RubyGems se creó alrededor de noviembre de 2003 y pasó a formar parte de la biblioteca estándar de Ruby desde la versión 1.9. Para más detalles, consulte:Ruby RubyGems

Use gem para instalar dbi y dbd-mysql:

sudo gem install dbi sudo gem install mysql sudo gem install dbd-mysql

Instalar desde el código fuente (usar este método para versiones de Ruby menores a 1.9)

Este módulo es un DBD, se puedehttp://tmtm.org/downloads/mysql/ruby/descargar desde.

Después de descargar el último paquete, descomprímalo y entre en el directorio, luego ejecute el siguiente comando para instalarlo:

ruby extconf.rbOruby extconf.rb --with-mysql-dir=/usr/local/mysqlOruby extconf.rb --with-mysql-config

Luego compile:

make

Obtener e instalar Ruby/DBI

Puede descargar e instalar el módulo Ruby DBI desde el siguiente enlace:

https://github.com/erikh/ruby-dbi

Antes de comenzar la instalación, asegúrese de tener privilegios de root. Ahora, siga los pasos a continuación para instalar:

Paso 1

git clone https://github.com/erikh/ruby-dbi.git

O descargue directamente el paquete zip y descomprímalo.

Paso 2

Entre en el directorioruby-dbi-master, y en el directorio usesetup.rbscript para configurar. El comando de configuración más común es config sin ningún parámetro. Este comando, por defecto, se configura para instalar todos los controladores.

ruby setup.rb config

Más específicamente, puede usar la opción --with para listar las partes específicas que desea utilizar. Por ejemplo, si solo desea configurar el módulo DBI principal y el controlador de la capa MySQL DBD, introduzca el siguiente comando:

ruby setup.rb config --with=dbi,dbd_mysql

Paso 3

El último paso es construir el controlador. Use el siguiente comando para instalar:

ruby setup.rb setup ruby setup.rb install

Conexión a la base de datos

Suponiendo que usamos una base de datos MySQL, antes de conectarse a la base de datos, asegúrese de:

  • Ha creado una base de datos TESTDB.
  • Ha creado la tabla EMPLOYEE en TESTDB.
  • La tabla tiene los campos FIRST_NAME, LAST_NAME, AGE, SEX e INCOME.
  • Establezca el ID de usuario "testuser" y la contraseña "test123" para acceder a TESTDB
  • Ha instalado correctamente el módulo Ruby DBI en su máquina.
  • Ha leído el tutorial de MySQL y ha comprendido las operaciones básicas de MySQL.

A continuación se muestra un ejemplo de conexión a la base de datos MySQL "TESTDB":

Ejemplo

#!/usr/bin/ruby -w require "dbi" begin #Conectar al servidor MySQL dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") #Obtener la cadena de versión del servidor y mostrarla row = dbh.select_one("SELECT VERSION()") puts "Server version: " + row[0] rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" ensure #Desconectarse del servidor dbh.disconnect if dbh end

Al ejecutar este script, se producirá el siguiente resultado en una máquina Linux.

Server version: 5.0.45

Si la conexión se establece con una fuente de datos, se devuelve un manejador de base de datos (Database Handle) y se guarda endbhpara su uso posterior; de lo contrario,dbhse establecerá en nil,e.errye::errstrse devuelven el código de error y la cadena de error respectivamente.

Finalmente, antes de salir del programa, asegúrese de cerrar la conexión a la base de datos y liberar recursos.

Operación INSERT

Cuando desee crear registros en una tabla de base de datos, necesitará usar la operación INSERT.

Una vez establecida la conexión con la base de datos, podemos prepararnos para usardométodo oprepareyexecutemétodo para crear una tabla o insertar registros en la tabla de datos.

Usar la declaración do

Las declaraciones que no devuelven filas se pueden ejecutar llamando adométodo de procesamiento de base de datos. Este método toma un parámetro de cadena de declaración y devuelve el número de filas afectadas por la declaración.

dbh.do("DROP TABLE IF EXISTS EMPLOYEE") dbh.do("CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )" );

De manera similar, puede ejecutar SQLINSERTdeclaración para crear registros e insertarlos en la tabla EMPLOYEE.

Ejemplo

#!/usr/bin/ruby -w require "dbi" begin #Conectar al servidor MySQL dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") dbh.do( "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)" ) puts "Record has been created" dbh.commit rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" dbh.rollback ensure #Desconectarse del servidor dbh.disconnect if dbh end

Usoprepareyexecute

Puede usar el método de DBIprepareyexecutepara ejecutar declaraciones SQL en código Ruby.

Los pasos para crear un registro son los siguientes:

  • Prepare una declaración SQL con una declaración INSERT. Esto se realizará usandoprepareel método.
  • Ejecute la consulta SQL para seleccionar todos los resultados de la base de datos. Esto se realizará usandoexecuteel método.
  • Libere el manejador de declaración. Esto se realizará usandofinishel API.
  • Si todo va bien, entoncescommitla operación; de lo contrario, puederollbackconfirmar la transacción.

A continuación se muestra la sintaxis para usar estos dos métodos:

Ejemplo

sth = dbh.prepare(statement) sth.execute ... zero or more SQL operations ... sth.finish

Estos dos métodos se pueden usar para pasarbindvalores a declaraciones SQL. A veces, los valores ingresados pueden no estar dados de antemano; en este caso, se utilizan valores vinculados. Use un signo de interrogación (?) en lugar del valor real, y el valor real se pasa a través de la API execute().

El siguiente ejemplo crea dos registros en la tabla EMPLOYEE:

Ejemplo

#!/usr/bin/ruby -w require "dbi" begin #Conectar al servidor MySQL dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") sth = dbh.prepare( "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (?, ?, ?, ?, ?)" ) sth.execute('John', 'Poul', 25, 'M', 2300) sth.execute('Zara', 'Ali', 17, 'F', 1000) sth.finish dbh.commit puts "Record has been created" rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" dbh.rollback ensure #Desconectarse del servidor dbh.disconnect if dbh end

Si se usan varios INSERT a la vez, preparar una sola declaración y luego ejecutarla varias veces en un bucle es mucho más eficiente que llamar a do cada vez en el bucle.

Operación READ

La operación READ en cualquier base de datos se refiere a obtener información útil de la base de datos.

Una vez establecida la conexión con la base de datos, podemos prepararnos para consultar la base de datos. Podemos usardométodo oprepareyexecutemétodo para obtener valores de las tablas de la base de datos.

Los pasos para obtener registros son los siguientes:

  • Prepare la consulta SQL basada en las condiciones requeridas. Esto se hará mediante el uso delpreparemétodo para completar.
  • Ejecute la consulta SQL y seleccione todos los resultados de la base de datos. Esto se hará mediante el uso delexecutemétodo para completar.
  • Obtenga los resultados uno a uno y muéstrelos. Esto se hará mediante el uso delfetchmétodo para completar.
  • Libere el manejador de sentencia. Esto se hará mediante el uso delfinishmétodo para completar.

El siguiente ejemplo consulta todos los registros de la tabla EMPLOYEE cuyo salario (salary) supera los 1000.

Ejemplo

#!/usr/bin/ruby -w require "dbi" begin #Conectar al servidor MySQL dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") sth = dbh.prepare("SELECT * FROM EMPLOYEE WHERE INCOME > ?") sth.execute(1000) sth.fetch do |row| printf "First Name: %s, Last Name : %s\n", row[0], row[1] printf "Age: %d, Sex : %s\n", row[2], row[3] printf "Salary :%d \n\n", row[4] end sth.finish rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" ensure #Desconectar del servidor dbh.disconnect if dbh end

Esto producirá el siguiente resultado:

First Name: Mac, Last Name : Mohan
Age: 20, Sex : M
Salary :2000

First Name: John, Last Name : Poul
Age: 25, Sex : M
Salary :2300

Hay muchos otros métodos para obtener registros de la base de datos. Si está interesado, puede consultarla operación Read de Ruby DBI。

Operación UPDATE

La operación UPDATE en cualquier base de datos consiste en actualizar uno o más registros existentes en la base de datos. El siguiente ejemplo actualiza todos los registros con SEX como 'M'. Aquí, incrementaremos la edad (AGE) de todos los varones en un año. Esto se divide en tres pasos:

  • Prepare la consulta SQL basada en las condiciones requeridas. Esto se hará mediante el uso delpreparemétodo para completar.
  • Ejecute la consulta SQL y seleccione todos los resultados de la base de datos. Esto se hará mediante el uso delexecutemétodo para completar.
  • Libere el manejador de sentencia. Esto se hará mediante el uso delfinishmétodo para completar.
  • Si todo va bien, entoncescommitdicha operación; de lo contrario, puederollbackcompletar la transacción.

Ejemplo

#!/usr/bin/ruby -w require "dbi" begin #Conectar al servidor MySQL dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") sth = dbh.prepare("UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ?") sth.execute('M') sth.finish dbh.commit rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" dbh.rollback ensure #Desconectar del servidor dbh.disconnect if dbh end

Operación DELETE

Cuando desee eliminar registros de la base de datos, necesitará la operación DELETE. El siguiente ejemplo elimina todos los registros de EMPLOYEE cuya edad (AGE) supere los 20. Los pasos de esta operación son los siguientes:

  • Prepare la consulta SQL basada en las condiciones requeridas. Esto se hará mediante el uso delpreparemétodo para completar.
  • Ejecute la consulta SQL para eliminar los registros deseados de la base de datos. Esto se hará mediante el uso delexecutemétodo para completar.
  • Libere el manejador de sentencia. Esto se hará mediante el uso delfinishmétodo para completar.
  • Si todo va bien, entoncescommitdicha operación; de lo contrario, puederollbackcompletar la transacción.

Ejemplo

#!/usr/bin/ruby -w require "dbi" begin #Conectar al servidor MySQL dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") sth = dbh.prepare("DELETE FROM EMPLOYEE WHERE AGE > ?") sth.execute(20) sth.finish dbh.commit rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" dbh.rollback ensure #Desconectar del servidor dbh.disconnect if dbh end

Ejecutar transacciones

Una transacción es un mecanismo que garantiza la consistencia de las operaciones. Una transacción debe tener las siguientes cuatro propiedades:

  • Atomicidad:La atomicidad de una transacción se refiere a que el programa incluido en la transacción actúa como una unidad lógica de trabajo de la base de datos; las operaciones de modificación de datos que realiza deben ejecutarse todas o no ejecutarse en absoluto.
  • Consistencia:La consistencia de una transacción se refiere a que la base de datos debe estar en un estado consistente antes y después de la ejecución de una transacción. Si el estado de la base de datos satisface todas las restricciones de integridad, se dice que la base de datos es consistente.
  • Aislamiento:El aislamiento de una transacción significa que las transacciones concurrentes están aisladas entre sí, es decir, las operaciones internas de una transacción y los datos que se están operando deben estar bloqueados y no ser visibles para otras transacciones que intenten modificarlos.
  • Durabilidad:La durabilidad de una transacción significa que, cuando ocurre una falla en el sistema o en el medio, se garantiza que las actualizaciones de las transacciones confirmadas no se pierdan. Es decir, una vez que una transacción se confirma, los cambios que realiza en los datos de la base de datos deben ser permanentes y resistir cualquier falla del sistema de base de datos. La durabilidad se garantiza mediante la copia de seguridad y la recuperación de la base de datos.

DBI ofrece dos métodos para ejecutar transacciones. Uno es elcommitorollbackmétodo, que se utiliza para confirmar o revertir transacciones. El otro es eltransactionmétodo, que se puede utilizar para implementar transacciones. A continuación, presentaremos estos dos métodos simples para implementar transacciones:

Método I

El primer método utiliza elcommityrollbackmétodo de DBI para confirmar o cancelar explícitamente la transacción:

Ejemplo

dbh['AutoCommit'] = false #Establezca el autocommit en false. begin dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'John'") dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'Zara'") dbh.commit rescue puts "transaction failed" dbh.rollback end dbh['AutoCommit'] = true

Método II

El segundo método utiliza eltransactionmétodo. Este método es relativamente más simple, ya que requiere un bloque de código que contiene las sentencias que constituyen la transacción.transactionel método ejecuta el bloque y, según si el bloque se ejecutó correctamente, llama automáticamente acommitorollback：

Ejemplo

dbh['AutoCommit'] = false #Establezca el autocommit en false. dbh.transaction do |dbh| dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'John'") dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'Zara'") end dbh['AutoCommit'] = true

Operación COMMIT

Commit es una operación que indica que la base de datos ha completado los cambios; después de esta operación, todos los cambios son irreversibles.

A continuación se muestra un ejemplo simple de invocación delcommitmétodo.

dbh.commit

Operación ROLLBACK

Si no está satisfecho con uno o varios cambios y desea revertirlos por completo, utilice elrollbackmétodo.

A continuación se muestra un ejemplo simple de invocación delrollbackmétodo.

dbh.rollback

Desconectar la base de datos

Para desconectar la conexión a la base de datos, utilice la API disconnect.

dbh.disconnect

Si el usuario cierra la conexión a la base de datos mediante el método disconnect, DBI revertirá todas las transacciones pendientes. Sin embargo, no es necesario depender de ningún detalle de implementación de DBI; su aplicación puede llamar explícitamente a commit o rollback sin problemas.

Manejo de errores

Existen muchas fuentes diferentes de errores. Por ejemplo, errores de sintaxis al ejecutar sentencias SQL, fallos de conexión, o llamar al método fetch sobre un manejador de sentencia que ya ha sido cancelado o completado.

Si algún método de DBI falla, DBI lanza una excepción. Los métodos de DBI pueden lanzar cualquier tipo de excepción, pero las dos clases de excepción más importantes sonDBI::InterfaceErroryDBI::DatabaseError。

Los objetos Exception de estas clases tienenerr、errstrystatetres atributos, que representan respectivamente el número de error, una cadena descriptiva de error y un código de error estándar. La descripción detallada de los atributos es la siguiente:

  • err：Devuelve la representación entera del error ocurrido; si DBD no lo admite, devuelvenil. Por ejemplo, Oracle DBD devuelveORA-XXXXla parte numérica del mensaje de error.
  • errstr：Devuelve la representación de cadena del error ocurrido.
  • state：Devuelve el código SQLSTATE del error ocurrido. SQLSTATE es una cadena de cinco caracteres de longitud. La mayoría de los DBD no lo admiten, por lo que devuelven nil.

En los ejemplos anteriores ya ha visto el siguiente código:

rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" dbh.rollback ensure #Desconectar del servidor dbh.disconnect if dbh end

Para obtener información de depuración sobre lo que se ejecuta durante la ejecución del script, puede habilitar el rastreo. Para ello, primero debe descargar el módulo dbi/trace y luego llamar al método que controla el modo de rastreo y el destino de salida:tracemétodo:

require "dbi/trace" .............. trace(mode, destination)

El valor de mode puede ser 0 (off), 1, 2 o 3; el valor de destination debe ser un objeto IO. Los valores predeterminados son 2 y STDERR, respectivamente.

Bloques de código de métodos

Hay algunos métodos para crear manejadores. Estos métodos se invocan mediante bloques de código. La ventaja de usar bloques de código con métodos es que proporcionan el manejador como parámetro al bloque y automáticamente limpian el manejador al finalizar el bloque. A continuación se presentan algunos ejemplos que ayudan a entender este concepto.

  • DBI.connect ：Este método genera un manejador de base de datos; se recomienda llamar al final del bloquedisconnectpara desconectar la base de datos.
  • dbh.prepare ：Este método genera un manejador de sentencia; se recomienda llamar al final del bloque. Dentro del bloque, debe llamar alfinishDentro del bloque, debe llamar alexecutemétodo para ejecutar la sentencia.
  • dbh.execute ：Este método es similar a dbh.prepare, pero con dbh.execute no se necesita llamar al método execute dentro del bloque. El manejador de sentencia se ejecuta automáticamente.

Ejemplo 1

DBI.connectPuede llevar un bloque de código al que se le pasa el manejador de base de datos, y al final del bloque se desconecta automáticamente el manejador.

dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", 
                  "testuser", "test123") do |dbh|

Ejemplo 2

dbh.preparePuede llevar un bloque de código al que se le pasa el manejador de sentencia, y al final del bloque se llama automáticamente a finish.

dbh.prepare("SHOW DATABASES") do |sth| sth.execute puts "Databases: " + sth.fetch_all.join(", ") end

Ejemplo 3

dbh.executePuede llevar un bloque de código al que se le pasa el manejador de sentencia, y al final del bloque se llama automáticamente a finish.

dbh.execute("SHOW DATABASES") do |sth| puts "Databases: " + sth.fetch_all.join(", ") end

DBI transactionEl método también puede llevar un bloque de código, como ya se explicó en los capítulos anteriores.

Funciones y atributos de controladores específicos

DBI permite que los controladores de base de datos proporcionen funciones adicionales específicas de la base de datos; los usuarios pueden invocarlas a través delfuncmétodo de cualquier objeto Handle para invocarlas.

Se pueden establecer u obtener atributos específicos del controlador usando el[]= or []método.

DBD::Mysql implementa las siguientes funciones específicas del controlador:

No.Función y descripción
1dbh.func(:createdb, db_name)
Crea una nueva base de datos.
2dbh.func(:dropdb, db_name)
Elimina una base de datos.
3dbh.func(:reload)
Ejecuta una operación de recarga.
4dbh.func(:shutdown)
Cierra el servidor.
5dbh.func(:insert_id) => Fixnum
Devuelve el valor AUTO_INCREMENT más reciente de esa conexión.
6dbh.func(:client_info) => String
Devuelve información del cliente MySQL según la versión.
7dbh.func(:client_version) => Fixnum
Devuelve información del cliente según la versión. Esto es similar a :client_info, pero devuelve un fixnum en lugar de una cadena.
8dbh.func(:host_info) => String
Devuelve la información del host.
9dbh.func(:proto_info) => Fixnum
Devuelve el protocolo utilizado para la comunicación.
10dbh.func(:server_info) => String
Devuelve la información del lado del servidor MySQL según la versión.
11dbh.func(:stat) => Stringb>
Devuelve el estado actual de la base de datos.
12dbh.func(:thread_id) => Fixnum
Devuelve el ID del hilo actual.

#!/usr/bin/ruby require "dbi" begin #Conectar al servidor MySQL dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") puts dbh.func(:client_info) puts dbh.func(:client_version) puts dbh.func(:host_info) puts dbh.func(:proto_info) puts dbh.func(:server_info) puts dbh.func(:thread_id) puts dbh.func(:stat) rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" ensure dbh.disconnect if dbh end

Esto producirá el siguiente resultado:

5.0.45
50045
Localhost via UNIX socket
10
5.0.45
150621
Uptime: 384981  Threads: 1  Questions: 1101078  Slow queries: 4 \
Opens: 324  Flush tables: 1  Open tables: 64  \
Queries per second avg: 2.860