Ruby 连接 PostgreSQL 数据库教程

安装必要的库

在使用 Ruby 连接 PostgreSQL 数据库之前，首先需要安装 pg 库。pg 库是 Ruby 与 PostgreSQL 进行交互的核心库，它提供了一系列方法来连接数据库、执行 SQL 语句等操作。

在 Ruby 项目的 Gemfile 文件中添加以下内容：

gem 'pg'

然后在终端中运行 bundle install 命令来安装 pg 库。如果你不使用 bundler，也可以直接通过 gem install pg 命令来安装。

建立数据库连接

安装好 pg 库后，就可以在 Ruby 代码中建立与 PostgreSQL 数据库的连接了。以下是一个简单的示例：

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )
  puts "Connected to the database successfully!"
  conn.close
rescue PG::Error => e
  puts "Connection error: #{e.message}"
end

在上述代码中，使用 PG.connect 方法来建立连接。其中：

• host 表示数据库服务器的主机地址，如果是本地运行，一般为 localhost。
• port 是 PostgreSQL 数据库的端口号，默认是 5432。
• user 和 password 分别是数据库的用户名和密码。
• dbname 是要连接的数据库名称。

通过 begin...rescue 块来捕获可能出现的连接错误，并打印错误信息。

执行简单的 SQL 查询

连接建立成功后，就可以执行 SQL 查询了。例如，查询一个简单的表中的所有数据：

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )

  result = conn.exec('SELECT * FROM your_table')
  result.each do |row|
    puts row.values.join(', ')
  end

  conn.close
rescue PG::Error => e
  puts "Error: #{e.message}"
end

在上述代码中，使用 conn.exec 方法来执行 SQL 查询。exec 方法会返回一个 PG::Result 对象，该对象包含了查询结果。通过 each 方法遍历结果集，并打印每一行的数据。

插入数据

向数据库中插入数据也是常见的操作。以下是一个插入单条数据的示例：

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )

  name = 'John Doe'
  age = 30
  conn.exec_params("INSERT INTO your_table (name, age) VALUES ($1, $2)", [name, age])
  puts "Data inserted successfully!"

  conn.close
rescue PG::Error => e
  puts "Error: #{e.message}"
end

这里使用 conn.exec_params 方法来执行插入操作。exec_params 方法的第一个参数是 SQL 语句，其中使用 $1、$2 等占位符；第二个参数是一个数组，包含了占位符对应的值。这种方式可以有效防止 SQL 注入攻击。

更新数据

更新数据库中的数据同样重要。以下是一个更新数据的示例：

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )

  new_age = 31
  name = 'John Doe'
  conn.exec_params("UPDATE your_table SET age = $1 WHERE name = $2", [new_age, name])
  puts "Data updated successfully!"

  conn.close
rescue PG::Error => e
  puts "Error: #{e.message}"
end

在这个示例中，使用 UPDATE 语句结合 exec_params 方法来更新符合条件的数据。

删除数据

删除数据的操作如下：

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )

  name = 'John Doe'
  conn.exec_params("DELETE FROM your_table WHERE name = $1", [name])
  puts "Data deleted successfully!"

  conn.close
rescue PG::Error => e
  puts "Error: #{e.message}"
end

通过 DELETE 语句和 exec_params 方法来删除符合条件的数据。

事务处理

在数据库操作中，事务处理非常关键，它可以确保一组操作要么全部成功，要么全部失败。以下是一个使用事务的示例：

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )

  conn.transaction do
    # 插入第一条数据
    name1 = 'Alice'
    age1 = 25
    conn.exec_params("INSERT INTO your_table (name, age) VALUES ($1, $2)", [name1, age1])

    # 插入第二条数据，这里故意制造一个错误，比如字段类型不匹配
    name2 = 'Bob'
    age2 = 'twenty - five'
    conn.exec_params("INSERT INTO your_table (name, age) VALUES ($1, $2)", [name2, age2])
  end

  puts "Transaction completed successfully!"
rescue PG::Error => e
  puts "Transaction error: #{e.message}"
ensure
  conn.close if conn
end

在上述代码中，使用 conn.transaction 块来定义一个事务。在事务块中进行多个数据库操作。如果其中任何一个操作失败，整个事务会回滚，之前的操作都不会生效。

处理查询结果

PG::Result 对象提供了多种方法来处理查询结果。除了之前示例中使用的 each 方法遍历结果集，还可以使用以下方法：

• first：返回结果集中的第一行数据。

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )

  result = conn.exec('SELECT * FROM your_table')
  first_row = result.first
  puts first_row.values.join(', ') if first_row

  conn.close
rescue PG::Error => e
  puts "Error: #{e.message}"
end

• to_a：将结果集转换为数组，数组中的每个元素是一行数据的哈希表示。

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )

  result = conn.exec('SELECT * FROM your_table')
  rows = result.to_a
  rows.each do |row|
    puts row.inspect
  end

  conn.close
rescue PG::Error => e
  puts "Error: #{e.message}"
end

连接池的使用

在实际应用中，特别是在高并发的情况下，使用连接池可以提高性能和资源利用率。pg 库提供了简单的连接池实现。以下是一个使用连接池的示例：

