PostgreSQL Ruby 教程 · ZetCode 中文系列教程

# PostgreSQL Ruby 教程 > 原文： [http://zetcode.com/db/postgresqlruby/](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 数据库和其他必要的库。 ```ruby $ sudo apt-get install postgresql ``` 在基于 Debian 的系统上，我们可以使用上述命令从包中安装 PostgreSQL 数据库。 ```ruby $ 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 数据库的所有系统启动链接。 ```ruby $ sudo apt-get install libpq-dev ``` 要编译 Ruby `pg`模块，我们还需要 C `libpq`库的开发文件。 ```ruby $ sudo -u postgres psql postgres psql (9.3.9) Type "help" for help. postgres=# \password postgres ``` 我们为`postgres`用户设置了密码。 ```ruby $ sudo apt-get install ruby-dev ``` 我们安装了 Ruby 开发库，这是编译 Ruby 扩展模块所必需的。 ```ruby $ sudo gem install pg ``` 我们安装了 Ruby `pg`模块，它是 PostgreSQL 数据库的 Ruby 接口。 ## 启动和停止 PostgreSQL 在下一节中，我们将展示如何启动 PostgreSQL 数据库，停止它以及查询它的状态。 ```ruby $ sudo service postgresql start * Starting PostgreSQL 9.3 database server [ OK ] ``` 在基于 Debian 的 Linux 上，我们可以使用`service postgresql start`命令启动服务器。 ```ruby $ sudo service postgresql status 9.3/main (port 5432): online ``` 我们使用`service postgresql status`命令检查 PostgreSQL 是否正在运行。 ```ruby $ sudo service postgresql stop * Stopping PostgreSQL 9.3 database server [ OK ] ``` 我们使用`service postgresql stop`命令停止 PostgreSQL。 ```ruby $ service postgresql status 9.3/main (port 5432): down ``` 此时，`service postgresql status`命令报告 PostgreSQL 数据库已关闭。 ## 创建用户和数据库在以下步骤中，我们将创建一个新的数据库用户和数据库。 ```ruby $ sudo -u postgres createuser janbodnar ``` 我们在 PostgreSQL 系统中创建了一个新角色。我们允许它具有创建新数据库的能力。角色是数据库世界中的用户。角色与操作系统用户是分开的。 ```ruby $ 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 假定可以连接到服务器的任何人都可以使用他们指定的任何数据库用户名（甚至超级用户名）访问数据库。与数据库建立连接时，不需要密码。（在数据库和用户列中所做的限制仍然适用。）信任认证对于单用户工作站上的本地连接是适当的，并且非常方便。它通常不适用于多用户计算机。如果使用对等认证策略，则数据库用户名必须与操作系统用户名匹配。 ```ruby $ 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 ```ruby #!/usr/bin/ruby require 'pg' puts 'Version of libpg: ' + PG.library_version.to_s ``` 该程序将打印`libpq`库的版本。 ```ruby require 'pg' ``` 我们包括`pg`模块。 ```ruby puts 'Version of libpg: ' + PG.library_version.to_s ``` `library_version`方法返回正在使用的`libpq`的版本。 ```ruby $ ./lib_version.rb Version of libpg: 90309 ``` 该库的版本是 9.3.9。 ## 服务器版本在下面的示例中，我们找到了 PostgreSQL 数据库的版本。 server_version.rb ```ruby #!/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`方法，打印版本，关闭连接，然后清除。 ```ruby ... # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer ... ``` 在`pg_hba.conf`中，我们具有`peer`默认认证方法。在这种方法中，数据库用户名必须与操作系统用户名匹配。无需密码即可建立连接。 ```ruby con = PG.connect :dbname => 'testdb', :user => 'janbodnar' ``` 使用`connect`方法，我们连接到数据库。在连接字符串中，我们提供用户名和数据库名。 ```ruby rescue PG::Error => e puts e.message ``` 我们检查错误。这很重要，因为使用数据库容易出错。 ```ruby ensure con.close if con end ``` 最后，我们释放资源。 ```ruby $ ./server_version.rb 90309 ``` 运行程序，我们获得数据库服务器版本。 ## 密码认证接下来，我们将使用密码对数据库服务器进行认证。在本教程的所有其他示例中，我们假设使用`peer`或`trust`认证模式。我们将`pg_hba.conf`文件中本地连接的认证类型更改为`md5`。 ```ruby $ sudo service postgresql restart ``` 要应用更改，必须重新启动数据库服务器。 password_authentication.rb ```ruby #!/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 ``` 在示例中，我们使用密码连接到数据库。我们打印用户名，数据库名称和当前数据库连接的密码。 ```ruby con = PG.connect :dbname => 'testdb', :user => 'janbodnar', :password => 'pswd37' ``` 在连接字符串中，我们添加了密码选项。 ```ruby user = con.user ``` `user`方法返回连接的用户名。 ```ruby db_name = con.db ``` `db`方法返回连接的数据库名称。 ```ruby pswd = con.pass ``` `pass`方法返回连接的密码。 ```ruby $ ./password_authentication.rb User: janbodnar Database name: testdb Password: pswd37 ``` 该程序将打印数据库用户，数据库名称和使用的密码。 ## 创建数据库表在本节中，我们将创建一个数据库表并用数据填充它。 create_table.rb ```ruby #!/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，汽车名称及其价格。 ```ruby con.exec "DROP TABLE IF EXISTS Cars" ``` `exec`方法将 SQL 命令提交到服务器并等待结果。如果表已经存在，我们的 SQL 命令将删除该表。 ```ruby $ ./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 ```ruby #!/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 ``` 该示例获取数据库服务器的版本。 ```ruby rs = con.exec 'SELECT VERSION()' ``` `SELECT VERSION()` SQL 语句检索数据库的版本。 ```ruby puts rs.getvalue 0, 0 ``` `getvalue`方法返回返回结果集的一行的单个字段值。 ```ruby $ ./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 ```ruby #!/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`表的前五行的数据。 ```ruby rs = con.exec "SELECT * FROM Cars LIMIT 5" ``` 此 SQL 查询返回五行数据。 ```ruby rs.each do |row| puts "%s %s %s" % [ row['id'], row['name'], row['price'] ] end ``` 使用`each`方法，我们遍历结果集并打印一行的杂项。 ```ruby $ ./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 ```ruby #!/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 作为其参数。它获取指定行的数据并进行打印。由于程序从用户那里获取了一个不可信任的值，因此有必要使用预备语句。 ```ruby rowId = ARGV[0] ``` 命令行参数存储在`rowId`变量中。 ```ruby con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1" ``` `prepare`方法准备一个具有给定名称的 SQL 语句，以便稍后执行。我们的 SQL 语句返回`Cars`表的一行。 `$1`是一个占位符，稍后会填充一个实际值。 ```ruby rs = con.exec_prepared 'stm1', [rowId] ``` `exec_prepared`方法执行由语句名称指定的预备命名语句。第二个参数是 SQL 查询的绑定参数数组。 ```ruby puts rs.values ``` `values`方法打印行的字段值。 ```ruby $ ./prepared_statement.rb 4 4 Volvo 29000 ``` 这是示例的输出。以下示例显示了另一种创建预备语句的方法。 prepared_statement2.rb ```ruby #!/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`创建并执行预备语句。 ```ruby stm = "SELECT $1::int AS a, $2::int AS b, $3::int AS c" ``` 在语句中，我们将预期参数的数据类型附加到占位符。 ```ruby rs = con.exec_params(stm, [1, 2, 3]) ``` `exec_params`方法使用占位符作为参数将 SQL 查询请求发送到数据库。 ```ruby $ ./prepared_statement2.rb 1 2 3 ``` 这是示例的输出。 ## 元数据元数据是有关数据库中数据的信息。以下内容属于元数据：有关存储数据的表和列的信息，受 SQL 语句影响的行数或结果集中返回的行和列数。 ### 列标题在第一个示例中，我们打印列标题。 column_headers.rb ```ruby #!/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 ``` 该示例将可用列的数量及其名称打印到控制台。 ```ruby rs = con.exec 'SELECT * FROM Cars WHERE Id=0' ``` 在 SQL 语句中，我们选择一行的所有列。 ```ruby puts "There are %d columns " % rs.nfields ``` `nfields`方法返回查询结果行中的列数。 ```ruby puts rs.fields ``` `fields`方法返回一个字符串数组，该字符串表示结果中字段的名称。 ```ruby $ ./column_headers.rb There are 3 columns The column names are: id name price ``` 这是示例的输出。 ### 表的清单 PostgreSQL 的信息模式由一组视图组成，这些视图包含有关当前数据库中定义的对象的信息。 `tables`视图包含当前数据库中定义的所有表和视图。 list_tables.rb ```ruby #!/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`数据库中的所有表。 ```ruby rs = con.exec "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'" ``` 该 SQL 语句从当前数据库中选择所有表。 ```ruby rs.each do |row| puts row['table_name'] end ``` 表将打印到控制台。 ```ruby $ ./list_tables.rb authors books cars ``` `list_tables.rb`程序在`testdb`数据库中打印可用表。 ## 事务事务是针对一个或多个数据库中数据的数据库操作的基本单位。事务中的 SQL 语句可以全部提交给数据库，也可以全部回滚。为了数据安全和完整性，将 SQL 语句放入事务中。 PostgreSQL 在自动提交模式下运行。每个 SQL 语句都在一个事务中执行：每个单独的语句周围都包含一个隐式`BEGIN`和（如果成功）`COMMIT`。显式事务以`BEGIN`命令开始，以`COMMIT`或`ROLLBACK`命令结束。 transaction.rb ```ruby #!/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 ``` 在示例中，我们更新了汽车的价格并插入了新汽车。这两个操作包含在单个事务中。这意味着要么执行两个操作，要么不执行任何操作。 ```ruby 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`。 [Tweet](https://twitter.com/share) 这是 PostgreSQL Ruby 教程。您可能也对 [SQLite Ruby 教程](/db/sqliteruby/)， [MySQL Ruby 教程](/db/mysqlrubytutorial/)， [PostgreSQL Python 教程](/db/postgresqlpythontutorial/)， [MongoDB Ruby 教程](/db/mongodbruby/)感兴趣。或 ZetCode 上的 [PostgreSQL PHP 教程](/db/postgresqlphp/)。