Edited at
移行Day 12

AdventCalendar Day12 [超Ruby入門]


はじめに

本記事は、超Ruby入門12日目の記事です。

コメント頂ける方は、ガイドラインを読んで頂けると幸いです。


目的

Databaseの基本的な使い方を知る。


目標

RubyでCSVとSQLiteに関する処理ができるようになる。


やること

今日から3日間一つの問題に関して学んでいく。やることは以下の通りだ。


  1. 一部のデータをCSVから取得してSQLiteに移し替える


  2. APIを実装


  3. APIのテスト



前提知識


CSVとは

CSVとは、RFC4180に定義されている7つのフォーマットに従ったテキストデータだ。

カンマ区切りでデータ単位を区別するファイルだと思えばよい。


SQLiteとは

SQLiteは、組み込み型データベースエンジンの一つだ。

代表的なRDBMSであるMySQLやPostgreSQLとは異なり、サーバではなくライブラリである。

データベースとは、整備されたデータの集まりのことである。


SQL

SQLは、リレーショナルデータベース管理システム(RDBMS)と対話するための言語だ。

基本的な構文である4つを紹介する。

create

createはデータベースオブジェクト(表、インデックス、制約など)の定義を行う。

create table table_name (col_name1, data_type1, col_name2, data_type2, .... );

select

selectは、データベースからデータを取り出す。

select * from table_name;

insert

insertは、データベースにデータを登録する。

insert into table_name (col_name1, col_name2, ....) values (data1, data2, ....);

update

updateは、データベースに登録されているデータの更新を行う。

update table_name set col_name1 = data [WHERE ....];

SQLは、大文字小文字を区別しない。個人的に大文字は素早く頭に入ってこないため小文字を利用している。

スタイルは、開発現場の方針に合わせれば良いだろう。

分析SQLのコーディングスタイル

SQLプログラミング作法


準備

KAKENサイトの検索文字を「自然言語処理」としてCSVファイルをダウンロードしdata.csvと名前変更。

データが正しく取得できているかを確認する。

head -n 5 data.csv

gem sqlite3をインストールし、使用可能にする。


実践

csvのデータを読み込む構文は以下だ。

CSV.foreach("path/to/file.csv") do |row|

# use row here...
end

rubyでダウンロードしたcsvを読み込むことができるか確かめるため、irbを展開し

require 'csv'

CSV.foreach('data.csv') do | item |
puts item
end

を実行するとCSV::MalformedCSVError (Illegal quoting in line 1.)というエラーが出力されてしまう。

原因調査を行うためにdata.csvのファイル情報を確認しよう。

$file data.csv

> UTF-8 Unicode (with BOM) text, with very long lines, with CRLF, LF line terminators

(with BOM)の部分が悪さをしているのではないかとあたりをつけて検索してみましょう。

すると

BOM付きCSVを読み込む[Ruby 2.3.0]Regression since ruby 2.4 - unknown encoding name - bom|utf-8からbomはruby側の問題であることがわかる。

対処方法として

1. csv_data = CSV.read('data.csv', 'r:BOM|UTF-8', headers: true)

2. Ruby2.4から追加された、liberal_parsingオプションが使えそうだ。

今回は膨大なCSVのデータにも対応可能なように、CSV.readを使わないようにしよう。


store_csv_to_sqlite.rb

require 'csv'

CSV.foreach('data.csv', liberal_parsing: true, headers:true).each_with_index do | item, i |

puts item
if i == 3
break
end
end


上記コードでCSVファイルのデータが確認できるはずだ。

CSVの操作に慣れてきたところで、sqlite3の使い方を見ていこう。

下記コードを実行すると

require 'sqlite3'

db = SQLite3::Database.new("kaken")
db.close

同ディレクトリにkakenというsqliteデータベースが作成されているはずだ。

テーブルを作成してみよう。


store_csv_to_sqlite.rb

require 'sqlite3'

db = SQLite3::Database.new("kaken")
sql = <<-SQL
create table nlp (
id integer primary key,
name text
type text,
keyword text,
organ text,
start text
finish text
);
SQL
db.execute(sql)

sqlite3を起動してテーブルが作成されているか確認できる。

sqlite> .help

sqlite> .table
nlp

sqlite> .schema nlp
CREATE TABLE nlp (
id integer primary key,
name text
type text,
keyword text,
organ text,
start text
finish text
);

以下のコードは、CSVファイルからSQLiteへデータをinsertするコードだ。

CSV.foreach('data.csv', liberal_parsing: true, headers:true).each_with_index do | item, i |

name = item["研究代表者"]
type = item["研究種目"]
keyword = item["キーワード"]
organ = item["研究機関"]
start = item["研究期間 (年度)"].split.first
finish = item["研究期間 (年度)"].split.last
insert_data_sql = <<-SQL
insert into nlp values(
?,
?,
?,
?,
?,
?,
?
);
SQL
db.execute(insert_data_sql, i + 1 , name, type, keyword, organ, start, finish)
end

正しくデータが入力されたかを確かめてみよう。

$sqlite kaken

sqlite> .header on
sqlite> .mode column
sqlite> select * from nlp limit 5;


完成コード


store_csv_to_sqlite.rb

require 'sqlite3'

require 'csv'
## Table作成 ##
db = SQLite3::Database.new("kaken")
create_table_sql = <<-SQL
create table nlp (
id integer primary key,
name text,
type text,
keyword text,
organ text,
start text,
finish text
);
SQL

begin
db.execute(create_table_sql)
rescue SQLite3::SQLException
puts "You already created kaken table"
end

## SQLiteへデータを流し込む##
CSV.foreach('data.csv', liberal_parsing: true, headers:true).each_with_index do | item, i |
name = item["研究代表者"]
type = item["研究種目"]
keyword = item["キーワード"]
organ = item["研究機関"]
start = item["研究期間 (年度)"].split.first
finish = item["研究期間 (年度)"].split.last
insert_data_sql = <<-SQL
insert into nlp values(
?,
?,
?,
?,
?,
?,
?
);
SQL
db.execute(insert_data_sql, i + 1 , name, type, keyword, organ, start, finish)
end
db.close



課題


  1. 完成コードには、プレースホルダが使われている。プレースホルダについて該当箇所を探せ。


  2. sqliteでない、適当なデータベースにCSVのデータを格納せよ。



もっと深めたい人

7つのデータベース 7つの世界


時間がある時にやること


  • CSV.foreach('data.csv', encoding: 'BOM|UTF-8'', headers:true)では動かない理由を解明