PostgreSQL Ruby

最后修改于 2020 年 7 月 6 日

这是一个针对 PostgreSQL 数据库的 Ruby 编程教程。它涵盖了使用 Ruby 语言进行 PostgreSQL 编程的基础知识。

PostgreSQL

PostgreSQL 是一个功能强大、开源的对象关系数据库系统。它是一个多用户数据库管理系统。它运行在多个平台，包括 Linux、FreeBSD、Solaris、Microsoft Windows 和 Mac OS X。PostgreSQL 由 PostgreSQL 全球开发组开发。

PostgreSQL 拥有复杂的功能，例如多版本并发控制 (MVCC)、时间点恢复、表空间、异步复制、嵌套事务（保存点）、在线/热备份、复杂的查询规划器/优化器以及用于容错的预写日志。它支持国际字符集、多字节字符编码、Unicode，并且在排序、大小写敏感性和格式化方面具有区域感知能力。

Ruby

Ruby 是一种动态、反射、通用的面向对象编程语言。最初的作者是日本程序员松本行弘。Ruby 于 1995 年首次出现。Ruby 支持各种编程范式。这包括面向对象、反射、命令式和反射式编程。

Ruby pg

Ruby pg 是一个模块，允许 Ruby 程序与 PostgreSQL 数据库引擎交互。它支持 libpq C 库中定义的函数。

安装

我们将安装 PostgreSQL 数据库和额外的必要库。

$ sudo apt-get install postgresql

在基于 Debian 的系统上，我们可以使用上述命令从软件包安装 PostgreSQL 数据库。

$ sudo update-rc.d -f postgresql remove
 Removing any system startup links for /etc/init.d/postgresql ...
   /etc/rc0.d/K21postgresql
   /etc/rc1.d/K21postgresql
   /etc/rc2.d/S19postgresql
   /etc/rc3.d/S19postgresql
   /etc/rc4.d/S19postgresql
   /etc/rc5.d/S19postgresql
   /etc/rc6.d/K21postgresql

如果从软件包安装 PostgreSQL 数据库，它会自动添加到操作系统的启动脚本中。如果我们只是学习如何使用数据库，则每次启动系统时都无需启动数据库。上述命令会删除 PostgreSQL 数据库的任何系统启动链接。

$ sudo apt-get install libpq-dev

要编译 Ruby pg 模块，我们还需要 C libpq 库的开发文件。

$ sudo -u postgres psql postgres
psql (9.3.9)
Type "help" for help.

postgres=# \password postgres

我们为 postgres 用户设置密码。

$ sudo apt-get install ruby-dev

我们安装 Ruby 开发库，这些库是编译 Ruby 扩展模块所必需的。

$ sudo gem install pg

我们安装 Ruby pg 模块，它是 PostgreSQL 数据库的 Ruby 接口。

启动和停止 PostgreSQL

在下一节中，我们将展示如何启动 PostgreSQL 数据库、停止它以及查询其状态。

$ sudo service postgresql start
 * Starting PostgreSQL 9.3 database server     [ OK ]

在基于 Debian 的 Linux 上，我们可以使用 service postgresql start 命令启动服务器。

$ sudo service postgresql status
9.3/main (port 5432): online

我们使用 service postgresql status 命令检查 PostgreSQL 是否正在运行。

$ sudo service postgresql stop
 * Stopping PostgreSQL 9.3 database server     [ OK ]

我们使用 service postgresql stop 命令停止 PostgreSQL。

$ service postgresql status
9.3/main (port 5432): down

此时，service postgresql status 命令报告 PostgreSQL 数据库已停止。

创建用户和数据库

在以下步骤中，我们创建一个新的数据库用户和数据库。

$ sudo -u postgres createuser janbodnar

我们在 PostgreSQL 系统中创建一个新角色。我们允许它能够创建新数据库。在数据库世界中，角色就是用户。角色独立于操作系统用户。

$ sudo -u postgres psql postgres
psql (9.3.9)
Type "help" for help.

postgres=# ALTER USER janbodnar WITH password 'pswd37';
ALTER ROLE
postgres=# \q

使用 psql 命令，我们为新用户添加密码。

PostgreSQL 通常对本地连接使用信任或对等身份验证策略。在信任身份验证策略的情况下，PostgreSQL 假定任何能够连接到服务器的人都有权使用他们指定的任何数据库用户名（甚至是超级用户名称）访问数据库。连接数据库时无需密码。（数据库和用户列中的限制仍然适用。）信任身份验证适用于单用户工作站上的本地连接，并且非常方便。它通常不适用于多用户机器。在对等身份验证策略的情况下，数据库用户名必须与操作系统用户名匹配。

