sqlite3

2018-09-282018-09-30

macOSをMojaveに更新した。スクリプト実行環境のバージョンは変わったか

Python

Mojave

1
2
3

Python 2.7.10 (default, Aug 17 2018, 17:41:52)
[GCC 4.2.1 Compatible Apple LLVM 10.0.0 (clang-1000.0.42)] on darwin
Type "help", "copyright", "credits" or "license" for more information.

High Sierra

1
2
3

Python 2.7.10 (default, Oct 6 2017, 22:29:07)
[GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.31)] on darwin
Type "help", "copyright", "credits" or "license" for more information.

Pythonのバージョンは変更なし。
コンパイラのバージョンは上がっている。

perl

Mojave

1
2
3
4

This is perl 5, version 18, subversion 2 (v5.18.2) built for darwin-thread-multi-2level
(with 2 registered patches, see perl -V for more detail)

Copyright 1987-2013, Larry Wall

High Sierra

1
2
3
4

This is perl 5, version 18, subversion 2 (v5.18.2) built for darwin-thread-multi-2level
(with 2 registered patches, see perl -V for more detail)

Copyright 1987-2013, Larry Wall

perlのバージョンは変わらない。

PHP

Mojave

1
2
3

PHP 7.1.19 (cli) (built: Aug 17 2018 18:03:17) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2018 Zend Technologies

High Sierra

1
2
3

PHP 7.1.16 (cli) (built: Mar 31 2018 02:59:59) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2018 Zend Technologies

細かいバージョンが上がっている。

Ruby

Mojave

1	ruby 2.3.7p456 (2018-03-28 revision 63024) [universal.x86_64-darwin18]

High Sierra

1	ruby 2.3.7p456 (2018-03-28 revision 63024) [universal.x86_64-darwin17]

Rubyのバージョンは変わらない。

Mojave

1	SQLite version 3.24.0 2018-06-04 14:10:15

High Sierra

1	SQLite version 3.19.3 2017-06-27 16:48:08

細かいバージョンが上がっている。

まとめ

今回もPythonのバージョンアップはなかった。
macOSの何らかのプログラムがPython2に依存しているのだろうか。

同じタグの記事

同じカテゴリの記事

2018-08-05

sqlite3のデータ型

今まで、適当に使っていたsqlite3だが、sqlite3のデータ型は5種類あるようだ。

NULL
INTEGER
REAL
TEXT
BLOB

数値はintやnumberとして定義してしまっていたが、これは自動でINTEGERと判断されるようだ。
また、charやvarcharと定義した場合は、TEXTとして扱われるようだ。

sqlite3のデータ型の定義は厳密ではなく、可能な限り定義したタイプに変換して登録される。
データ型が異なるからといって、INSERTやUPDATEがエラーになることはない。

INTEGERの項目にはINTEGERとして登録し、
TEXTの項目にはTEXTとして登録される。
INTEGERの項目に’A’を登録しようとするとTEXTの’A’として登録される。
1.5を登録しようとすると、REALの1.5として登録される。
TEXTの項目は、TEXTに変換され登録される。
INTEGERに’1.5 ‘を登録すると、REAL 1.5 として登録され、
TEXTに’1.5 ‘を登録すると、TEXT ‘1.5 ‘として登録される。

かなり上手に型変換してくれるので、余程間違った登録さえしなければ、
数値は数値として、文字は文字として処理できる。

試しに、いろいろな値を入れてみた

データ投入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
a TEXT,
b INTEGER,
c REAL,
d BLOB,
e
);
INSERT INTO t1 VALUES ('A', 'A', 'A', 'A', 'A' );
INSERT INTO t1 VALUES ('B ', 'B ', 'B ', 'B ', 'B ' );
INSERT INTO t1 VALUES ('1', '1', '1', '1', '1' );
INSERT INTO t1 VALUES ('2 ', '2 ', '2 ', '2 ', '2 ' );
INSERT INTO t1 VALUES (' 3', ' 3', ' 3', ' 3', ' 3' );
INSERT INTO t1 VALUES (' 4 ',' 4 ',' 4 ',' 4 ',' 4 ');
INSERT INTO t1 VALUES (5, 5, 5, 5, 5 );
INSERT INTO t1 VALUES (-6, -6, -6, -6, -6 );
INSERT INTO t1 VALUES (7.1, 7.1, 7.1, 7.1, 7.1 );
INSERT INTO t1 VALUES (-8.2, -8.2, -8.2, -8.2, -8.2 );
INSERT INTO t1 VALUES (0.009,0.009,0.009,0.009,0.009);
INSERT INTO t1 VALUES (NULL, NULL, NULL, NULL, NULL );

検索

1
2
3
4
5
6
7
8
9
10
11

.header ON
.mode COLUMN
SELECT
'|' || a || '|' AS TEXT, typeof(a) AS T_TEXT,
'|' || b || '|' AS INTEGER, typeof(b) AS T_INTEGER,
'|' || c || '|' AS REAL, typeof(c) AS T_REAL,
'|' || d || '|' AS BLOB, typeof(d) AS T_BLOB,
'|' || e || '|' AS NONE, typeof(e) AS T_NONE
FROM
t1
;

