Zenn
👩‍🦽

SQLite3 からデータを postgresql に ... Amazon Fire HD 10

2021/07/30に公開
#
sqlite3
tech

SQLite3 でデータベースをつくって、データを書き込んでいくと件数が多くなって、ネットワークで複数からデータの追加と参照ができるほうがよいことに気がつく。

こわごわデータを postgresql に持ってくる。

Amazon Fire HD 10を使い、Termux で行う。

Termux での postgresql のセットアップはこちらを参考にした。
https://wiki.termux.com/wiki/Postgresql
$PREFIX についてはこちら
https://wiki.termux.com/wiki/Differences_from_Linux
インストールについて補足になる postgresql のクラスタということばについてと、インストール、スタートなどの postgresql のマニュアルの該当部分。
https://www.postgresql.jp/document/11/html/creating-cluster.html

SQLite3 については、Ruby,Python のモジュール版と、Termux の pkg からインストールしたものを使用しているが、データ移行には SQLite3 本体を使った。

u0_a185@localhost ~> pkg show sqlite
Package: sqlite
Version: 3.36.0
Maintainer: @termux
Installed-Size: 844 kB
Depends: readline, libsqlite (= 3.36.0)
Homepage: https://www.sqlite.org
Download-Size: 474 kB
APT-Manual-Installed: yes
APT-Sources: https://packages.termux.org/apt/termux-main stable/main arm Packages
Description: Command line shell for SQLite

sqlite で pkg search すると見つかる。Ruby,Python のモジュール版（言語に属すライブラリのこと）との違いは、sqlite3 単体でターミナルで起動して使えることで、SQL 文の命令や sqlite3 の独自のコマンドも最小限の長さでかけて実行できること。モジュール版だと Ruby、 python のプログラミング文で書くことになるのと、そのプログラムを実行しなければ結果が取り出せないので、最小限で済ませたい場合は単体でいい。

u0_a185@localhost ~> pkg install sqlite

流れをまず理解しているといいはずなので、何をどうするかを説明。

Sqlite3 のモジュール版を使ってデータベースをつくったファイルから、データを Postgresql へ移行する。

  • SQLite3 でテーブルの全データを、CSV にエクスポートする。
  • そのエクスポートされた CSV を postgresql で新たにつくって用意するテーブルにSQLite3 のデータベースと同じ構造でカラムをつくり、そこに読み込む。

流れとしてはこうなっている。
データベースのダンプファイルを扱う方法もあるが、今回は CSV ファイルを使う方法で事足りた。なので、ここでは CSV での方法のみを扱う。
はじめての場合、コマンドや Postgresql 機能に不馴れなために、期待通りの動作を得るまでにとても時間がかかるかもしれない。
実際に、朝からトライして、夜の８時になってはじめて完了した。
これは参照したドキュメントが、その通りに操作しても現在の postgresql のコマンドの動作とは違っていたということが原因だろうが、はじめての場合は大変迷うので、にたような経験になるのではないかと思う。

説明することは、うまくできたところだけになるので、限定的である。
ひととおりわかれば、何時間もかかることではなく、数分で完了できるようになる。SQL 自体は単純なコマンドの集合で操作するものなので、わかっていることが増えればミニマムでいいが、はじめてやることについては、コマンドがあっているのか？とか、コマンドの並びが違うなど、わかってなければ分かりようのないことも無機質に動作を拒否されるだけなので、ハマりやすいということ。ずいぶんハマったので、これを参考にできるひとにはそのようなことが起こらず次に進められるといいなと思う。ハマったとしても、少し時間をかけて可能性をつぶしていけば、必ず抜けれると思う。そうは深くないので、落ち着けばなんとかなる。

使用したのは、なんら変哲のないこのようなデータである。
SQLite3 の形式だが、通常のレコード件数のリミットは越えていると思われる。

~ $ fish
Welcome to fish, the friendly interactive shell
Type help for instructions on how to use fish
u0_a185@localhost ~> sqlite3 ./pytest/mangaB_python.db

home ディレクトリの下に pytest というディレクトリがあり、ここにSQLite3 の形式の db ファイルがあるとする。u0_a185 はタブレットの OS のユーザー名で、Termux においては root 相当であるけれども OS はルート化されているわけではない。

冗長になるがはしょらずに記載する方が、省略してわかちで記載するより、やや分かりやすいかもしれないので、そのようにしてみる。

~ $ fish
Welcome to fish, the friendly interactive shell
Type help for instructions on how to use fish
u0_a185@localhost ~> sqlite3 ./pytest/mangaB_python.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .table
tbl_manga
sqlite> .databases
main: /data/data/com.termux/files/home/pytest/mangaB_python.db r/w
sqlite>

.table コマンド（SQLite3）で接続した db ファイルのテーブル名を確認している。

sqlite> .table
tbl_manga
sqlite> .databases
main: /data/data/com.termux/files/home/pytest/mangaB_python.db r/w
sqlite> .schema
CREATE TABLE tbl_manga (
    id INTEGER PRIMARY KEY,
    title text,
    url text,
    updated_datetime datetime,
    author text,
    book_title text,
    tags text
    );