$ sudo -u postgres createdb testdb --owner janbodnar

使用 createdb 命令，我们创建一个名为 testdb 的新数据库。其所有者是新的数据库用户。

libpq 库

libpq 库是 PostgreSQL 的 C 接口。它是一组库函数，允许客户端程序与 PostgreSQL 交互。它也是其他几个 PostgreSQL 应用程序接口的底层引擎，包括那些用 C++、Perl、PHP、Ruby、Python 和 Tcl 编写的接口。

Ruby pg 模块是 libpg 库的封装。

lib_version.rb

#!/usr/bin/ruby

require 'pg'

puts 'Version of libpg: ' + PG.library_version.to_s

该程序打印 libpq 库的版本。

require 'pg'

我们包含 pg 模块。

puts 'Version of libpg: ' + PG.library_version.to_s

library_version 方法返回正在使用的 libpq 的版本。

$ ./lib_version.rb 
Version of libpg: 90309

库版本为 9.3.9。

服务器版本

在以下示例中，我们找出 PostgreSQL 数据库的版本。

server_version.rb

#!/usr/bin/ruby

require 'pg'

begin

    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    puts con.server_version

rescue PG::Error => e

    puts e.message 
    
ensure

    con.close if con
    
end

该示例连接到 PostgreSQL 数据库，执行 server_version 方法，打印版本，关闭连接并进行清理。

...
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
...

在 pg_hba.conf 中，我们有 peer 默认身份验证方法。在此方法中，数据库用户名必须与操作系统用户名匹配。无需密码即可建立连接。

con = PG.connect :dbname => 'testdb', :user => 'janbodnar'

使用 connect 方法，我们连接到数据库。在连接字符串中，我们提供用户名和数据库名。

rescue PG::Error => e

    puts e.message

我们检查错误。这很重要，因为与数据库打交道容易出错。

ensure

    con.close if con
    
end

最后，我们释放资源。

$ ./server_version.rb 
90309

运行程序，我们获得数据库服务器版本。

使用密码进行身份验证

接下来，我们将使用密码对数据库服务器进行身份验证。在本教程中的所有其他示例中，我们假定使用 peer 或 trust 身份验证模式。我们将 pg_hba.conf 文件中本地连接的身份验证类型更改为 md5。

$ sudo service postgresql restart

要应用更改，必须重新启动数据库服务器。

password_authentication.rb

#!/usr/bin/ruby

require 'pg'

begin

    con = PG.connect :dbname => 'testdb', :user => 'janbodnar', 
        :password => 'pswd37'

    user = con.user
    db_name = con.db
    pswd = con.pass
    
    puts "User: #{user}"
    puts "Database name: #{db_name}"
    puts "Password: #{pswd}" 
    
rescue PG::Error => e

    puts e.message 
    
ensure

    con.close if con
    
end

在此示例中，我们使用密码连接到数据库。我们打印当前数据库连接的用户名、数据库名和密码。

con = PG.connect :dbname => 'testdb', :user => 'janbodnar', 
    :password => 'pswd37'

在连接字符串中，我们添加密码选项。

user = con.user

user 方法返回连接的用户名。

db_name = con.db

db 方法返回连接的数据库名。

pswd = con.pass

pass 方法返回连接的密码。

$ ./password_authentication.rb 
User: janbodnar
Database name: testdb
Password: pswd37

该程序打印数据库用户、数据库名和使用的密码。

创建数据库表

在本节中，我们创建一个数据库表并用数据填充它。

create_table.rb

#!/usr/bin/ruby

require 'pg'

begin

    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    
    con.exec "DROP TABLE IF EXISTS Cars"
    con.exec "CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
        Name VARCHAR(20), Price INT)"
    con.exec "INSERT INTO Cars VALUES(1,'Audi',52642)"
    con.exec "INSERT INTO Cars VALUES(2,'Mercedes',57127)"
    con.exec "INSERT INTO Cars VALUES(3,'Skoda',9000)"
    con.exec "INSERT INTO Cars VALUES(4,'Volvo',29000)"
    con.exec "INSERT INTO Cars VALUES(5,'Bentley',350000)"
    con.exec "INSERT INTO Cars VALUES(6,'Citroen',21000)"
    con.exec "INSERT INTO Cars VALUES(7,'Hummer',41400)"
    con.exec "INSERT INTO Cars VALUES(8,'Volkswagen',21600)"
    