結果

1
2
3
4
5
6
7
8
9
10
11
12
13
14

TEXT T_TEXT INTEGER T_INTEGER REAL T_REAL BLOB T_BLOB NONE T_NONE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
|A| text |A| text |A| text |A| text |A| text
|B | text |B | text |B | text |B | text |B | text
|1| text |1| integer |1.0| real |1| text |1| text
|2 | text |2| integer |2.0| real |2 | text |2 | text
| 3| text |3| integer |3.0| real | 3| text | 3| text
| 4 | text |4| integer |4.0| real | 4 | text | 4 | text
|5| text |5| integer |5.0| real |5| integer |5| integer
|-6| text |-6| integer |-6.0| real |-6| integer |-6| integer
|7.1| text |7.1| real |7.1| real |7.1| real |7.1| real
|-8.2| text |-8.2| real |-8.2| real |-8.2| real |-8.2| real
|0.009| text |0.009| real |0.009| real |0.009| real |0.009| real
null null null null null

まとめ

やはり、なかなかの精度で変換してくれている。
これからも使い続けようと思う。

同じタグの記事

同じカテゴリの記事

2018-08-042018-08-05

手軽なのに強力なsqlite3

適当なツールにsqlite3をよく使う。
pythonを使っていると、最初からpython3ライブラリが使えるので、テキストファイルの集計なども :memory: データベースに投入して出力すると、簡単に実装できる。

データベースを:memory:ではなく、ファイルにした場合、データの再利用が可能だ。
そのデータをちょっと見たいとき、わざわざpythonを経由して見るのは面倒なので、sqlite3コマンドでアクセスしている。

SELECTの結果

デフォルトの出力形式は見ずらい。
効率的といえば効率的かもしれないが、すべてのカラムがパイプ区切りでべったりくっついて表示されてしまう。
この設定は.modeで行う。

.mode csv

カンマ区切りで出力される。特定のカラムに絞ったデータをCSVで出力して、次の処理を行うのも良いかもしれない。

.mode tabs

タブ区切りで出力される。データにカンマが含まれることも多いと思うので、こちらで出力しておいた方が、次の利用に問題が出ないかもしれない。

.mode ascii

カラムの区切り文字が0x1F、行の区切り文字が0x1Eで出力される。
データにカンマもタブも含まれている場合は、この形式が良いと思う。

.mode html

html形式で出力される。使えないこともない。

.mode insert TABLENAME

insert文として出力される。これをファイルに出力して、特定のデータのみを移行するというのにも使える。
TABLENAMEはINSERT文のINSERT先のテーブル名として利用される。
指定しないとINSERT先がTABLEになってしまう。

.mode column

コンソール出力で見栄えが良いように出力してくれる。
OracleのSQL*Plusのような感じか。
しかし、.widthと組み合わせて使わないと、カラムのデータが途中で区切れてしまうことがある。要注意。
.widthはcolumnの先頭から文字数をスペース区切りで指定していく。
.width 10 10 10 20 20 20

日本語が登録されたカラムがあると、文字数でカウントされ縦位置がずれてしまうので注意。
数値とコードのみの、トランザクションテーブルなら使いやすい。

.header on

1行目にカラム名が表示されるようになる。
これはよく使う。

.output FILENAME

SELECT結果の出力先をファイルにする。
.modeをasciiやinsert、htmlに設定した場合は、.outputをファイルにしておく。
終わったら、「.output stdout」として、出力先をコンソールに戻しておく。

その他、たまに利用する設定

.timer on

処理時間が表示されるようになる。
SQL*PlusのSET TIMING ONのような感じ。
sqlite3で性能問題に当たるような、大作SQLを作ったことがないので、あまり気にしないが。

.show

変更した設定値の一覧が確認できる。

.table

テーブル一覧の表示。

.read FILENAME

外部ファイルを読み込んで実行する。
.mode insertで出力した結果を取り込むのに使える。

普段、何も考えずによく使う使い方

1
2
3
4

sqlite> .header on
sqlite> .mode column
sqlite> select * from test_table limit 10;
sqlite> ...

まとめ

簡単に使えてとても便利。それにINSERTとSELECTはとても速い。
INSERTは1件コミットにするととても遅いので注意。
SELECTはINDEXを効かせれば、数万レコードのテーブルでも一瞬で返ってくる。
ツールもなかなか使えるし、手放せないデータベースになってきた。

最後に、sqlite3コマンド抜けるのは.quit（または.exit）。

同じタグの記事

同じカテゴリの記事

2017-08-19

DBD::SQLite::db do failed: unable to open database file at の対応

macのapacheでsqlite3を使おうとした時、DBD::SQLite::db do failed: unable to open database file at のエラーが発生した。
検索はできているので、ファイルは見つかっているよう。
INSERTやUPDATEでエラーが発生している。

原因

sqlite3のデータベースは更新時にジャーナルファイルを作成する。
そのジャーナルファイルが作成できない場合に発生する。

対策

sqlite3のデータベースファイルの保存フォルダにアクセス権限を設定する。

1	chmod 777 db_dir

同じタグの記事

同じカテゴリの記事