Ruby - How to read from Oracle DB to Excel file?

Question

ruby

Hello everyone, there is an Oracle database:
Number 1 | Number 2 | Number 3

How can I get all the data from the database and write it to an Excel file?
Plugging occurs precisely with reading from the SQL database with a query to the cursor, getting from the cursor to the array and from the array to the file =(

require 'oci8'
require 'spreadsheet'
 
Login = "admin"
Password = "admin"
Server = "admin"
#Подключение к БД Oracle 
connection = OCI8.new(Login, Password, Server)
cursor = connection.parse("Select Number1, Number2, Number3 From Table1")
cursor.exec
 
cursor.fetch_hash do |row|
        descr = nil
        if !row['Number1'].nil?
        descr = row['Number1'].encode('utf-8')
        end
///// Здесь сомневаюсь в правильности цикла, подскажите, как с курсора добавить данные в массив????
end
    cursor.close
connection.logoff
#Создание Excel файла, запись в него данных 
workbook  = Spreadsheet::Workbook.new
    heading = Spreadsheet::Format.new(:weight => :bold, :size =>10, :border => :thin, :align => :merge, :pattern_fg_color => :yellow, :pattern => 1)
    normal  = Spreadsheet::Format.new(size: 10)
    worksheet = workbook.create_worksheet name: 'Название'
    heading_row = worksheet.row(0)
    heading_row.set_format(0, heading)
    heading_row.set_format(1, heading)
    heading_row.set_format(2, heading)
    worksheet.row(0).concat(['Number1', 'Number2', 'Number3'])
    worksheet.column(0).width=10
    worksheet.column(1).width=55
    worksheet.column(2).width=15

Здесь по идеи цикл чтения с массива/////// Как правильно сделать запись с массива в файл по столбцам?????

report_name = Time.now.strftime("1.xls")
    worksheet.write(report_name)

Answer 1

cursor.fetch_hash do |row|
  
    data_report_list = {'N1' => row['Number1'], 'N2' => row['Number2'], 'N3'=> row['Number3'], 'N4'=> row['Number4']}
      worksheet.row(row_num2k).concat([data_report_list['N1'], data_report_list['N2'],data_report_lis['N3'],data_report_list['N4'], '555' ])			
      row_num2k+=1
      end
      cursor.close

I did it like this, it works, but with a number of lines of about 200k-300k, the script works for about 4-5 minutes. Is there a way to optimize it?
I found the solution, we reduce the number of iterations by increasing the line buffer:

connection = OCI8.new(Login, Password, Server)
connection.prefetch_rows = 10000

The time has been halved.

Answer 2

I do not pretend to be accurate (I did not check the code), but I think it should be like this:

require 'oci8'
require 'spreadsheet'

def build_report(report_name, worksheet name)
  Spreadsheet.client_encoding = 'UTF-8'

  workbook  = Spreadsheet::Workbook.new
  worksheet = workbook.create_worksheet name: worksheet name

    yeild(worksheet)  # выполнение блока внутри do |worksheet|

    worksheet.write(report_name)
end

def run
  Login = "admin"
  Password = "admin"
  Server = "admin"

  connection = OCI8.new(Login, Password, Server)

  cursor = connection.parse("Select Number1, Number2, Number3 From Table1")
  cursor.exec
   
  #Создание Excel файла, запись в него данных 

  build_report( "report_#{Time.now.strftime("%F")}.xls", "Название" ) do |worksheet|
    row_num = 0

    heading = Spreadsheet::Format.new(:weight => :bold, :size =>10, :border => :thin, :align => :merge, :pattern_fg_color => :yellow, :pattern => 1)
    normal  = Spreadsheet::Format.new(size: 10)
    cursor.fetch_hash do |row|
      if row_num == 0
        heading_row = worksheet.row(row_num)
        heading_row.set_format(0, heading)
        heading_row.set_format(1, heading)
        heading_row.set_format(2, heading)

        descr = row['Number1'].nil? : nil : row['Number1'].encode('utf-8')
        worksheet.row(row_num).concat([descr, row['Number2'], row['Number3']])
      else
        worksheet.row(row_num).concat([row['Number1'], row['Number2'], row['Number3']])
      end
      row_num += 1
    end

    worksheet.column(0).width=10
    worksheet.column(1).width=55
    worksheet.column(2).width=15
  end

  cursor.close
  connection.logoff
end

run

It makes sense to take something more high-level, easier to use, from Object-relational mapping , for example, the same Sequel .

Sequel currently has adapters for ADO, Amalgalite, CUBRID, DataObjects, IBM_DB, JDBC, MySQL, Mysql2, ODBC, Oracle , PostgreSQL, SQLAnywhere, SQLite3, Swift, and TinyTDS.

Answer 3

How can I get all the data from the database and write it to an Excel file?

Use a standard, for a DBMS of this level, data export tool. Or paste the table into an Excel file.

with a certain frequency

Run the export with the task scheduler or use the Oracle tools for this.