sqlite>

テーブルのカラムを見ると、このようになっていて、postgresql では datetime 型はなく、対応するのは timestamp 型になるので、postgresql でテーブルを用意するときには updated_datetime カラムは timestamp にする。あとは、変更が要らなさそうである。
INTEGER PRIMARY KEY が int serial に変更する方がよいかという気がしたが、このままでもおかしくはならなかった。

sqlite> select count(id) from tbl_manga;
56332

id は 1 から番号を割り付けている。 1 ~ 56332 まで、id, title, url, updated_datetime, author, book_title, tags というカラムに値のデータが入っている。

sqlite> .schema
CREATE TABLE tbl_manga (
    id INTEGER PRIMARY KEY,
    title text,
    url text,
    updated_datetime datetime,
    author text,
    book_title text,
    tags text
    );
sqlite> select count(id) from tbl_manga;
56332
sqlite> .headers off
sqlite> .mode csv
sqlite> .once 'tbl_manga.csv'
sqlite> select * from tbl_manga;
sqlite>

.headers off は、CSV の一行目に header をつけるか否かの指定だが、ここは要らないので、off 。デフォルトでは off になっているらしいので、指定する必要はないが、反対は、 on ということになる。on でもかまわないが、その際は、 postgresql で読み込むときに一行目をスキップする必要がでてくる。
.once 'tbl_manga.csv'　は、tbl_manga.csv というファイルにする前に、直後にある SQL 文の
select * from tbl_manga;
を実行してから、ファイルにエクスポートするという意味である。

sqlite> select count(id) from tbl_manga;
56332
sqlite> .headers off
sqlite> .mode csv
sqlite> .once 'tbl_manga.csv'
sqlite> select * from tbl_manga;
sqlite> .quit
u0_a185@localhost ~> find tbl_manga.csv
tbl_manga.csv
u0_a185@localhost ~>

以上で、SQLite3 から CSV へのテーブルの内容をエクスポートできた。

つぎは、 Postgresql で CSV を読み込む。
Termux の pkg で postgresql はすでに済ましているとする。
インストールについてはこちら。（再掲）
https://wiki.termux.com/wiki/Postgresql

pg_ctl -D $PREFIX/var/lib/postgresql startでスタート

sqlite> .quit
u0_a185@localhost ~> find tbl_manga
tbl_manga
u0_a185@localhost ~> pg_ctl -D $PREFIX/var/lib/postgresql start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2021-07-30 19:39:27.405 JST [17174] LOG:  starting PostgreSQL 13.3 on arm-unknown-linux-androideabi, compiled by Android (6454773 based on r365631c2) clang version 9.0.8 (https://android.googlesource.com/toolchain/llvm-project 98c855489587874b2a325e7a516b99d838599c6f) (based on LLVM 9.0.8svn), 32-bit
2021-07-30 19:39:27.407 JST [17174] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-07-30 19:39:27.413 JST [17174] LOG:  listening on Unix socket "/data/data/com.termux/files/usr/tmp/.s.PGSQL.5432"
2021-07-30 19:39:27.441 JST [17178] LOG:  database system was interrupted; last known up at 2021-07-29 23:45:13 JST
2021-07-30 19:39:27.662 JST [17178] LOG:  database system was not properly shut down; automatic recovery in progress
2021-07-30 19:39:27.667 JST [17178] LOG:  redo starts at 0/7F1CF90
2021-07-30 19:39:27.667 JST [17178] LOG:  invalid record length at 0/7F1D0B0: wanted 24, got 0
2021-07-30 19:39:27.667 JST [17178] LOG:  redo done at 0/7F1D078
2021-07-30 19:39:27.697 JST [17174] LOG:  database system is ready to accept connections
 done
server started
u0_a185@localhost ~> createdb tbl_manga
2021-07-30 19:40:27.803 JST [17237] ERROR:  database "tbl_manga" already exists
2021-07-30 19:40:27.803 JST [17237] STATEMENT:  CREATE DATABASE tbl_manga;
createdb: error: database creation failed: ERROR:  database "tbl_manga" already exists
u0_a185@localhost ~ [1]> psql tbl_manga
psql (13.3)
Type "help" for help.

tbl_manga=# create TABLE IF not EXISTS tbl_manga(
id integer,
title text,
url text,
updated_datetime timestamp,
author text,
book_title text,
tags text);

postgresql をスタートさせて、tbl_manga という db をつくり、db を選んで、SQL 文で tbl_manga というテーブルをつくって、スキーマを定義する。
テーブルがまっさらな場合は、このような感じで準備ができる。
このテーブルに、CSV ファイルからデータを読み込む。

tbl_manga=# \copy tbl_manga (id,title,url,updated_datetime,author,book_title,tags) from './pytest/tbl_manga.csv' with csv ;

注意するのは、必ずバックスラッシュからコマンドを始めること。
日本語キーボードのバックスラッシュ代替えの￥マークだとコマンドが通らなかった（ Termux 環境では ） 。
\copy テーブルの名前 (カラム) from ファイルパス with CSV ;
という文になっている。
ここでは db 名をテーブルの名前と同じにしてしまったので、db 名を指定してると誤解するかもしれないが、すでに db のなかにいるので、ここで \copy のあとに指定しているのはテーブルの tbl_manga であると理解して欲しい。

