如何在Ruby中运行SQL文件 [英] How to run a SQL file in Ruby

问题描述

我有多个 .sql 文件，这些文件可能包含多个Alter表语句或视图定义或 plsql 函数定义。我想在PostgreSQL DB上应用完整的 sql 文件。

I have multiple .sql files which might consists of multiple Alter table statements or view definitions or plsql function definitions. I want to apply complete sql file on PostgreSQL DB. It should be something similar like

psql -h "HostName" -p "Port" -U "userName" -d "pwd" -f "Sql file location"

我尝试做 PG.connect .execute ，这样我就需要加载文件并分别执行每个语句。有没有办法可以在Ruby中执行整个 sql 文件？

I have tried doing PG.connect.execute and that way I need to load the file and execute each statement separately. Is there a way I can execute entire sql file in Ruby?

推荐答案

有两个用于与PostgreSQL数据库通信的API，两个
均可作为gems使用。 pg gem提供了到Postgres的纯Ruby绑定。

There are two APIs for communicating with a PostgreSQL database, and both are available as gems. The pg gem provides a pure Ruby binding to Postgres.

示例：
此代码假设您正在通过TCP / IP在$端口5432上访问数据库b $ b您的本地计算机：

Exemple: This code assumes you’re accessing the database through TCP/IP on port 5432 of your local machine:

gem 'pg'
require 'pg'
 def with_db
   db = PG.connect(
     dbname: 'dbname',
     user: 'user',
     password: 'password'
   )
   begin
      yield db
   ensure
      db.close
   end
 end

sql = File.open('path/to/your_sql.sql', 'rb') { |file| file.read }
with_db do |db|
  begin
    db.exec(sql)
  rescue PG::Error
     #####
  end
end

更多信息

这篇关于如何在Ruby中运行SQL文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！