SQLite は Perl, Python, Ruby などのスクリプト言語からでも簡単に利用することができます。本稿では Ruby (ver 2.3) から SQLite にアクセスする基本的な方法について簡単に説明します。
Ruby から SQLite にアクセスする場合、モジュール sqlite3 が必要になります。sqlite3 がインストールされていない場合、Ubuntu 系の OS では次のコマンドで簡単にインストールすることができます。
$ sudo apt-get install ruby-sqlite3
これで SQLite にアクセスすることができます。
Ruby で SQLite を使用する場合、最初に require 'sqlite3' でモジュール sqlite3 をロードしてください。データベースの接続はメソッド SQLite3::Database.new を、切断は close を使います。次の例を見てください。
リスト : 接続と切断 (test01.rb)
# coding: utf-8
require 'sqlite3'
# 接続
db = SQLite3::Database.new("sample_ruby.sqlite")
# 処理
# 切断
db.close
print "OK\n"
# ブロックを使う方法
SQLite3::Database.new("sample_ruby.sqlite") do |db|
# 処理
end
print "OK\n"
Database.new の引数にはデータベース名を指定します。データベース名と一致するファイルが見つからない場合、新しいファイルが生成されます。同名のファイルがある場合はそれをデータベースとして使用します。Database.new の返り値 (データベースオブジェクト) はデータベースの操作に必要なので、変数 db に格納しておきます。切断はメソッド close を呼び出すだけです。
このほかに、Database.new はブロックを受け取ることもできます。ブロックの引数にはデータベースオブジェクトが渡されます。この場合、close はブロック終了時に自動的に呼び出されます。
Ruby で SQL 文を実行する場合、データベースオブジェクトのメソッド execute で行います。execute は引数に渡された文字列を SQL 文として実行します。execute は SQL 文にパラメータ情報を含めることができます。パラメータは ? で表します。これを「プレスホルダー」といいます。パラメータに対応する値は execute の第 2 引数以降に渡します。
Ruby にはもう一つ方法があって、? のかわりに名前 (Ruby のシンボル) を使います。execute の第 2 引数以降に :名前 => 値 を渡します。execute の第 2 引数は可変個引数で、:名前 => 値 を渡すと、それらをハッシュに格納して execute に渡します。
可変個引数の簡単な実行例を示します。
リスト : Ruby の可変個引数 def foo(*a) p a end foo(1) foo(1, 2) foo(1, 2, 3) foo(:foo => 1) foo(:foo => 1, :bar => 2) foo(:foo => 1, :bar => 2, :baz => 3)
mhiroi@mhiroi-VirtualBox:~/sqlite$ ruby test00.rb
[1]
[1, 2]
[1, 2, 3]
[{:foo=>1}]
[{:foo=>1, :bar=>2}]
[{:foo=>1, :bar=>2, :baz=>3}]
それでは簡単な例題として、次に示すテーブルを作成してみましょう。
| id | name | age | sex | |
|---|---|---|---|---|
| 1 | Foo | 50 | male | foo@yahoo.co.jp |
| 2 | Bar | 35 | female | bar@yahoo.co.jp |
| 3 | Baz | 40 | male | baz@yahoo.co.jp |
| 4 | Oops | 30 | female | oops@yahoo.co.jp |
リスト : テーブルの作成 (test02.rb)
# coding: utf-8
require 'sqlite3'
# 接続
db = SQLite3::Database.new("sample_ruby.sqlite")
# テーブルの作成
db.execute("create table person (id integer, name text, age integer, sex text, email text)")
for xs in [[1, 'Foo', 50, 'male', 'foo@yahoo.co.jp'],
[2, 'Bar', 35, 'female', 'bar@yahoo.co.jp'],
[3, 'Baz', 40, 'male', 'baz@yahoo.co.jp'],
[4, 'Oops', 30, 'female', 'oops@yahoo.co.jp']]
db.execute("insert into person (id, name, age, sex, email) values (?,?,?,?,?)", *xs)
end
# 切断
db.close
print "OK\n"
また、次のようにヒアドキュメントを使っても、簡単にデータを挿入することができます。
リスト : テーブルの作成 (test021.rb)
# coding: utf-8
require 'sqlite3'
# 接続
db = SQLite3::Database.new("sample_ruby.sqlite")
# テーブルの作成
db.execute("create table person (id integer, name text, age integer, sex text, email text)")
str = <<SQL
insert into person (id, name, age, sex, email) values
(1, 'Foo', 50, 'male', 'foo@yahoo.co.jp'),
(2, 'Bar', 35, 'female', 'bar@yahoo.co.jp'),
(3, 'Baz', 40, 'male', 'baz@yahoo.co.jp'),
(4, 'Oops', 30, 'female', 'oops@yahoo.co.jp')
SQL
db.execute(str)
# 切断
db.close
print "OK\n"
execute は最初の SQL 文しか実行しません。複数の SQL 文を実行するときは、メソッド execute_batch を使ってください。
リスト : テーブルの作成 (test022.rb)
# coding: utf-8
require 'sqlite3'
# 接続
db = SQLite3::Database.new("sample_ruby.sqlite")
# テーブルの作成
str = <<SQL
create table person (id integer, name text, age integer, sex text, email text);
insert into person (id, name, age, sex, email) values
(1, 'Foo', 50, 'male', 'foo@yahoo.co.jp'),
(2, 'Bar', 35, 'female', 'bar@yahoo.co.jp'),
(3, 'Baz', 40, 'male', 'baz@yahoo.co.jp'),
(4, 'Oops', 30, 'female', 'oops@yahoo.co.jp');
SQL
db.execute_batch(str)
# 切断
db.close
print "OK\n"
select 文のように結果を返す SQL 文も、メソッド execute を使って実行します。その結果は次のようにブロックで受け取ることができます。
リスト : データの抽出 (test03.rb)
# coding: utf-8
require 'sqlite3'
# 接続
db = SQLite3::Database.new("sample_ruby.sqlite")
# データの抽出
db.execute('select * from person') do |xs|
p xs
end
# 切断
db.close
print "OK\n"
mhiroi@mhiroi-VirtualBox:~/sqlite$ ruby test03.rb [1, "Foo", 50, "male", "foo@yahoo.co.jp"] [2, "Bar", 35, "female", "bar@yahoo.co.jp"] [3, "Baz", 40, "male", "baz@yahoo.co.jp"] [4, "Oops", 30, "female", "oops@yahoo.co.jp"] OK
メソッド get_first_row を使うと最初の 1 行だけを、メソッド get_first_value を使うと最初の値を取り出すことができます。
リスト : データの抽出 (test031.rb)
# coding: utf-8
require 'sqlite3'
# 接続
db = SQLite3::Database.new("sample_ruby.sqlite")
p db.get_first_row('select * from person')
p db.get_first_value('select count(*) from person')
# 切断
db.close
print "OK\n"
mhiroi@mhiroi-VirtualBox:~/sqlite$ ruby test031.rb [1, "Foo", 50, "male", "foo@yahoo.co.jp"] 4 OK
「トランザクション (transaction)」は処理とか取引という意味ですが、SQL では「関連した複数の処理を一つの処理にまとめたもの」をトランザクションといいます。SQL 文では、BEGIN でトランザクションを開始します。この場合、データベースの変更を伴う行う作業 (insert, update など) では、そのつどデータベースに変更が反映されるのではありません。トランザクションを終了して実際にデータベースの変更を行う SQL 文が COMMIT (コミット) です。
Ruby で SQLite を操作する場合、オートコミット (AutoCommit) モードが設定されていると、トランザクションやコミットを明示的に指定しなくても、データベースを操作することができます。insert や update などの SQL 文を実行するとき、SQLite は暗黙のうちにトランザクションを開始します。さらに、オートコミットモードが有効だと、SQL 文が終了したとき、SQLite は自動的にコミットしてくれます。
これはとても便利な機能なのですが、コミットはけっこう時間がかかる処理なので、オートコミットモードのままでたくさんのデータをいっきに挿入しようとすると、時間がとてもかかるのです。次の例を見てください。
リスト : オートコミットモードでの挿入 (test04.rb)
# coding: utf-8
require 'sqlite3'
# 接続
db = SQLite3::Database.new("test_ruby.sqlite")
# テーブル作成
db.execute('create table test (name text, val real)');
db.execute('create index name_idx on test(name)');
# データ挿入
for n in 1..10000
s = sprintf("test%05d", n)
db.execute('insert into test (name, val) values (?, ?)', s, rand)
end
# 切断
db.close
print "OK\n"
test_ruby.sqlite に TEXT と REAL を 10000 件挿入します。実行時間は次のようになりました。
mhiroi@mhiroi-VirtualBox:~/sqlite$ time ruby test04.rb OK real 1m34.928s user 0m2.400s sys 0m6.932s 実行環境 : Lubuntu 16.04 on VirtualBox, Core i7-2670QM 2.20GHz
10000 件のデータを挿入するのに約 1 分 35 秒もかかりました。トランザクションを明示的に指定すると、もっと高速にデータを挿入することができます。
リスト : トランザクションの利用 (test05.rb)
# coding: utf-8
require 'sqlite3'
# 接続
db = SQLite3::Database.new("test_ruby.sqlite")
# テーブル作成
db.execute('create table test (name text, val real)');
db.execute('create index name_idx on test(name)');
# データ挿入
db.transaction do
for n in 1..10000
s = sprintf("test%05d", n)
db.execute('insert into test (name, val) values (?, ?)', s, rand)
end
end
# 切断
db.close
print "OK\n"
db.transaction でトランザクションを開始します。db.transaction にブロックを渡すと、ブロックの終了後に自動的にコミットされます。ブロックのかわりに begin 文を渡すこともできます。この場合、自動的にコミットされないので、メソッド db.commit を呼び出してください。
mhiroi@mhiroi-VirtualBox:~/sqlite$ time ruby test05.rb OK real 0m0.604s user 0m0.480s sys 0m0.004s 実行環境 : Lubuntu 16.04 on VirtualBox, Core i7-2670QM 2.20GHz
10000 件のデータを 1 秒もかからずに挿入することができました。
SQLite はデータベース名に :memory: を指定すると、メモリ上にデータベースを作成することができます。簡単な実行例として、test04.rb のデータベースをメモリに変更してみましょう。プログラムは次のようになります。
リスト : インメモリデータベース (test06.rb)
# coding: utf-8
require 'sqlite3'
# 接続
db = SQLite3::Database.new(":memory:")
# テーブル作成
db.execute('create table test (name text, val real)');
db.execute('create index name_idx on test(name)');
# データ挿入
for n in 1..10000
s = sprintf("test%05d", n)
db.execute('insert into test (name, val) values (?, ?)', s, rand)
end
# 切断
db.close
print "OK\n"
ファイル名を :memory: に変更しただけです。実行結果は次のようになりました。
mhiroi@mhiroi-VirtualBox:~/sqlite$ time ruby test06.rb OK real 0m0.550s user 0m0.524s sys 0m0.008s 実行環境 : Lubuntu 16.04 on VirtualBox, Core i7-2670QM 2.20GHz
オートコミットしているはずですが、とても高速ですね。トランザクションを指定すると、次のようになります。
リスト : インメモリデータベース (test07.rb)
# coding: utf-8
require 'sqlite3'
# 接続
db = SQLite3::Database.new(":memory:")
# テーブル作成
db.execute('create table test (name text, val real)');
db.execute('create index name_idx on test(name)');
# データ挿入
db.transaction do
for n in 1..10000
s = sprintf("test%05d", n)
db.execute('insert into test (name, val) values (?, ?)', s, rand)
end
end
# 切断
db.close
print "OK\n"
mhiroi@mhiroi-VirtualBox:~/sqlite$ time ruby test07.rb OK real 0m0.495s user 0m0.472s sys 0m0.008s 実行環境 : Lubuntu 16.04 on VirtualBox, Core i7-2670QM 2.20GHz
少しですが高速になりました。インメモリで利用する場合、オートコミットのままでもよいかもしれません。興味のある方はいろいろ試してみてください。