tbl_manga=# \copy tbl_manga (id,title,url,updated_datetime,author,book_title,tags) from './pytest/tbl_manga.csv' (delimiter ',',format CSV);

でもいい。CSV ファイルをつくるときに .headers on にしていた場合は、

tbl_manga=# \copy tbl_manga (id,title,url,updated_datetime,author,book_title,tags) from './rubytest/tbl_manga.csv' (delimiter ',',format CSV,header);

または、

tbl_manga=# \copy tbl_manga (id,title,url,updated_datetime,author,book_title,tags) from './pytest/tbl_manga.csv' with csv header;

でヘッダの一行がスキップされる。

チェック

u0_a185@localhost ~> psql postgres
psql (13.3)
Type "help" for help.
postgres=# \l

postgres=# \q
u0_a185@localhost ~> psql tbl_manga

tbl_manga=# select count(*) from tbl_manga ;                                                                 count
-------
 56332
(1 row)

local host で postgresql サーバーが動いている状況であれば、デフォルトでは、パスワード、ユーザー指定は要らないらしい。指定していない。
Ruby ライブラリ pg を使ってテスト。

irb(main):007:0> require 'pg'
=> true
irb(main):008:0> conn = PG.connect( dbname: 'tbl_manga' )
=> #<PG::Connection:0xe4baeef8>
irb(main):009:0> res = conn.exec("select id,tags from tbl_manga where id<10 ;")
=> #<PG::Result:0xe429ee00 status=PGRES_TUPLES_OK ntuples=9 nfields=2 cmd_tuples=9>
irb(main):010:1* res.each do |data| p data
irb(main):011:0> end
{"id"=>"1", "tags"=>"WATANABE,Weak Pedals,Yowamushi Pedal,スポーツ・格闘技,一般コミック,学園,少年漫画,弱虫ペダル,渡辺航,週刊少年チャンピオン"}
{"id"=>"2", "tags"=>"MFゴースト,NeuN〈ノイン〉,Weekly Young Magazine,アルキメデスの大戦,アンダーニンジャ,おたくの隣りはエルフですか？,キッズファイヤー・ドットコ ム,ギャルと恐竜,ザ・ファブル,サタノファニ,センゴク権兵衛,ソウナンですか？,テッペン 〜那須川天心物語〜,なんでここに先生が！？,パラレルパラダイス,ハレ婚。,ハンツー×トラッシュ,マイホームヒーロー,みなみけ,一般コミック,不死身の特攻兵 生キトシ生ケル者タチヘ,乃木坂の詩,喧嘩稼業,変な知識に詳しい彼女 高床式草子さん,夫のちんぽが入らない,契れないひと,少年漫画,彼岸島 48日後…,手品先輩,新・信長公記 〜ノブナガくんと私〜,皆殺しのアーサー,神さまの恋人,私の少年,賭博堕天録カイジ 24億脱出編,週刊ヤングマガジン,雑誌,雪女と蟹を食う,青年漫画,首を斬らねば分かるまい"}
{"id"=>"3", "tags"=>"MFゴースト,NeuN〈ノイン〉,Weekly Young Magazine,アルキメデスの大戦,アンダーニンジャ,おたくの隣りはエルフですか？,キッズファイヤー・ドットコ ム,ギャルと恐竜,ザ・ファブル,サタノファニ,センゴク権兵衛,ソウナンですか？,テッペン 〜那須川天心物語〜,なんでここに先生が！？,パラレルパラダイス,ハレ婚。,ハンツー×トラッシュ,マイホームヒーロー,みなみけ,一般コミック,不死身の特攻兵 生キトシ生ケル者タチヘ,乃木坂の詩,喧嘩稼業,変な知識に詳しい彼女 高床式草子さん,夫のちんぽが入らない,契れないひと,少年漫画,彼岸島 48日後…,手品先輩,新・信長公記 〜ノブナガくんと私〜,皆殺しのアーサー,神さまの恋人,私の少年,賭博堕天録カイジ 24億脱出編,週刊ヤングマガジン,雑誌,雪女と蟹を食う,青年漫画,首を斬らねば分かるまい"}
{"id"=>"4", "tags"=>"少年漫画,空男,糸川一成,青年漫画"}
{"id"=>"5", "tags"=>"少年漫画,空男,糸川一成,青年漫画"}
{"id"=>"6", "tags"=>"少年漫画,空男,糸川一成,青年漫画"}
{"id"=>"7", "tags"=>"少年漫画,空男,糸川一成,青年漫画"}
{"id"=>"8", "tags"=>"少年漫画,空男,糸川一成,青年漫画"}
{"id"=>"9", "tags"=>"少年漫画,空男,糸川一成,青年漫画"}
=> #<PG::Result:0xe429ee00 status=PGRES_TUPLES_OK ntuples=9 nfields=2 cmd_tuples=9>
irb(main):012:0>

Discussion