I am using Ruby to automate reporting from a remote MySQL database. I am using multiple queries (and not joining them into one) as I eventually wish to split rows based a given value in one or several columns. It also gives me the option to work across different databases or even different hosts.
I am using the mysql2, watir and csv Ruby gems.
My code:
#!/usr/local/bin/ruby
require "mysql2"
require "watir"
require "csv"
puts "Initializing Report"
Mysql2::Client.default_query_options.merge!(:as => :array)
mysql = Mysql2::Client.new(:host => "1.2.3.4", :useame => "user", :pass => "password", :database => "db")
puts "Successfully accessed db"
month = mysql.query("SELECT DATE_FORMAT(db.table.start, '%m') FROM db.table WHERE db.start.group = 1;")
report = mysql.query("SELECT db.table.name, db.table.company, db.table.type FROM db.table WHERE db.start.group = 1;")
CSV.open("Report.csv", "wb") do |csv|
csv << ["Month", "Name", "Company", "Type"]
month.zip(report).each {|row| csv << row}
end
puts "Results can be found in Report.csv"
Now that report query is much bigger, spaing several dozen columns and several thousand rows made from almost a dozen INNER JOINs and WHERE criteria. It has been simplified for this question.
The output the above code gives me, in a CSV file, is:
Month, Name, Company, Type
["1"], ["Adam", "companyrecordlabel", "Ficticious"]
["1"], ["Alana", "beeUrself", "Made-up"]
. .
. .
. .
["12"], ["Zed", "Meds4U", "Fake"]
and the output I desire:
Month, Name, Company, Type
1, Adam, companyrecordlabel, Ficticious
1, Alana, beeUrself, Made-up
. .
. .
. .
12, Zed, Meds4U, Fake
I have been struggling with this for hours and I caot seem to get the output I want.
I have read CSV files, outputted multiple (and multi-dimensional) arrays to a .txt file and written a single (large) query to a CSV file without the brackets or quotation marks; but I caot seem to achieve this.
Thank you for any and all help in advance.
