原文： http://zetcode.com/db/postgresqlruby/

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

PostgreSQL

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

PostgreSQL 具有复杂的功能，例如多版本并发控制（MVCC），时间点恢复，表空间，异步复制，嵌套事务（保存点），联机/热备份，复杂的查询计划程序/优化程序以及用于容错的预写日志记录 。 它支持国际字符集，多字节字符编码，Unicode，并且对于排序，区分大小写和格式具有区域设置意识。

Ruby

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

Ruby pg

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

安装

我们将安装 PostgreSQL 数据库和其他必要的库。

  1. $ sudo apt-get install postgresql

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

  1. $ sudo update-rc.d -f postgresql remove
  2.  Removing any system startup links for /etc/init.d/postgresql ...
  3.    /etc/rc0.d/K21postgresql
  4.    /etc/rc1.d/K21postgresql
  5.    /etc/rc2.d/S19postgresql
  6.    /etc/rc3.d/S19postgresql
  7.    /etc/rc4.d/S19postgresql
  8.    /etc/rc5.d/S19postgresql
  9.    /etc/rc6.d/K21postgresql

如果我们从包中安装 PostgreSQL 数据库，它将自动添加到操作系统的启动脚本中。 如果我们仅学习使用数据库，则不必在每次引导系统时都启动数据库。 上面的命令删除 PostgreSQL 数据库的所有系统启动链接。

  1. $ sudo apt-get install libpq-dev

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

  1. $ sudo -u postgres psql postgres
  2. psql (9.3.9)
  3. Type "help" for help.
  5. postgres=# \password postgres

我们为postgres用户设置了密码。

  1. $ sudo apt-get install ruby-dev

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

  1. $ sudo gem install pg

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

启动和停止 PostgreSQL

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

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

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

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

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

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

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

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

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

创建用户和数据库

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

  1. $ sudo -u postgres createuser janbodnar

我们在 PostgreSQL 系统中创建了一个新角色。 我们允许它具有创建新数据库的能力。 角色是数据库世界中的用户。 角色与操作系统用户是分开的。

  1. $ sudo -u postgres psql postgres
  2. psql (9.3.9)
  3. Type "help" for help.
  5. postgres=# ALTER USER janbodnar WITH password 'pswd37';
  6. ALTER ROLE
  7. postgres=# \q

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

PostgreSQL 通常在本地连接上使用信任或对等认证策略。 在信任认证策略的情况下，PostgreSQL 假定可以连接到服务器的任何人都可以使用他们指定的任何数据库用户名（甚至超级用户名）访问数据库。 与数据库建立连接时，不需要密码。 （在数据库和用户列中所做的限制仍然适用。）信任认证对于单用户工作站上的本地连接是适当的，并且非常方便。 它通常不适用于多用户计算机。 如果使用对等认证策略，则数据库用户名必须与操作系统用户名匹配。

  1. $ 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

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. puts 'Version of libpg: ' + PG.library_version.to_s

该程序将打印libpq库的版本。

  1. require 'pg'

我们包括pg模块。

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

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

  1. $ ./lib_version.rb 
  2. Version of libpg: 90309

该库的版本是 9.3.9。

服务器版本

在下面的示例中，我们找到了 PostgreSQL 数据库的版本。

server_version.rb

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. begin
  7.     con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  8.     puts con.server_version
  10. rescue PG::Error => e
  12.     puts e.message 
  14. ensure
  16.     con.close if con
  18. end

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

  1. ...
  2. # TYPE  DATABASE        USER            ADDRESS                 METHOD
  4. # "local" is for Unix domain socket connections only
  5. local   all             all                                     peer
  6. ...

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

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

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

  1. rescue PG::Error => e
  3.     puts e.message

我们检查错误。 这很重要，因为使用数据库容易出错。

  1. ensure
  3.     con.close if con
  5. end

最后，我们释放资源。

  1. $ ./server_version.rb 
  2. 90309

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

密码认证

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

  1. $ sudo service postgresql restart

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

password_authentication.rb

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. begin
  7.     con = PG.connect :dbname => 'testdb', :user => 'janbodnar', 
  8.         :password => 'pswd37'
  10.     user = con.user
  11.     db_name = con.db
  12.     pswd = con.pass
  14.     puts "User: #{user}"
  15.     puts "Database name: #{db_name}"
  16.     puts "Password: #{pswd}" 
  18. rescue PG::Error => e
  20.     puts e.message 
  22. ensure
  24.     con.close if con
  26. end

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

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

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

  1. user = con.user

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

  1. db_name = con.db

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

  1. pswd = con.pass

pass方法返回连接的密码。

  1. $ ./password_authentication.rb 
  2. User: janbodnar
  3. Database name: testdb
  4. Password: pswd37

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

创建数据库表

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

create_table.rb

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. begin
  7.     con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  9.     con.exec "DROP TABLE IF EXISTS Cars"
  10.     con.exec "CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
  11.         Name VARCHAR(20), Price INT)"
  12.     con.exec "INSERT INTO Cars VALUES(1,'Audi',52642)"
  13.     con.exec "INSERT INTO Cars VALUES(2,'Mercedes',57127)"
  14.     con.exec "INSERT INTO Cars VALUES(3,'Skoda',9000)"
  15.     con.exec "INSERT INTO Cars VALUES(4,'Volvo',29000)"
  16.     con.exec "INSERT INTO Cars VALUES(5,'Bentley',350000)"
  17.     con.exec "INSERT INTO Cars VALUES(6,'Citroen',21000)"
  18.     con.exec "INSERT INTO Cars VALUES(7,'Hummer',41400)"
  19.     con.exec "INSERT INTO Cars VALUES(8,'Volkswagen',21600)"
  21. rescue PG::Error => e
  23.     puts e.message 
  25. ensure
  27.     con.close if con
  29. end

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

  1. con.exec "DROP TABLE IF EXISTS Cars"

