hello-world
webエンジニアのメモ。とりあえずやってみる。

rubyでDB(mysql)に保存された特定の値をcsvで書き出す

公開日時

ruby(1.9.3)でDB(mysql)に保存された特定の値をcsvで書き出すスクリプトを書いてみました。

SQLは実行結果をcsvファイルとして書きだすことができるので、普通はSQLだけで済ませられるのですが、今回はテーブルにJSON形式で保存されたデータから特定の値を書き出す必要があったのでスクリプトを書きました。

定義

以下のようなsampleテーブルがあるとします。

CREATE TABLE `sample` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`json_data` TEXT NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`)
);

また、json_dataカラムに保存されるjsonにはnameとmessageが格納されているとします。

{ name: "json", message: "hello" }

get_csv.rb

スクリプトは以下のようになりました。

DBから取得したデータをループで回し、jsonをパースして必要データをcsvに書きだしています。

また、highline/importを使用して、DBのユーザ名とパスワードをコマンドラインから入力するようにしています。

# get_csv.rb
require 'rubygems'
require 'bundler/setup'

require 'mysql2'
require 'highline/import'
require 'json'
require 'csv'

def ask_authentication
    username = ask("enter your username: ") { |q| q.echo = true }
    password = ask("enter your password: ") { |q| q.echo = "*" }
    [username, password]
end

username, password = ask_authentication
begin
  client = Mysql2::Client.new(:host => "localhost", :username => username, :password => password, :database => "sample")
rescue
  p 'can not login mysql'
  exit
end

res = client.query('select * from sample')

csv_string = CSV.generate(:force_quotes => true) do |csv|
  csv << ['id', 'name', 'message', 'created_at']

  res.each do |row|
    json = JSON.parse(row['json_data'])

    csv << [
      row['id'],
      json['name'],
      json['message'],
      row['created_at']
    ]
  end
end

File.open('info.csv', 'w:cp932') do |file|
  file.write(csv_string) 
end

Gemfileの作成

Gemfileに今回使用したgemを記述しておきます。

# Gemfile
source "https://rubygems.org"                  
gem 'mysql2'
gem 'highline'

実行方法

cd {スクリプトのあるディレクトリ}
bundle install --path vendor/bundle
ruby get_csv.rb

bundle install --path vendor/bundle とすることで、gemをスクリプトのあるディレクトリ以下にインストールすることができ、環境を汚さずにスクリプトが実行できます。

スクリプトを実行するとcsvが出力されてできあがりです。

参考URL


Related #MySQL

[mac]homebrewでmysqlをインストール

- インストール

RDSで日本語を使う場合の初期設定(utf8mb4)

RDSで日本語を使う場合、Parameter Groupsを作成し、

MySQLで時間毎のデータを集計する

ちょっとした分析レポートを作る機会があったのですが、「時間ごとに集計するSQLってどうやって書けばいいんだろう」と思って色々悩んでいました。