require 'pg'
require 'pg/pool'

pool = PG::ConnectionPool.new(
  size: 5,
  host: 'localhost',
  port: 5432,
  user: 'your_username',
  password: 'your_password',
  dbname: 'your_database'
)

pool.with_connection do |conn|
  result = conn.exec('SELECT * FROM your_table')
  result.each do |row|
    puts row.values.join(', ')
  end
end

pool.close

在上述代码中，通过 PG::ConnectionPool.new 创建一个连接池，size 参数指定了连接池的大小。使用 with_connection 方法从连接池中获取一个连接来执行数据库操作，操作完成后连接会自动返回连接池。

处理大查询结果

当查询结果集非常大时，一次性加载到内存中可能会导致内存问题。pg 库提供了流式处理结果的方式。以下是一个示例：

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )

  conn.async_exec('SELECT * FROM very_large_table') do |result|
    result.each do |row|
      # 处理每一行数据，例如写入文件等操作
      puts row.values.join(', ')
    end
  end

  loop do
    break unless conn.wait_for_notify
    # 处理通知
  end

  conn.finish
rescue PG::Error => e
  puts "Error: #{e.message}"
end

在这个示例中，使用 async_exec 方法异步执行查询，并通过块来处理每一行结果。同时，通过 wait_for_notify 方法来处理可能的通知。

数据库元数据操作

获取数据库的元数据信息也是很有用的。例如，获取表的结构信息：

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )

  schema = conn.schema
  table_name = 'your_table'
  columns = schema[table_name]
  columns.each do |column|
    puts "Column: #{column.name}, Type: #{column.type}"
  end

  conn.close
rescue PG::Error => e
  puts "Error: #{e.message}"
end

在上述代码中，通过 conn.schema 获取数据库的模式信息，然后根据表名获取表的列信息，并打印列名和类型。

高级 SQL 特性的使用

除了基本的 SELECT、INSERT、UPDATE 和 DELETE 操作，PostgreSQL 还支持许多高级特性，如窗口函数、递归查询等。以下是一个使用窗口函数的示例：

require 'pg'

begin
  conn = PG.connect(
    host: 'localhost',
    port: 5432,
    user: 'your_username',
    password: 'your_password',
    dbname: 'your_database'
  )

  result = conn.exec(<<-SQL
    SELECT name, age,
           RANK() OVER (ORDER BY age DESC) as age_rank
    FROM your_table
  SQL
  )
  result.each do |row|
    puts "#{row['name']}, #{row['age']}, Rank: #{row['age_rank']}"
  end

  conn.close
rescue PG::Error => e
  puts "Error: #{e.message}"
end

在这个示例中，使用 RANK() 窗口函数根据年龄对数据进行排名，并在 Ruby 中处理查询结果。

与 Rails 框架结合使用

如果在 Rails 项目中使用 PostgreSQL，Rails 已经对数据库连接和操作进行了高度封装。在 config/database.yml 文件中配置数据库连接信息：

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  host: localhost
  username: your_username
  password: your_password
  database: your_database

development:
  <<: *default

test:
  <<: *default
  database: your_database_test

production:
  <<: *default
  url: <%= ENV['DATABASE_URL'] %>

然后在 Rails 模型中，可以通过 ActiveRecord 进行数据库操作。例如，在一个 User 模型中：

class User < ApplicationRecord
end

可以通过以下方式进行查询：

users = User.all
users.each do |user|
  puts user.name
end

插入数据：

user = User.new(name: 'Jane Doe', age: 28)
user.save

更新数据：

user = User.find_by(name: 'Jane Doe')
user.age = 29
user.save

删除数据：

user = User.find_by(name: 'Jane Doe')
user.destroy

Rails 的 ActiveRecord 提供了丰富的方法来简化数据库操作，同时也支持事务处理、连接池等功能。

常见问题及解决方法

1. 连接超时：
   • 原因：可能是数据库服务器未启动、网络问题或配置的连接参数错误。
   • 解决方法：确保数据库服务器已启动并运行，检查网络连接是否正常，仔细核对 PG.connect 中的连接参数，如 host、port、user、password 和 dbname。
2. SQL 注入问题：
   • 原因：在构建 SQL 语句时，直接拼接用户输入的数据。
   • 解决方法：始终使用 exec_params 方法，通过占位符传递参数，避免直接拼接用户输入。
3. 数据类型不匹配：
   • 原因：在插入或更新数据时，提供的数据类型与数据库表定义的类型不一致。
   • 解决方法：仔细检查数据库表结构，确保插入或更新的数据类型正确。例如，如果表中年龄字段定义为整数类型，不要传入字符串类型的数据。

通过以上详细的教程，你应该对 Ruby 连接 PostgreSQL 数据库有了全面的了解，可以在实际项目中灵活运用这些知识进行数据库相关的开发工作。无论是简单的查询、插入、更新和删除操作，还是复杂的事务处理、连接池使用以及与 Rails 框架的结合，都能得心应手。同时，在遇到常见问题时，也能根据上述的解决方法进行排查和修复。