rescue PG::Error => e

    puts e.message 
    
ensure

    con.close if con
    
end

创建的表名为 Cars，它有三列：ID、汽车名称和价格。

con.exec "DROP TABLE IF EXISTS Cars"

exec 方法向服务器提交一个 SQL 命令并等待结果。我们的 SQL 命令如果表已经存在则删除它。

$ ./create_table.rb
$ psql testdb
psql (9.3.9)
Type "help" for help.

testdb=> SELECT * FROM Cars;
 id |    name    | price  
----+------------+--------
  1 | Audi       |  52642
  2 | Mercedes   |  57127
  3 | Skoda      |   9000
  4 | Volvo      |  29000
  5 | Bentley    | 350000
  6 | Citroen    |  21000
  7 | Hummer     |  41400
  8 | Volkswagen |  21600
(8 rows)

我们执行程序并使用 psql 工具验证创建的表。

简单查询

在本节中，我们执行一个简单的查询命令。

query_version.rb

#!/usr/bin/ruby

require 'pg'

begin

    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'

    rs = con.exec 'SELECT VERSION()'
    puts rs.getvalue 0, 0
    
rescue PG::Error => e

    puts e.message 
    
ensure

    con.close if con
    
end

该示例获取数据库服务器的版本。

rs = con.exec 'SELECT VERSION()'

SELECT VERSION SQL 语句检索数据库的版本。

puts rs.getvalue 0, 0

getvalue 方法返回返回结果集的一行中的单个字段值。

$ ./query_version.rb 
PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04) 4.8.4, 64-bit

该程序打印此输出。

检索多行数据

以下示例执行一个返回多行数据的查询。

multiple_rows.rb

#!/usr/bin/ruby

require 'pg'

begin

    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    
    rs = con.exec "SELECT * FROM Cars LIMIT 5"

    rs.each do |row|
      puts "%s %s %s" % [ row['id'], row['name'], row['price'] ]
    end
    
rescue PG::Error => e

    puts e.message 
    
ensure

    rs.clear if rs
    con.close if con
    
end

该程序打印 Cars 表的前五行数据。

rs = con.exec "SELECT * FROM Cars LIMIT 5"

此 SQL 查询返回五行数据。

rs.each do |row|
    puts "%s %s %s" % [ row['id'], row['name'], row['price'] ]
end

使用 each 方法，我们遍历结果集并打印一行中的字段。

$ ./multiple_rows.rb 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000

这是 multiple_rows.rb 程序的输出。

预处理语句

预处理语句可以防止 SQL 注入并提高性能。使用预处理语句时，我们使用占位符而不是直接将值写入语句。

prepared_statement.rb

#!/usr/bin/ruby

require 'pg'

if ARGV.length != 1 then
    puts "Usage: prepared_statement.rb rowId"
    exit
end

rowId = ARGV[0]

begin
  
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    
    con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1"
    rs = con.exec_prepared 'stm1', [rowId]
        
    puts rs.values 
    
rescue PG::Error => e

    puts e.message 
    
ensure

    rs.clear if rs
    con.close if con
    
end

该程序将行 ID 作为其参数。它获取指定行的数据并打印出来。由于程序从用户那里获取一个不可信的值，因此有必要使用预处理语句。

rowId = ARGV[0]

命令行参数存储在 rowId 变量中。

con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1"

prepare 方法准备一个带有给定名称的 SQL 语句，以便稍后执行。我们的 SQL 语句返回 Cars 表的一行。$1 是一个占位符，稍后将填充实际值。

rs = con.exec_prepared 'stm1', [rowId]

exec_prepared 方法执行由语句名称指定的预处理命名语句。第二个参数是 SQL 查询的绑定参数数组。

puts rs.values

values 方法打印行的字段值。

$ ./prepared_statement.rb 4
4
Volvo
29000

这是示例的输出。

以下示例显示了创建预处理语句的另一种方法。

prepared_statement2.rb

#!/usr/bin/ruby

require 'pg'

begin
  
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    
    stm = "SELECT $1::int AS a, $2::int AS b, $3::int AS c"
    rs = con.exec_params(stm, [1, 2, 3])
        
    puts rs.values 
    
rescue PG::Error => e

    puts e.message 
    
ensure

    rs.clear if rs
    con.close if con
    
end

该示例使用 exec_params 创建和执行预处理语句。

