Ruby — Как читать с БД Oracle в Excel файл?

Question

[Денис Черный]

Всем привет, есть БД Oracle:
Number 1 | Number 2 | Number 3

Как мне достать с БД все данные и записать в Excel файл?
Затык происходит именно с чтением из БД SQL запросом в курсор, доставанием из курсора в массив и из массива в файл=(

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

Сделал вот так, работает, но при кол-ве строк около 200к-300к, скрип работает около 4-5мин. Есть пути его оптимизировать?

Решение нашел, уменьшаем количество итераций, путем увеличение буфера строк:

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

Время уменьшилось в два раза.

Answer 2

[d'Ivan]

Не претендую на точность (код не проверял), но думаю, что должно быть типа такого:

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

Имеет смысл взять что-то более высокоуровневое, попроще в использовании, из Object-relational mapping, например тот же Sequel.

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

Comments

[Денис Черный]

Что то не работает=( Подскажи, пожалуйста, как быть если надо собрать данные из нескольких БД?

[d'Ivan]

Денис Черный: "что-то" - это не ответ. Должно быть какое-то сообщение об ошибке или что-то с файлом не так.
Если нужно работать с двумя БД, то connection1, connection2 и вперёд.
Сисадмин или программист?

[Денис Черный]

Роман Мирр: Получилось, а есть варианты оптимизировать скрип, а то при 200к строк выполняет около 4 мин, или другие пути решения=/
Инженер техподдержки)

[d'Ivan]

Денис Черный: t-SQL: stackoverflow.com/a/9261762

[d'Ivan]

Денис Черный: сейчас скрипт загружает все 200К в память. Нужно порциями.

[Денис Черный]

Роман Мирр: Привет, подскажи в чем может быть беда, cursor = connection.parse сложный SQL запрос с несколькими JOIN, просто напросто пропускает, т.е курсор получается пустой=( С легким проходит на ура...

[d'Ivan]

Денис Черный: не работал с Ораклом, но там должны быть программы с графическим интерфейсом, позволяющие исполнять SQL запросы, получая в ответ результаты в табличном формате. Пробуй там и после этого подстраивай скрипт на Ruby.

Answer 3

[Константин Цветков]

Как мне достать с БД все данные и записать в Excel файл?

Использовать стандартное, для СУБД такого уровня, средство экспорта данных. Или вставить таблицу в файл Excel.

с определенной периодичностью

Запускать экспорт планировщиком задач или использовать для этого средства Oracle.

Comments

[Денис Черный]

Это скрипт, будет лежать на сервере и с определенной периодичностью парсить данные из БД в Excel и рассылать по почте.