やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3でランダムに複数行を取得する

 random()関数を使う。

成果物

手順

1. テーブルを作る

create_Names.sql

create table Names(
    Id primary key not null,
    Name text not null
)

Names.tsv

Id    Name
1   A
2   B
3   C
4   D
5   E
6   F
7   G
8   H
9   I
10  J
11  K
12  L
13  M
14  N
15  O
16  P
17  Q
18  R
19  S
20  T
21  U
22  V
23  W
24  X
25  Y
26  Z
sqlite3 Names.db < create_Names.sql
sqlite3 Names.db '.mode tabs' '.import Names.tsv Names'
$ sqlite3 Names.db '.mode tabs' '.headers on' 'select * from Names'
Id  Name
1  A
2  B
3  C
4  D
5  E
6  F
7  G
8  H
9  I
10 J
11 K
12 L
13 M
14 N
15 O
16 P
17 Q
18 R
19 S
20 T
21 U
22 V
23 W
24 X
25 Y
26 Z

 ちなみに連番の作成は以下の通り。

  • paste <(echo {1..26} | tr ' ' '\n') <(echo {A..Z} | tr ' ' '\n') > Names.tsv
    • 数値の連番はLibreOffice Calcのセルに1を入れて、セル右端の四角を下へドラッグ&ドロップすることでも可
    • アルファベットの連番はシェルのecho {A..Z} | tr ' ' '\n'で作成できる

2. ランダムに取得する

$ sqlite3 Names.db \
'.trace stdout' \
'.mode tabs' \
'.headers on' \
'select * from Names where Id in (select Id from Names order by random() limit 5)'
Id Name
12 L
2  B
21 U
6  F
9  I

 何度か実行してみればランダムであることがわかる。

3. 出力を整形する

3. 出力を整形する

3-1. ヘッダを出力する

 .headers on

sqlite3 Names.db '.headers on' 'select * from Names'

 詳細は以下ヘルプ参照。

$ sqlite3 :memory: '.help headers'
.headers on|off          Turn display of headers on or off

3-2. SQL文を出力する

 .trace stdout

sqlite3 Names.db '.trace stdout' 'select * from Names'

 詳細は以下ヘルプ参照。

$ sqlite3 :memory: '.help trace'
-- Loading resources from /home/pi/.sqliterc
.trace ?OPTIONS?         Output each SQL statement as it is run
    FILE                    Send output to FILE
    stdout                  Send output to stdout
    stderr                  Send output to stderr
    off                     Disable tracing
    --expanded              Expand query parameters
    --plain                 Show SQL as it is input
    --stmt                  Trace statement execution (SQLITE_TRACE_STMT)
    --profile               Profile statements (SQLITE_TRACE_PROFILE)
    --row                   Trace each row (SQLITE_TRACE_ROW)
    --close                 Trace connection close (SQLITE_TRACE_CLOSE)

3-3. 不要な出力を削除する

 SQLite3は~/.sqlitercファイルがあると最初に以下が出力されてしまう。

-- Loading resources from /home/pi/.sqliterc

 これは標準エラー(ファイルディスクリプタ2番)へ出力される。よって、以下のようにすると削除できる。

sqlite3 $DB_PATH $SQL 2>/dev/null
sqlite3 Names.db '.mode tabs' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)' 2>/dev/null

 または-batch -interactiveフラグを付与する。

sqlite3 -batch -interactive Names.db '.mode tabs' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'

3-4. 出力形式を指定する

$ sqlite3 Names.db '.mode tabs' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'

 .modeで指定する。使える値は以下。わざと不正値を渡すことでヒントのメッセージが得られる。

  • 3.33.0で追加されたのはbox,json,markdown,table
$ sqlite3 :memory: '.mode x'
Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl
  • ascii
  • box
  • column
  • csv
  • html
  • insert
  • json
  • line
  • list
  • markdown
  • quote
  • table
  • tabs
  • tcl
$ sqlite3 Names.db '.mode ascii' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
IdName13M14N20T21U4D
$ sqlite3 Names.db '.mode box' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
┌────┬──────┐
│ Id │ Name │
├────┼──────┤
│ 2  │ B    │
│ 24 │ X    │
│ 4  │ D    │
│ 5  │ E    │
│ 6  │ F    │
└────┴──────┘
$ sqlite3 Names.db '.mode column' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
Id  Name
--  ----
17  Q   
18  R   
23  W   
25  Y   
8   H  
$ sqlite3 Names.db '.mode csv' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
Id,Name
1,A
13,M
2,B
7,G
8,H
$ sqlite3 Names.db '.mode html' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
<TR><TH>Id</TH>
<TH>Name</TH>
</TR>
<TR><TD>17</TD>
<TD>Q</TD>
</TR>
<TR><TD>24</TD>
<TD>X</TD>
</TR>
<TR><TD>25</TD>
<TD>Y</TD>
</TR>
<TR><TD>26</TD>
<TD>Z</TD>
</TR>
<TR><TD>9</TD>
<TD>I</TD>
</TR>
$ sqlite3 Names.db '.mode insert' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
INSERT INTO "table"(Id,Name) VALUES('12','L');
INSERT INTO "table"(Id,Name) VALUES('16','P');
INSERT INTO "table"(Id,Name) VALUES('24','X');
INSERT INTO "table"(Id,Name) VALUES('26','Z');
INSERT INTO "table"(Id,Name) VALUES('5','E');
$ sqlite3 Names.db '.mode json' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
[{"Id":"10","Name":"J"},
{"Id":"12","Name":"L"},
{"Id":"22","Name":"V"},
{"Id":"3","Name":"C"},
{"Id":"5","Name":"E"}]
$ sqlite3 Names.db '.mode line' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
   Id = 18
 Name = R

   Id = 22
 Name = V

   Id = 25
 Name = Y

   Id = 26
 Name = Z

   Id = 8
 Name = H
$ sqlite3 Names.db '.mode list' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
Id|Name
10|J
12|L
19|S
2|B
22|V
$ sqlite3 Names.db '.mode quote' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
'Id','Name'
'10','J'
'14','N'
'17','Q'
'2','B'
'22','V'
$ sqlite3 Names.db '.mode table' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
+----+------+
| Id | Name |
+----+------+
| 10 | J    |
| 13 | M    |
| 24 | X    |
| 26 | Z    |
| 3  | C    |
+----+------+
$ sqlite3 Names.db '.mode tabs' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
Id  Name
1  A
16 P
17 Q
24 X
25 Y
$ sqlite3 Names.db '.mode tcl' '.headers on' 'select * from Names where Id in (select Id from Names order by random() limit 5)'
"Id" "Name"
"14" "N"
"15" "O"
"24" "X"
"26" "Z"
"6" "F"

所感

 ランダムができればランダムジェネレータが作れる。SQLite3だけで作れそう。

情報源

前回まで

対象環境

$ uname -a
Linux raspberrypi 4.19.97-v7l+ #1294 SMP Thu Jan 30 13:21:14 GMT 2020 armv7l GNU/Linux