exec方法将 SQL 命令提交到服务器并等待结果。 如果表已经存在，我们的 SQL 命令将删除该表。

  1. $ ./create_table.rb
  2. $ psql testdb
  3. psql (9.3.9)
  4. Type "help" for help.
  6. testdb=> SELECT * FROM Cars;
  7.  id |    name    | price  
  8. ----+------------+--------
  9.   1 | Audi       |  52642
  10.   2 | Mercedes   |  57127
  11.   3 | Skoda      |   9000
  12.   4 | Volvo      |  29000
  13.   5 | Bentley    | 350000
  14.   6 | Citroen    |  21000
  15.   7 | Hummer     |  41400
  16.   8 | Volkswagen |  21600
  17. (8 rows)

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

简单查询

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

query_version.rb

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. begin
  7.     con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  9.     rs = con.exec 'SELECT VERSION()'
  10.     puts rs.getvalue 0, 0
  12. rescue PG::Error => e
  14.     puts e.message 
  16. ensure
  18.     con.close if con
  20. end

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

  1. rs = con.exec 'SELECT VERSION()'

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

  1. puts rs.getvalue 0, 0

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

  1. $ ./query_version.rb 
  2. 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

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. begin
  7.     con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  9.     rs = con.exec "SELECT * FROM Cars LIMIT 5"
  11.     rs.each do |row|
  12.       puts "%s %s %s" % [ row['id'], row['name'], row['price'] ]
  13.     end
  15. rescue PG::Error => e
  17.     puts e.message 
  19. ensure
  21.     rs.clear if rs
  22.     con.close if con
  24. end

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

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

此 SQL 查询返回五行数据。

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

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

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

这是multiple_rows.rb程序的输出。

预备语句

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

prepared_statement.rb

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. if ARGV.length != 1 then
  6.     puts "Usage: prepared_statement.rb rowId"
  7.     exit
  8. end
  10. rowId = ARGV[0]
  12. begin
  14.     con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  16.     con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1"
  17.     rs = con.exec_prepared 'stm1', [rowId]
  19.     puts rs.values 
  21. rescue PG::Error => e
  23.     puts e.message 
  25. ensure
  27.     rs.clear if rs
  28.     con.close if con
  30. end

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

  1. rowId = ARGV[0]

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

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

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

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

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

  1. puts rs.values

values方法打印行的字段值。

  1. $ ./prepared_statement.rb 4
  2. 4
  3. Volvo
  4. 29000

这是示例的输出。

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

prepared_statement2.rb

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. begin
  7.     con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  9.     stm = "SELECT $1::int AS a, $2::int AS b, $3::int AS c"
  10.     rs = con.exec_params(stm, [1, 2, 3])
  12.     puts rs.values 
  14. rescue PG::Error => e
  16.     puts e.message 
  18. ensure
  20.     rs.clear if rs
  21.     con.close if con
  23. end

该示例使用exec_params创建并执行预备语句。

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

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

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

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

  1. $ ./prepared_statement2.rb 
  2. 1
  3. 2
  4. 3

这是示例的输出。

元数据

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

列标题

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

column_headers.rb

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. begin
  7.     con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  9.     rs = con.exec 'SELECT * FROM Cars WHERE Id=0'
  10.     puts 'There are %d columns ' % rs.nfields
  11.     puts 'The column names are:'
  12.     puts rs.fields
  14. rescue PG::Error => e
  16.     puts e.message 
  18. ensure
  20.     rs.clear if rs
  21.     con.close if con
  23. end

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

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

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

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

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

  1. puts rs.fields

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

  1. $ ./column_headers.rb 
  2. There are 3 columns 
  3. The column names are:
  4. id
  5. name
  6. price

这是示例的输出。

表的清单

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

list_tables.rb

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. begin
  7.     con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  9.     rs = con.exec "SELECT table_name FROM information_schema.tables 
  10.         WHERE table_schema = 'public'"
  12.     rs.each do |row|
  13.         puts row['table_name']
  14.     end
  16. rescue PG::Error => e
  18.     puts e.message 
  20. ensure
  22.     rs.clear if rs
  23.     con.close if con
  25. end

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

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

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

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

表将打印到控制台。

  1. $ ./list_tables.rb 
  2. authors
  3. books
  4. cars

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

事务

事务是针对一个或多个数据库中数据的数据库操作的基本单位。 事务中的 SQL 语句可以全部提交给数据库，也可以全部回滚。 为了数据安全和完整性，将 SQL 语句放入事务中。

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

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

transaction.rb

  1. #!/usr/bin/ruby
  3. require 'pg'
  5. begin
  7.     con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  9.     con.transaction do |con|
  11.         con.exec "UPDATE Cars SET Price=23700 WHERE Id=8"
  12.         con.exec "INSERT INTO Car VALUES(9,'Mazda',27770)"
  14.     end
  16. rescue PG::Error => e
  18.     puts e.message 
  20. ensure
  22.     con.close if con
  24. end

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

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

transaction方法在单个事务中在块内运行代码。 它在块的开头执行BEGIN，在块的末尾执行COMMIT，如果发生任何异常，则执行ROLLBACK。

Tweet

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