Ruby 访问数据库

  • DBI教程

    本章教您如何使用Ruby访问数据库。Ruby DBI模块提供类似于Perl DBI模块的Ruby脚本独立于数据库的接口。DBI代表Ruby的数据库独立接口,这意味着DBI在Ruby代码和基础数据库之间提供了一个抽象层,使您可以真正轻松地切换数据库实现。它定义了一组方法,变量和约定,它们提供了一致的数据库接口,而与所使用的实际数据库无关。DBI可以与以下接口:
    • ADO (ActiveX Data Objects)
    • DB2
    • Frontbase
    • mSQL
    • MySQL
    • ODBC
    • Oracle
    • OCI8 (Oracle)
    • PostgreSQL
    • Proxy/Server
    • SQLite
    • SQLRelay
  • DBI应用程序的体系结构

    DBI独立于后端中可用的任何数据库。无论您是使用Oracle,MySQL还是Informix等,都可以使用DBI。从以下体系结构图中可以清楚地看出这一点。
    dbi
    Ruby DBI的通用架构使用两层-
    • 数据库接口(DBI)层。该层是独立于数据库的,并且提供了一组常用访问方法,无论您要与之通信的数据库服务器的类型如何,都以相同的方式使用它们。
    • 数据库驱动程序(DBD)层。该层取决于数据库;不同的驱动程序提供对不同数据库引擎的访问。有一个用于MySQL的驱动程序,另一个用于PostgreSQL的驱动程序,另一个用于InterBase的驱动程序,另一个用于Oracle的驱动程序,依此类推。每个驱动程序解释来自DBI层的请求,并将它们映射到适合给定类型的数据库服务器的请求。
    首先下载gem-
    ::https://rubygems.org/pages/download来管理各种依赖和API(这里安装gem我们把gem的可执行目录bin添加到了环境变量PATH以便于使用gem命令)。
    安装完成后我们进行依赖安装,和DBI和MySQL驱动的安装。
    
    >  gem install dbi                  # 安装DBI
    >  gem install mysql
    >  gem install dbd-mysql 
    
  • 数据库连接

    假设我们要使用MySQL数据库,在连接数据库之前,请确保以下内容-
    • 假设您已经创建了数据库test。
    • 您已经在TESTDB中创建了EMPLOYEE。
    • 该表具有字段FIRST_NAME,LAST_NAME,AGE,SEX和INCOME。
    • 用户ID“root”和密码“123465”设置为访问test数据库。
    • Ruby Module DBI已正确安装在您的机器上。
    • 您已经阅读了MySQL教程,以了解MySQL基础。
    以下是连接MySQL数据库“test”的示例
    
    require "dbi"
     
    begin
         # 连接到 MySQL 服务器
         dbh = DBI.connect("DBI:Mysql:test:192.168.61.201", "root", "123456")
         # 获取服务器版本字符串,并显示
         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
         # 断开与服务器的连接
         dbh.disconnect if dbh
    end
    
    运行上面的代码,如果您能正确连接数据库,应该返回类型下面的消息:
    
    Server version: 8.0.11
    
    如果与数据源建立了连接,则将数据库句柄返回并保存到dbh中以供进一步使用,否则dbh设置为nil值,并且e.err和e :: errstr分别返回错误代码和错误字符串。最后,请确保关闭数据库连接并释放资源。
  • 插入操作

    要在数据库表中创建记录时需要INSERT操作。建立数据库连接后,我们准备使用do方法或prepare和execute方法在数据库表中创建表或记录。
    使用do语句
    您可以执行SQL INSERT语句在EMPLOYEE表中创建一条记录。
    
    require "dbi"
     
    begin
         # 连接到 MySQL 服务器
         dbh = DBI.connect("DBI:Mysql:test:192.168.61.201", "root", "123456")
         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
         # disconnect from server
         dbh.disconnect if dbh
     end
    
    使用prepare和execute
    您可以使用DBI类的prepare和execute方法通过Ruby代码执行SQL语句。
    记录创建采取以下步骤-
    • 用INSERT语句准备SQL语句。这将使用prepare方法完成。
    • 执行SQL查询以从数据库中选择所有结果。这将使用execute方法完成。
    • 释放语句句柄。这将使用finish API 完成
    • 如果一切正常,请执行此操作,否则您可以回滚整个事务。
    以下是使用这两种方法的语法-
    
    sth = dbh.prepare(statement)
    sth.execute
       ... zero or more SQL operations ...
    sth.finish
    
    这两种方法可用于将绑定值传递给SQL语句。在某些情况下,可能不会事先给出要输入的值。在这种情况下,将使用绑定值。使用问号(?)代替实际值,然后实际值通过execute() API传递。以下是在EMPLOYEE表中创建两个记录的示例-
    
    require "dbi"
     
    begin
       # 连接到 MySQL 服务器
       dbh = DBI.connect("DBI:Mysql:test:192.168.61.201", "root", "123456")
       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
       # disconnect from server
       dbh.disconnect if dbh
    end
    
    如果一次有多个INSERT,则首先准备一条语句,然后在循环中多次执行它比每次在循环中调用都更有效。
  • 读操作

    任何数据库上的读操作都意味着从数据库中获取一些有用的信息。建立数据库连接后,就可以对这个数据库进行查询了。我们可以使用do方法,也可以使用prepare和execute方法从数据库表中获取值。
    记录获取采取以下步骤-
    • 根据所需条件准备SQL查询。这将使用prepare方法完成。
    • 执行SQL查询以从数据库中选择所有结果。这将使用execute方法完成。
    • 一张一张地获取所有结果并打印这些结果。这将使用fetch方法完成。
    • 释放语句句柄。这将使用finish方法完成。
    以下是从薪水超过1000的EMPLOYEE表中查询所有记录的过程。
    
    require "dbi"
     
    begin
       # 连接到 MySQL 服务器
       dbh = DBI.connect("DBI:Mysql:test:192.168.61.201", "root", "123456")
       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
       # disconnect from server
       dbh.disconnect if dbh
    end
    
    这将产生以下结果-
    
    First Name: Mac, Last Name : Mohan
    Age: 20, Sex : M
    Salary :2000
    
    First Name: John, Last Name : Poul
    Age: 25, Sex : M
    Salary :2300
    
    还有更多捷径可以从数据库中获取记录。如果您有兴趣,请查看DBI方法
  • 更新操作

    UPDATE对任何数据库的操作意味着更新一个或多个记录,这些记录已在数据库中可用。以下是更新所有SEX为'M'的记录的过程。在这里,我们将所有男性的年龄提高一年。这将采取三个步骤-
    • 根据所需条件准备SQL查询。这将使用prepare方法完成。
    • 执行SQL查询以从数据库中选择所有结果。这将使用execute方法完成。
    • 释放语句句柄。这将使用finish方法完成。
    • 如果一切正常,请执行此操作,否则您可以回滚整个事务。
    
    require "dbi"
     
    begin
       # 连接到 MySQL 服务器
       dbh = DBI.connect("DBI:Mysql:test:192.168.61.201", "root", "123456")
       sth = dbh.prepare("UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ?")
       sth.execute('M')
       sth.finish
       dbh.commit
       puts "Update Success!"
    rescue DBI::DatabaseError => e
       puts "An error occurred"
       puts "Error code:    #{e.err}"
       puts "Error message: #{e.errstr}"
       dbh.rollback
       puts "Update Fail!"
    ensure
       # disconnect from server
       dbh.disconnect if dbh
    end
    
    如果一切正常,将产生以下结果-
    
    Update Success!
    
  • 删除操作

    要从数据库中删除某些记录时,需要执行DELETE操作。以下是从AGE大于20的EMPLOYEE删除所有记录的过程。此操作将执行以下步骤。
    • 根据所需条件准备SQL查询。这将使用prepare方法完成。
    • 执行SQL查询以从数据库中删除所需的记录。这将使用execute方法完成。
    • 释放语句句柄。这将使用finish方法完成。
    • 如果一切正常,请执行此操作,否则您可以回滚整个事务。
    
    require "dbi"
     
    begin
       # 连接到 MySQL 服务器
       dbh = DBI.connect("DBI:Mysql:test:192.168.61.201", "root", "123456")
       sth = dbh.prepare("UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ?")
       sth = dbh.prepare("DELETE FROM EMPLOYEE WHERE AGE > ?")
       sth.execute(20)
       sth.finish
       dbh.commit
       puts "Delete Success!"
    rescue DBI::DatabaseError => e
       puts "An error occurred"
       puts "Error code:    #{e.err}"
       puts "Error message: #{e.errstr}"
       dbh.rollback
       puts "Delete Fail!"
    ensure
       # disconnect from server
       dbh.disconnect if dbh
    end
    
    如果一切正常,将产生以下结果-
    
    Delete Success!
    
  • 执行事务

    事务是一种确保数据一致性的机制。事务应具有以下四个属性-
      原子性 -交易完成或什么都没有发生。 一致性 -事务必须以一致状态开始,而使系统处于一致状态。 隔离性 -交易的中间结果在当前交易之外不可见。 持久性 -提交事务后,即使在系统故障后,效果也将持续存在。
    DBI提供了两种方法来提交或回滚事务。还有另一种称为事务的方法,可用于实现事务。有两种简单的方法来实现事务-
    方法一
    第一种方法使用DBI的commit和rollback方法显式提交或取消事务-
    
    dbh['AutoCommit'] = false # Set auto commit to 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
    
    方法二
    第二种方法使用事务处理方法。这比较简单,因为它需要一个包含构成事务的语句的代码块。的交易方法执行块,然后调用提交或回滚自动,这取决于该块是否成功或失败-
    
    dbh['AutoCommit'] = false # Set auto commit to 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
    
    提交操作
    Commit是一项操作,它向数据库发出绿色信号以完成更改,并且在执行此操作后,将无法还原任何更改。这是一个调用commit方法的简单示例。
    
    dbh.commit
    
    回滚操作
    如果您对一项或多项更改不满意,并且想要完全还原这些更改,请使用回滚方法。这是调用rollback方法的简单示例。
    
    dbh.rollback
    
    断开数据库
    要断开数据库连接,请使用断开连接API。
    
    dbh.disconnect
    
    如果用户使用断开连接方法关闭了与数据库的连接,则DBI将回滚所有未完成的事务。但是,最好不要显式调用提交或回滚,而不必依赖于DBI的任何实现细节。
  • 处理错误

    错误的来源很多。一些示例是已执行的SQL语句中的语法错误,连接失败或为已取消或完成的语句句柄调用fetch方法。如果DBI方法失败,则DBI引发异常。DBI方法可以引发几种类型的异常中的任何一种,但是两个最重要的异常类是DBI::InterfaceErrorDBI::DatabaseError。这些类的异常对象具有名为err,errstr和state的三个属性,分别表示错误编号,描述性错误字符串和标准错误代码。属性说明如下-
    • err - 返回发生的错误的整数表示形式; 如果DBD不支持,则返回nil。例如,Oracle DBD返回ORA-XXXX错误消息的数字部分。
    • errstr - 返回所发生错误的字符串表示形式。
    • state - 返回的发生和error.The SQLSTATE的SQLSTATE代码是一个五字符的长字符串。大多数DBD不支持此功能,而是返回nil。
    您已经在大多数示例中看到了以下代码-
    要获取有关脚本执行时的操作的调试信息,可以启用跟踪。为此,必须首先加载dbi/trace模块,然后调用控制跟踪模式和输出目标的trace方法-
    
    require "dbi/trace"
    ..............
    
    trace(mode, destination)
    
    模式值可以是0(关闭),1、2或3,并且目标应该是IO对象。默认值分别为2和STDERR。
  • 带方法的代码块

    有一些创建句柄的方法。可以使用代码块来调用这些方法。将代码块与方法一起使用的优点是,它们将代码块的句柄作为其参数提供,并在代码块终止时自动清除该句柄。很少有例子可以理解这个概念。
    • DBI.connect - 此方法生成数据库句柄,建议在块末尾调用断开连接以断开数据库连接。
    • dbh.prepare - 此方法生成一个语句句柄,建议在代码块末尾完成。在该块中,您必须调用execute方法来执行该语句。
    • dbh.execute - 此方法类似,只不过我们不需要在块内调用execute。语句句柄将自动执行。
    例子1
    DBI.connect可以获取一个代码块,将数据库句柄传递给它,然后按如下所示自动断开该块末尾的句柄。-
    
    dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") do |dbh|
    
    例子2
    dbh.prepare可以获取一个代码块,将语句句柄传递给它,然后按如下所示在该块的末尾自动调用finish。
    
    dbh.prepare("SHOW DATABASES") do |sth|
       sth.execute
       puts "Databases: " + sth.fetch_all.join(", ")
    end
    
    例子3
    dbh.execute可以获取一个代码块,将语句句柄传递给它,并在该块的末尾自动调用finish,如下所示:
    
    dbh.execute("SHOW DATABASES") do |sth|
       puts "Databases: " + sth.fetch_all.join(", ")
    end
    
    DBI 事务方法还采用了上面已描述的代码块。
  • 驱动程序特定的功能和属性

    DBI使数据库驱动程序提供附加的特定于数据库的功能,用户可以通过任何Handle对象的func方法调用这些功能。支持特定于驱动程序的属性,可以使用[]=或[]方法进行设置或获取。
    
    require "dbi"
    begin
       # 连接到 MySQL 服务器
       dbh = DBI.connect("DBI:Mysql:test:192.168.61.201", "root", "123456")
       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
    
    
    5.5.64-MariaDB
    50564
    192.168.61.201 via TCP/IP
    10
    8.0.11
    13
    Uptime: 7930  Threads: 2  Questions: 16  Slow queries: 0  Opens: 131  Flush tables: 2  Open tables: 107  Queries per second avg: 0.002