stm = "SELECT $1::int AS a, $2::int AS b, $3::int AS c"

在语句中，我们将预期参数的数据类型附加到占位符。

rs = con.exec_params(stm, [1, 2, 3])

exec_params 方法使用参数占位符向数据库发送 SQL 查询请求。

$ ./prepared_statement2.rb 
1
2
3

这是示例的输出。

元数据

元数据是关于数据库中数据的信息。以下属于元数据：关于我们存储数据的表和列的信息，受 SQL 语句影响的行数，或结果集中返回的行数和列数。

列标题

在第一个示例中，我们打印列标题。

column_headers.rb

#!/usr/bin/ruby

require 'pg'

begin

    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    
    rs = con.exec 'SELECT * FROM Cars WHERE Id=0'
    puts 'There are %d columns ' % rs.nfields
    puts 'The column names are:'
    puts rs.fields
    
rescue PG::Error => e

    puts e.message 
    
ensure

    rs.clear if rs
    con.close if con
    
end

该示例打印可用列的数量及其名称到控制台。

rs = con.exec 'SELECT * FROM Cars WHERE Id=0'

在 SQL 语句中，我们选择一行中的所有列。

puts "There are %d columns " % rs.nfields

nfields 方法返回查询结果行中的列数。

puts rs.fields

fields 方法返回一个字符串数组，表示结果中字段的名称。

$ ./column_headers.rb 
There are 3 columns 
The column names are:
id
name
price

这是示例的输出。

列出表

PostgreSQL 的信息模式由一组视图组成，这些视图包含有关当前数据库中定义的对象的信息。tables 视图包含当前数据库中定义的所有表和视图。

list_tables.rb

#!/usr/bin/ruby

require 'pg'

begin

    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    
    rs = con.exec "SELECT table_name FROM information_schema.tables 
        WHERE table_schema = 'public'"
        
    rs.each do |row|
        puts row['table_name']
    end
    
rescue PG::Error => e

    puts e.message 
    
ensure

    rs.clear if rs
    con.close if con
    
end

该示例打印 testdb 数据库中的所有表。

rs = con.exec "SELECT table_name FROM information_schema.tables 
    WHERE table_schema = 'public'"

此 SQL 语句从当前数据库中选择所有表。

rs.each do |row|
    puts row['table_name']
end

这些表被打印到控制台。

$ ./list_tables.rb 
authors
books
cars

list_tables.rb 程序打印 testdb 数据库中的可用表。

事务

事务是针对一个或多个数据库中数据的原子数据库操作单元。事务中的 SQL 语句可以全部提交到数据库，也可以全部回滚。SQL 语句被放入事务中以确保数据安全性和完整性。

PostgreSQL 在自动提交模式下运行。每个 SQL 语句都在一个事务中执行：每个单独的语句都有一个隐式的 BEGIN 和（如果成功）COMMIT 包裹着它。

显式事务以 BEGIN 命令开始，并以 COMMIT 或 ROLLBACK 命令结束。

transaction.rb

#!/usr/bin/ruby

require 'pg'

begin

    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    
    con.transaction do |con|
        
        con.exec "UPDATE Cars SET Price=23700 WHERE Id=8"
        con.exec "INSERT INTO Car VALUES(9,'Mazda',27770)"
    
    end
    
rescue PG::Error => e

    puts e.message 
    
ensure

    con.close if con
    
end

在此示例中，我们更新一辆汽车的价格并插入一辆新汽车。这两个操作包含在一个事务中。这意味着这两个操作要么都执行，要么都不执行。

con.transaction do |con|
    
    con.exec "UPDATE Cars SET Price=23700 WHERE Id=8"
    con.exec "INSERT INTO Car VALUES(9,'Mazda',27770)"

end

transaction 方法在单个事务中运行块内的代码。它在块开始时执行 BEGIN，在块结束时执行 COMMIT，或者在发生任何异常时执行 ROLLBACK。

这是 PostgreSQL Ruby 教程。您可能还对 ZetCode 上的 SQLite Ruby 教程、MySQL Ruby 教程、PostgreSQL Python 教程、MongoDB Ruby 教程或 PostgreSQL PHP 教程感兴趣。

电子书

PostgreSQL Ruby

PostgreSQL

Ruby

Ruby pg

安装

启动和停止 PostgreSQL

创建用户和数据库

libpq 库

服务器版本

使用密码进行身份验证

创建数据库表

简单查询

检索多行数据

预处理语句

元数据

列标题

列出表

事务