SQLite
SQLiteは、MySQLのようなサーバー・クライアント方式ではなく、アプリケーションに直接組み込んで使用することができる関係データベース管理システム (RDBMS) です。 身近なところでは、Google ChromeやMozilla Firefoxなどの多くのアプリケーションで使用されており、Google AndroidではOSに組み込まれています。 これにより、SQLiteは世界で最もインストールベースの多いRDBMSの1つとして認知されています。
SQLiteについて
編集SQLiteは、高速かつ軽量なリレーショナルデータベース管理システムです。SQLiteは、コンパクトなライブラリとして提供され、モバイルデバイス、デスクトップアプリケーション、Webブラウザ、組み込みシステムなど、あらゆるプラットフォームで使用されています。
SQLiteは、標準のSQLをサポートし、トランザクション処理、データの完全性維持、およびACID(原子性、一貫性、分離性、耐久性)の保証を提供します。また、多くの主要なプログラミング言語(C、Java、Python、PHPなど)で利用可能であり、簡単に統合することができます。
SQLiteの最も重要な特徴の一つは、ファイルベースのデータベースであることです。つまり、データはファイルに保存され、ファイルを別の場所にコピーするだけで、データを移行したりバックアップしたりすることができます。また、複数のプロセスが同時にデータベースにアクセスすることができるため、アプリケーションのパフォーマンスが向上します。
SQLiteは、他のリレーショナルデータベース管理システムと比較して、データベースが比較的小規模である場合に最適です。大量のデータを処理する必要がある場合や、高い同時アクセスが必要な場合には、より高機能なデータベースシステムを検討する必要があります。
ただし、SQLiteは非常に高速であるため、多くの場合、大量のデータを処理する必要がある場合でも、十分なパフォーマンスを発揮します。SQLiteはまた、カスタム拡張機能を提供することができ、標準のSQLコマンド以外の機能を簡単に実装することができます。
SQLiteは、プログラマにとって非常に使いやすく、学びやすいです。SQLの基本構文を理解しているだけで、すぐにSQLiteを利用することができます。また、SQLiteは非常に広く利用されているため、オンラインで豊富な情報を見つけることができます。
インストール
編集本書では、SQLite version3の保守管理用のコマンドラインインターフェース sqlite3
の操作を中心に、SQLiteの機能の解説を行いますので、まずsqlite3
が、ご自身の環境にインストールされているか確認します。
- コマンドラインでsqlite3のバージョンを確認(インストールされている場合)
$ sqlite3 -version 3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1 (64-bit)
-version
の前の-
(ハイフン)は1つです。- バージョンは異なるかもしれませんが、この書式で1行表示されたら sqlite3 はインストールされています。
- その場合は、追加のインストール手順は不要ですが、2フィールド目の日付が年オーダーで古い場合は、よりあらたしいバージョンの入手も検討してください。
- コマンドラインでsqlite3のバージョンを確認(インストールされていない場合)
$ sqlite3 -version bash: sqlite3: command not found
- メッセージの細部は異なるかもしれませんが、”command not found” や ”コマンドまたはファイル名が違います” のようなメッセージが返された場合は、 sqlite3 はインストールされていません。
- その場合は、下記のインストール手順が必要です。
Microsoft Windows
編集SQLite公式(ダウンロードページ)の Precompiled Binaries for Windows にある sqlite-tools-win-x64-3470000.zip(6.188 MiB)をダウンロードします(3470000の部分はバージョンによって変わります。この場合は、3.47.0)。
sqlite-tools-win-x64-3450000.zipは、ZIP書庫で
$ unzip -l sqlite-tools-win-x64-3470000.zip Archive: sqlite-tools-win-x64-3470000.zip Length Date Time Name --------- ---------- ----- ---- 3155968 2024-10-22 09:00 sqldiff.exe 4422144 2024-10-22 09:00 sqlite3_analyzer.exe 3848704 2024-10-22 08:59 sqlite3.exe 2959872 2024-10-22 19:40 sqlite3_rsync.exe --------- ------- 14386688 4 files
- と4つの実行形式があります。
- これらはインストーラーではなく、コマンドそのものです。
- このまま実行できます。
Apple macOS
編集2022年11月現在サポートが継続されている macOS には、最初からインストールされています。 Terminalを起動してバージョンを確認してください。
% sqlite3 --version 3.28.0 2019-04-15 14:49:49 378230ae7f4b721c8b8d83c8ceb891449685cd23b1702a57841f1be40b5daapl
-version
の前の-
(ハイフン)は1つです。
FreeBSD
編集ports/pkg の databases/sqlite3 にあります。
- 自分でビルド
% sudo make -C /usr/ports/databases/sqlite3/ all install clean ... % sqlite3 -version 3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1 (64-bit)
-version
の前の-
(ハイフン)は1つです。
- ビルド済パッケージを導入
% sudo pkg install sqlite3-3.46.1,1 ... % sqlite3 -version 3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1 (64-bit)
GNU/Linux
編集GNU/Linuxの場合は、ディストーションやパッケージマネージャーによってパッケージの名前やインストール方法が異なるので以下は一例です。
Google ChromeOS/Chromebrew
編集Chromebrewは、sqlite
の名前で SQLite のパッケージが用意されています。
$ crew install -sv sqlite
...
$ sqlite3 -version
3.42.0 2023-05-16 12:36:15 831d0fb2836b71c9bc51067c49fee4b8f18047814f2ff22d817d25195cf3alt1
-version
の前の-
(ハイフン)は1つです。
Debian
編集$ sudo apt-get install sqlite3
...
$ sqlite3 -version
3.40.1 2022-12-28 14:03:47 df5c253c0b3dd24916e4ec7cf77d3db5294cc9fd45ae7b9c5e82ad8197f3alt1
-version
の前の-
(ハイフン)は1つです。
SQLiteは、アプリケーションプログラムにライブラリーとしてリンクされ、単一のファイルにデータベースを構築します。 このため、MySQLのようにサーバー・クライアントモデルで必要な、クライアントからのデータベース・サーバーへの接続・認証は必要なく、データベースファイルをオープンし使用を開始します。
コマンドラインインターフェース
編集前述の通り、SQLiteはアプリケーションプログラムに組込まれて使用されますが、データベースの保守管理は必要なので、その用途にコマンドラインインターフェースアプリケーション sqlite3
が用意されています。
操作と結果概観
編集個別のコマンドの説明を列挙して解説する前に、実際に sqlite3
を使って
- データベースを開く
- テーブルを定義する
- テーブルにレコードを挿入する
- データを抽出する
などの操作を script(1) を使って記録して様子を掲載します。
- typescript
$ cd $ mkdir sqlite/ $ cd sqlite/ $ sqlite3 periodic.db SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. sqlite> -- sqlite> CREATE TABLE elements ( number, name, symbol ) ; sqlite> INSERT INTO element (number, name, symbol) VALUES(1, "Hydrogen", "H"); Parse error: no such table: element sqlite> INSERT INTO elements (number, name, symbol) VALUES(1, "Hydrogen", "H"); sqlite> SELECT * FROM elements; 1|Hydrogen|H sqlite> .header on sqlite> SELECT * FROM elements; number|name|symbol 1|Hydrogen|H sqlite> INSERT INTO elements (number, name, symbol) VALUES(2, "Helium"); Parse error: 2 values for 3 columns sqlite> INSERT INTO elements (number, name) VALUES(2, "Helium"); sqlite> SELECT * FROM elements; number|name|symbol 1|Hydrogen|H 2|Helium| sqlite> SELECT number,TYPEOF(number),name,TYPEOF(name),symbol,TYPEOF(symbol) FROM elements; number|TYPEOF(number)|name|TYPEOF(name)|symbol|TYPEOF(symbol) 1|integer|Hydrogen|text|H|text 2|integer|Helium|text||null sqlite> DELETE FROM elements WHERE name = "Helium" ; sqlite> SELECT number,TYPEOF(number),name,TYPEOF(name),symbol,TYPEOF(symbol) FROM elements; number|TYPEOF(number)|name|TYPEOF(name)|symbol|TYPEOF(symbol) 1|integer|Hydrogen|text|H|text sqlite> .schema CREATE TABLE elements ( number, name, symbol ); sqlite> .databases main: /home/user1/sqlite/periodic.db r/w sqlite> .dump elements PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE elements ( number, name, symbol ); INSERT INTO elements VALUES(1,'Hydrogen','H'); COMMIT; sqlite> ^D $ ls -l total 20 -rw-r--r--. 1 chronos chronos 8192 Nov 24 07:19 periodic.db $ file periodic.db periodic.db: SQLite 3.x database, last written using SQLite version 3040000, file counter 4, database pages 2, cookie 0x1, schema 4, UTF-8, version-valid-for 4
個別のコマンドの説明
編集データベースを開く
編集SQLiteのデータベースは、単一のファイルです。 なのでデータベースへは、S/Cモデルのように接続するのではなくデータベースファイルを開きます。
sqlite3起動時にデータベースファイルを開く
編集sqlite3
の起動時パラメーターにデータベースファイルを与えると、データベースがオープンした状態で起動します。
- sqlite3起動時にデータベースファイルを開く
$ sqlite3 periodic.db SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. sqlite> .databases main: /home/user1/sqlite/periodic.db r/w sqlite>
- このとき、データベースファイルがない場合、新しいデータベースファイルが作られます。
- .databasesは、オープン中のデータベースの一覧を表示する sqlite3 のコマンドです。
.open
編集sqlite3
をパラメータなしで起動したあと、.open
コマンドでデータベースファイルを開きます。
- sqlite3を起動するときにデータベースファイルを与え開く
$ sqlite3 SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. sqlite> .open periodic.db sqlite> .databases main: /home/user1/sqlite/periodic.db r/w sqlite>
- このとき、データベースファイルがない場合、新しいデータベースファイルが作られます。
$ sqlite3 sqlite>
- が、sqlite3 のプロンプトです。
.databases
編集- 書式
.databases
- 例
sqlite> .databases main: /home/user1/sqlite/sample.db r/w
- 同義のSQL
PRAGMA database_list;
.databases
コマンドは、現在開いているデータベースに関する情報を表示します。.databases
までタイプしなくても、.dat
で同じ意味になります。- これは、
.databases
にかぎらず、多くのコマンドで. を含め先頭4文字までタイプすればコマンドの別名として受け付けられます。
.open
や .databases
は、SQLではなくsqlite3のコマンドで、SQLと異なり大文字小文字を区別します。
sqlite> .OPEN periodic.db Error: unknown command or invalid arguments: "OPEN". Enter ".help" for help sqlite> _
.help
編集sqlite
のオンラインヘルプコマンドは.help
です。下記に3.40.0の.helpを示します(なるべく解説へのリンクにしました)
- .help
$ sqlite3 /dev/null .help .archive ... Manage SQL archives .auth ON|OFF Show authorizer callbacks .backup ?DB? FILE Backup DB (default "main") to FILE .bail on|off Stop after hitting an error. Default OFF .binary on|off Turn binary output on or off. Default OFF .cd DIRECTORY Change the working directory to DIRECTORY .changes on|off Show number of rows changed by SQL .check GLOB Fail if output since .testcase does not match .clone NEWDB Clone data into NEWDB FROM the existing database .connection [close] [#] Open or close an auxiliary database connection .databases List names and files of attached databases .dbconfig ?op? ?val? List or change sqlite3_db_config() options .dbinfo ?DB? Show status information about the database .dump ?OBJECTS? Render database content as SQL .echo on|off Turn command echo on or off .eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN .excel Display the output of next command in spreadsheet .exit ?CODE? Exit this program with return-code CODE .expert EXPERIMENTAL. Suggest indexes for queries .explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto .filectrl CMD ... Run various sqlite3_file_control() operations .fullschema ?--indent? Show schema and the content of sqlite_stat tables .headers on|off Turn display of headers on or off .help ?-all? ?PATTERN? Show help text for PATTERN .import FILE TABLE Import data FROM FILE into TABLE .imposter INDEX TABLE Create imposter table TABLE on index INDEX .indexes ?TABLE? Show names of indexes .limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT .lint OPTIONS Report potential schema issues. .load FILE ?ENTRY? Load an extension library .log FILE|off Turn logging on or off. FILE can be stderr/stdout .mode MODE ?OPTIONS? Set output mode .nonce STRING Suspend safe mode for one command if nonce matches .nullvalue STRING Use STRING in place of NULL VALUES .once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE .open ?OPTIONS? ?FILE? Close existing database and reopen FILE .output ?FILE? Send output to FILE or stdout if FILE is omitted .parameter CMD ... Manage SQL parameter bindings .print STRING... Print literal STRING .progress N Invoke progress handler after every N opcodes .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILE Read input FROM FILE or command output .recover Recover as much data as possible FROM corrupt db. .restore ?DB? FILE Restore content of DB (default "main") FROM FILE .save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...) .scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off .schema ?PATTERN? Show the CREATE statements matching PATTERN .selftest ?OPTIONS? Run tests defined in the SELFTEST table .separator COL ?ROW? Change the column and row separators .session ?NAME? CMD ... Create or control sessions .sha3sum ... Compute a SHA3 hash of database content .shell CMD ARGS... Run CMD ARGS... in a system shell .show Show the current VALUES for various settings .stats ?ARG? Show stats or turn stats on or off .system CMD ARGS... Run CMD ARGS... in a system shell .tables ?TABLE? List names of tables matching LIKE pattern TABLE .testcase NAME Begin redirecting output to 'testcase-out.txt' .testctrl CMD ... Run various sqlite3_test_control() operations .timeout MS Try opening locked tables for MS milliseconds .timer on|off Turn SQL timer on or off .trace ?OPTIONS? Output each SQL statement as it is run .vfsinfo ?AUX? Information about the top-level VFS .vfslist List all available VFSes .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set minimum column widths for columnar output $
- 全部のコマンドを覚える必要はありませんが、「○○○なる機能はない」という前に確認しましょう。
データベースファイルの拡張子
編集SQLiteのデータベースファイルの拡張子には、.db
、.sqlite
や.sqlite3
が用いられます。
SQLite自身は、拡張子によって動作を変えるようなことはしていないので、どのような拡張子でも構いませんが、オペレーションシステムの拡張子とファイルタイプの関連付けと重複しないように注意が必要です。
ちなみにSQLiteバージョン3のデータベースファイルのIANAメディアタイプは、application/vnd.sqlite3
です[1]。
- file(1)が返す情報
$ file periodic.db periodic.db: SQLite 3.x database, last written using SQLite version 3040000, file counter 4, database pages 2, cookie 0x1, schema 4, UTF-8, version-valid-for 4 $ _
- どれがSQLiteのデータベースファイルかわからなくなったときに役に立ちます。
終了コマンド
編集sqlite3
は、.exit
、.quit
または^D
(EOF)で終了します。
- 書式
.exit .quit ^D (ctrl-D)
- 例
$ sqlite3 SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .quit $
- sqlite3 を起動してすぐ終了。
複数行入力モードからの回復
編集タイプミスなどで複数行入力モードに入ることがあります
$ sqlite3 SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> open periodic.db ...> ; Parse error: near "open": syntax error open periodic.db ; ^--- error here sqlite>
- タイプミスの場合どのみち syntax error なので
;
(セミコロン)を1文字入力します。
このほか、文字列の ” や ’ が閉じられていない場合も考えられます。
テーブル定義
編集テーブルを定義する場合は CREATE TABLE コマンドを使います。
CREATE TABLE
編集原子番号 | 元素名英名 | 元素記号 |
---|---|---|
number | name | symbol |
1 | Hydrogen | H |
2 | Helium | He |
3 | Lithium | Li |
4 | Beryllium | Be |
5 | Boron | B |
6 | Carbon | C |
7 | Nitrogen | N |
8 | Oxygen | O |
9 | Fluorine | F |
10 | Neon | Ne |
11 | Sodium | Na |
12 | Magnesium | Mg |
- 書式
CREATE TABLE テーブル名( カラム名1 [ 型1 ], カラム名2 [ 型2 ], カラム名2 [ 型3 ] … カラム名n [ 型n ]) ;
- 例
CREATE TABLE elements( number, name, symbol );
- 最後の ;(セミコロン)を忘れがちです。気をつけましょう。
- ここでは「型」を省略しています。
テーブル削除
編集テーブルを削除する場合は DROP TABLE コマンドを使います。
DROP TABLE
編集[TODO]
カラムの型
編集SQLiteの型システムは、標準SQLとはポリシーが異なっており、カラムに定義された型と異なる型の値が与えられると、可能な限り情報を落とさないように、場合によっては型を変えて保存します。
$ sqlite3 sqlite> CREATE TABLE test(r1, r2); sqlite> INSERT INTO test VALUES(null, 42); sqlite> INSERT INTO test VALUES(2.73, "Shizuoka" ); sqlite> INSERT INTO test VALUES("Hello", 1e-10); sqlite> SELECT * FROM test; |42 2.73|Shizuoka Hello|1.0e-10 sqlite> SELECT r1, TYPEOF(r1), r2, TYPEOF(r2) FROM test; |null|42|integer 2.73|real|Shizuoka|text Hello|text|1.0e-10|real sqlite> CREATE TABLE test2(r1 TEXT, r2 NUMERIC); sqlite> INSERT INTO test2 VALUES(null, 42); sqlite> INSERT INTO test2 VALUES(2.73, "Shizuoka" ); sqlite> INSERT INTO test2 VALUES("Hello", 1e-10); sqlite> SELECT r1, TYPEOF(r1), r2, TYPEOF(r2) FROM test2; |null|42|integer 2.73|text|Shizuoka|text Hello|text|1.0e-10|real sqlite>
TYPEOF(カラム名)
関数でレコードのカラムの型(実際の型)を参照できます。- REALに変換できればREALで保存されます。
- INTEGERに変換できればINTEGERで保存されます。
- それ以外は、TEXTで保存されます。
これは、テーブル定義と異なる型の値がカラムに与えられた場合、SQLiteが情報を落としてしまうと、SQLiteを利用するアプリケーションプログラムが、適切な判断を行う手段を失ってしまうからです。
このような設計上に選択にも、SQLiteが単体のデータベースシステムではなく、アプリケーションプログラムに組込まれるRDBMSエンジンであるという立ち位置の特徴が現れています。
テーブルへのデータの登録
編集ではさっそく、1番目の水素のデータをつけたしてみましょう。
INSERT INTO
編集- 書式
INSERT INTO テーブル ( カラム1, カラム2, カラム3, … カラムn ) VALUES ( 値1, 値2, 値3, … 値n ) ;
- 例
INSERT INTO elements (number, name, symbol) VALUES(1, "Hydrogen", "H" );
データの抽出
編集SELECT
編集- 書式
SELECT カラムリスト FROM テーブル ;
- 例
sqlite> select name FROM elements; Hydrogen sqlite> select name,number FROM elements; Hydrogen|1 sqlite> select symbol,name,number FROM elements; H|Hydrogen|1 sqlite> SELECT * FROM elements; 1|Hydrogen|H
- カラムリストに
*
を与えると「すべてのカラム」となります。
.header
編集SELECT
コマンドの結果にカラムの名前を含めるか含めないかは、 .header
コマンドで切替えできます。
- 書式
.header on .header off
- 例
sqlite> .header on sqlite> select symbol,name,number FROM elements; symbol|name|number H|Hydrogen|1
.header on
でSELECTの結果の1行目にカラムリストを表示します.header off
でSELECTの結果の1行目にカラムリストを表示しません。- ディフォルト は、
.header off
。 - on は、1, true, yes の3つの別名を持ちます。
- off は、0, false, no の3つの別名を持ちます。
未入力カラムのあるレコードの挿入
編集レコード(行)を挿入するとき、何らかの理由で全カラムを埋めることができなかった場合は、一部のカラムだけからなるレコードを挿入することもできます。 また、テーブル定義でカラムをNOT NULL制約することでレコードを挿入するときに必須のカラムを設け、入力がない場合をエラーとし挿入を拒絶することもできあます。
- 未入力カラムのあるレコードの挿入
sqlite> INSERT INTO elements ( number, name ) VALUES ( 2, "Helium" ); sqlite> select name,number,symbol,TYPEOF(symbol) from elements; name|number|symbol|TYPEOF(symbol) Hydrogen|1|H|text Helium|2||null
- 未入力の2列目のsymbolの値は””、型は
null
となっているのがわかります - TYPEOF()はカラムの「型」を返す関数です。
行の削除
編集行の削除は、DELETEコマンドとWHERE演算子を併用します。
- 書式
DELETE FROM テーブル WHERE カラム = 値 ;
DELETE
編集ヘリウムのnameデータ ”Helium” の行(レコード)を削除
sqlite> SELECT * FROM elements; 1|Hydrogen|H 2|Helium||null sqlite> DELETE FROM elements WHERE name = "Helium" ; sqlite> SELECT * FROM elements; 1|Hydrogen|H
WHERE
以降を忘れて DELETE FROM elements
とすると、elements
テーブルの全てのレコードが削除されます。テーブル定義の表示
編集.schema
コマンド(Show the CREATE statements matching PATTERN)で、CREATE
のフォーマットでテーブル定義を表示できます。
.schema
編集- 書式
.schema
- 例
sqlite> .schema CREATE TABLE elements( number, name, symbol );
- 同義のSQL
SELECT name,sql FROM sqlite_master WHERE type="table";
- sqlite_masterというテーブルがSQLiteのそれぞれのデータベースにあり、テーブルやインデックス・ビューを管理しています。
- 実際のアプリケーションでは、sqlite3のコマンドは使えないので、同じ機能のSQLを覚えていくことはとても重要です。
テーブルの一覧
編集.tables
コマンドでテーブルの一覧を表示できます。
.tables
編集- 書式
.tables
- 例
sqlite> .tables elements
- 同義のSQL
SELECT name FROM sqlite_master WHERE type="table";
- sqlite_masterというテーブルがSQLiteのそれぞれのデータベースにあり、テーブルやインデックス・ビューを管理しています。
- 実際のアプリケーションでは、sqlite3のコマンドは使えないので、同じ機能のSQLを覚えていくことはとても重要です。
バックアップ/外部出力の方法
編集SQLiteのデータベースは、1つの通常のファイルなので、バックアップはファイルをコピーするだけです。
また、sqlite3
のコマンドに、.backup
コマンドと .restore
が用意されています。
ダンプ
編集バックアップはファイルレベルのコピーでできますが、障害発生に備えてのバックアップとしては、テキストファイルへのダンプが有用です。
ダンプは、SQLiteのコマンドライン管理インターフェース sqlite3
を使います。
.dump
編集操作と結果概観で作成したデータベース periodic.db
の テーブル elements
をテキストファイルperiodic.txt
にダンプする例です。
$ sqlite3 SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open periodic.db sqlite> .table elements sqlite> .dump elements PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE elements( number, name, symbol ); INSERT INTO elements VALUES(1,'Hydrogen','H'); COMMIT; sqlite> .output periodic.txt sqlite> .dump elements sqlite> .output stdout sqlite> .quit $ cat periodic.txt PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE elements( number ,name ,symbol ); INSERT INTO elements VALUES(1,'Hydrogen','H'); COMMIT; $ _
sqlite> .open periodic.db
.open
コマンドで、データベースperiodic.db
を開いています。$ sqlite3 periodic.db
- と、最初にコマンドラインからデータベースを指定して開くこともできます。
sqlite> .table elements
.table
コマンドで、開いているデータベースに含まれるテーブル名を表示します。.dump elements PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE elements( number ,name ,symbol ); INSERT INTO elements VALUES(1,'Hydrogen','H'); COMMIT;
.dump
コマンドで、テーブルelements
をダンプしています。既定の出力先は標準出力です。sqlite> .output periodic.txt
- 出力先をファイル
periodic.txt
に変更。 sqlite> .dump elements
- またダンプします。今度の出力先は
periodic.txt
sqlite> .output stdout
- 出力先を標準出力に戻します(直後で終了しているので不要とは言えます)。
sqlite> .quit
- sqlite3 を終了します。
ダンプからの復元
編集.dump
コマンドでダンプしたデータを現在のデータベースにリストアするには .read
コマンドを使います。
.read
編集$ sqlite3 SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open restore.db sqlite> .read periodic.txt sqlite> SELECT * FROM elements; 1|Hydrogen|H
空き領域の開放
編集SQLiteでは、テーブルにデータが追加されると、データベースファイルのサイズは徐々に大きくなるが、テーブルからデータを削除しても、データベースファイルのサイズはすぐには小さくなりません。
VACUUM
編集sqlite3では、VACUUMコマンドで空き領域の開放を行います。
$ sqlite3 SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open restore.db sqlite> VACUUM ;
Pythonからの利用
編集SQLiteはアプリケーションに組込まれ使われますが、ここでは Python からの利用の例を紹介します。
Pythonでは、PEP 249 -- Python Database API Specification v2.0 でデータベースAPIが標準化されており、sqlite3 モジュールでSQLite3をサポートしています。
- 都道府県コード一覧表(Python版)
import sqlite3 with sqlite3.connect(":memory:") as conn: cur = conn.cursor() cur.execute('CREATE TABLE 都道府県コード一覧表 (都道府県コード INTEGER, 都道府県 STRING, prefectures STRING)') prefs = """\ 01 北海道 Hokkaido 02 青森県 Aomori 03 岩手県 Iwate 04 宮城県 Miyagi 05 秋田県 Akita 06 山形県 Yamagata 07 福島県 Fukushima 08 茨城県 Ibaraki 09 栃木県 Tochigi 10 群馬県 Gumma 11 埼玉県 Saitama 12 千葉県 Chiba 13 東京都 Tokyo 14 神奈川県 Kanagawa 15 新潟県 Niigata 16 富山県 Toyama 17 石川県 Ishikawa 18 福井県 Fukui 19 山梨県 Yamanashi 20 長野県 Nagano 21 岐阜県 Gifu 22 静岡県 Shizuoka 23 愛知県 Aichi 24 三重県 Mie 25 滋賀県 Shiga 26 京都府 Kyoto 27 大阪府 Osaka 28 兵庫県 Hyogo 29 奈良県 Nara 30 和歌山県 Wakayama 31 鳥取県 Tottori 32 島根県 Shimane 33 岡山県 Okayama 34 広島県 Hiroshima 35 山口県 Yamaguchi 36 徳島県 Tokushima 37 香川県 Kagawa 38 愛媛県 Ehime 39 高知県 Kochi 40 福岡県 Fukuoka 41 佐賀県 Saga 42 長崎県 Nagasaki 43 熊本県 Kumamoto 44 大分県 Oita 45 宮崎県 Miyazaki 46 鹿児島県 Kagoshima 47 沖縄県 Okinawa """ cur.executemany('INSERT INTO 都道府県コード一覧表 VALUES(?, ?, ?)', (line.split("\t") for line in prefs.splitlines())) print('SELECT * FROM 都道府県コード一覧表') for row in cur.execute('SELECT * FROM 都道府県コード一覧表'): print(*row, sep=',') print() print('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22') for row in cur.execute('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22'): print(*row, sep=',') print() print('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード >= 2 and 都道府県コード <= 7') for row in cur.execute('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード >= 2 and 都道府県コード <= 7'): print(*row, sep=',') print()
- sqlite3モジュールのインポート
import sqlite3
- python3では、標準でSQLite3をサポートしています。
- インコアDBへのコネクションを確立
with sqlite3.connect(":memory:") as conn:
- with 文を使うと open関数同様、スコープを抜けると自動的にclose(とcommit)が行われるので、commit忘れを防止するためにも with を使うべきです。
- try/except で例外にも対応すべきかもしれません。
- 単一のファイル(かインコアDB)で構成されるSQLite3に connect するのは奇異に感じますが、これは Python Database API 2 が汎用的に設計されているためで、MySQLのようなサーバークライアントモデルを採用したRDBMSではまさにDBサーバーへの接続(認証付き)で「接続」を行いますので、その意味論に揃えた形です。
- sqlite3.Cursorオブジェクトの取得
cur = conn.cursor()
- テーブル定義
cur.execute('CREATE TABLE 都道府県コード一覧表 (都道府県コード INTEGER, 都道府県 STRING, prefectures STRING)')
- 文字列としてTSVデータを用意しました。
prefs = """\ 01 北海道 Hokkaido 02 青森県 Aomori 03 岩手県 Iwate ︙ 47 沖縄県 Okinawa """
- 複数レコードの一括登録
cur.executemany('INSERT INTO 都道府県コード一覧表 VALUES(?, ?, ?)', (line.split("\t") for line in prefs.splitlines()))
- .executemany()は、プレースホルダー(?)を含むSQL文にリストの内容を順に渡します。
- プレースホルダーを使うこのやり方は、SQLインジェクションへの耐性を上げることができると考えられています。
- 全レコード取得
print('SELECT * FROM 都道府県コード一覧表') for row in cur.execute('SELECT * FROM 都道府県コード一覧表'): print(*row, sep=',') print()
- 都道府県コード = 22のレコードを取得
print('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22') for row in cur.execute('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22'): print(*row, sep=',') print()
- 都道府県コード >= 2 and 都道府県コード <= 7なレコードを取得(東北地方)
print('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード >= 2 and 都道府県コード <= 7') for row in cur.execute('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード >= 2 and 都道府県コード <= 7'): print(*row, sep=',')
- 周期律表(Python版)
import sqlite3 with sqlite3.connect("periodic.db") as conn: cur = conn.cursor() cur.execute('CREATE TABLE 周期律表 (原子番号 INTEGER PRIMARY KEY AUTOINCREMENT, 和名 STRING, 英名 STRING, 元素記号 STRING)') #cur.execute('INSERT INTO 周期律表 (name, symbol) VALUES("H", "Hydrogen")') cur.executemany('INSERT INTO 周期律表(和名,英名,元素記号) VALUES(?, ?, ?)', [ ("水素", "Hydrogen", "H"), ("ヘリウム", "Helium", "He"), ("リチウム", "Lithium", "Li"), ("ベリリウム", "Beryllium", "Be"), ("硼素", "Boron", "B"), ("炭素", "Carbon", "C"), ("窒素", "Nitrogen","N"), ("酸素", "Oxygen", "O"), ("弗素", "Fluorine", "F"), ("ネオン", "Neon", "Ne"), ("ナトリウム", "Sodium", "Na"), ("マグネシウム", "Magnesium", "Mg"), ]) cur.execute('INSERT INTO 周期律表 VALUES(null, "アルミニウム", "Al", "Aluminium")') cur.execute('INSERT INTO 周期律表 VALUES(null, "珪素", "Si", "Silicon")') with sqlite3.connect("periodic.db") as conn2: cur = conn2.cursor() print("テーブルスキーマーは、PRAGMA table_info(TABLE) で取得できます。") for row in cur.execute('PRAGMA table_info("周期律表") '): print(*row, sep=',') print('SELECT * FROM 周期律表') print(*(row[1] for row in cur.execute('PRAGMA table_info("周期律表") ')),sep=',') for row in cur.execute('SELECT * FROM 周期律表'): print(*row, sep=',') print("VIEW は、仮想的なテーブルで検索式に名前をつけたような働きをします") cur.execute('CREATE VIEW 原子番号4未満の元素 AS SELECT 英名, 元素記号 FROM 周期律表 WHERE 原子番号 < 4') for row in cur.execute('SELECT * FROM 原子番号4未満の元素'): print(*row, sep=',') print("INDEX は、検索性能が向上する一方レコード追加時にオーバーヘッドが生じるというトレードオフがあります。") cur.execute('CREATE INDEX 英名インデックス ON 周期律表(英名)') print("UNIQUE INDEX は、同一のテーブル内で値の重複のないフィールドを使ったインデックスです。") cur.execute('CREATE UNIQUE INDEX 原子番号英名ユニークインデックス ON 周期律表(原子番号,英名)') print("sqlite_master はデータベース毎にあるテーブルの台帳です。") for row in cur.execute('SELECT * FROM sqlite_master'): print(*row, sep=',') print("----") for row in cur.execute('SELECT name,sql FROM sqlite_master WHERE type="table"'): print(*row, sep=',') for row in cur.execute('PRAGMA database_list'): print(*row, sep=',') print(cur.execute('SELECT sqlite_version()').fetchone()) print("----") for line in conn2.iterdump(): print(line)
- 実行結果
テーブルスキーマーは、PRAGMA table_info(TABLE) で取得できます。 0,原子番号,INTEGER,0,None,1 1,和名,STRING,0,None,0 2,英名,STRING,0,None,0 3,元素記号,STRING,0,None,0 SELECT * FROM 周期律表 原子番号,和名,英名,元素記号 1,水素,Hydrogen,H 2,ヘリウム,Helium,He 3,リチウム,Lithium,Li 4,ベリリウム,Beryllium,Be 5,硼素,Boron,B 6,炭素,Carbon,C 7,窒素,Nitrogen,N 8,酸素,Oxygen,O 9,弗素,Fluorine,F 10,ネオン,Neon,Ne 11,ナトリウム,Sodium,Na 12,マグネシウム,Magnesium,Mg 13,アルミニウム,Al,Aluminium 14,珪素,Si,Silicon VIEW は、仮想的なテーブルで検索式に名前をつけたような働きをします Hydrogen,H Helium,He Lithium,Li INDEX は、検索性能が向上する一方レコード追加時にオーバーヘッドが生じるというトレードオフがあります。 UNIQUE INDEX は、同一のテーブル内で値の重複のないフィールドを使ったインデックスです。 sqlite_master はデータベース毎にあるテーブルの台帳です。 table,周期律表,周期律表,2,CREATE TABLE 周期律表 (原子番号 INTEGER PRIMARY KEY AUTOINCREMENT, 和名 STRING, 英名 STRING, 元素記号 STRING) table,sqlite_sequence,sqlite_sequence,3,CREATE TABLE sqlite_sequence(name,seq) view,原子番号4未満の元素,原子番号4未満の元素,0,CREATE VIEW 原子番号4未満の元素 AS SELECT 英名, 元素記号 FROM 周期律表 WHERE 原子番号 < 4 index,英名インデックス,周期律表,4,CREATE INDEX 英名インデックス ON 周期律表(英名) index,原子番号英名ユニークインデックス,周期律表,5,CREATE UNIQUE INDEX 原子番号英名ユニークインデックス ON 周期律表(原子番号,英名) ---- 周期律表,CREATE TABLE 周期律表 (原子番号 INTEGER PRIMARY KEY AUTOINCREMENT, 和名 STRING, 英名 STRING, 元素記号 STRING) sqlite_sequence,CREATE TABLE sqlite_sequence(name,seq) 0,main,/workspace/periodic.db ('3.31.1',) ---- BEGIN TRANSACTION; DELETE FROM "sqlite_sequence"; INSERT INTO "sqlite_sequence" VALUES('周期律表',14); CREATE TABLE 周期律表 (原子番号 INTEGER PRIMARY KEY AUTOINCREMENT, 和名 STRING, 英名 STRING, 元素記号 STRING); INSERT INTO "周期律表" VALUES(1,'水素','Hydrogen','H'); INSERT INTO "周期律表" VALUES(2,'ヘリウム','Helium','He'); INSERT INTO "周期律表" VALUES(3,'リチウム','Lithium','Li'); INSERT INTO "周期律表" VALUES(4,'ベリリウム','Beryllium','Be'); INSERT INTO "周期律表" VALUES(5,'硼素','Boron','B'); INSERT INTO "周期律表" VALUES(6,'炭素','Carbon','C'); INSERT INTO "周期律表" VALUES(7,'窒素','Nitrogen','N'); INSERT INTO "周期律表" VALUES(8,'酸素','Oxygen','O'); INSERT INTO "周期律表" VALUES(9,'弗素','Fluorine','F'); INSERT INTO "周期律表" VALUES(10,'ネオン','Neon','Ne'); INSERT INTO "周期律表" VALUES(11,'ナトリウム','Sodium','Na'); INSERT INTO "周期律表" VALUES(12,'マグネシウム','Magnesium','Mg'); INSERT INTO "周期律表" VALUES(13,'アルミニウム','Al','Aluminium'); INSERT INTO "周期律表" VALUES(14,'珪素','Si','Silicon'); CREATE VIEW 原子番号4未満の元素 AS SELECT 英名, 元素記号 FROM 周期律表 WHERE 原子番号 < 4; CREATE INDEX 英名インデックス ON 周期律表(英名); CREATE UNIQUE INDEX 原子番号英名ユニークインデックス ON 周期律表(原子番号,英名); COMMIT;
PHPからの利用
編集SQLiteはアプリケーションに組込まれ使われますが、ここでは PHP からの利用の例を紹介します。
PDO
編集PHPでは、PDOでデータベースAPIが抽象化/標準化されており、SQLiteもPDO経由で使用できます。
- 都道府県コード一覧表(PHP/PDO版)
<?php // SQLite3/PDOを使用したデータベースハンドリング declare(strict_types=1); header('Content-Type: text/plain'); $db = new PDO("sqlite::memory:"); $db->exec( "CREATE TABLE 都道府県コード一覧表 (都道府県コード INTEGER, 都道府県 STRING, prefectures STRING)" ); $insert = $db->prepare("INSERT INTO 都道府県コード一覧表 VALUES (:n,:ja,:en)"); $db->beginTransaction(); try { foreach ( array_map( fn($line): array => explode("\t", $line), explode( "\n", <<<EOS 01 北海道 Hokkaido 02 青森県 Aomori 03 岩手県 Iwate 04 宮城県 Miyagi 05 秋田県 Akita 06 山形県 Yamagata 07 福島県 Fukushima 08 茨城県 Ibaraki 09 栃木県 Tochigi 10 群馬県 Gumma 11 埼玉県 Saitama 12 千葉県 Chiba 13 東京都 Tokyo 14 神奈川県 Kanagawa 15 新潟県 Niigata 16 富山県 Toyama 17 石川県 Ishikawa 18 福井県 Fukui 19 山梨県 Yamanashi 20 長野県 Nagano 21 岐阜県 Gifu 22 静岡県 Shizuoka 23 愛知県 Aichi 24 三重県 Mie 25 滋賀県 Shiga 26 京都府 Kyoto 27 大阪府 Osaka 28 兵庫県 Hyogo 29 奈良県 Nara 30 和歌山県 Wakayama 31 鳥取県 Tottori 32 島根県 Shimane 33 岡山県 Okayama 34 広島県 Hiroshima 35 山口県 Yamaguchi 36 徳島県 Tokushima 37 香川県 Kagawa 38 愛媛県 Ehime 39 高知県 Kochi 40 福岡県 Fukuoka 41 佐賀県 Saga 42 長崎県 Nagasaki 43 熊本県 Kumamoto 44 大分県 Oita 45 宮崎県 Miyazaki 46 鹿児島県 Kagoshima 47 沖縄県 Okinawa EOS ) ) as $pref ) { $insert->bindParam(":n", $pref[0]); $insert->bindParam(":ja", $pref[1]); $insert->bindParam(":en", $pref[2]); $insert->execute(); } $db->commit(); } catch (PDOException $e) { echo $e; $db->rollback(); throw $e; } $insert = null; $query = "PRAGMA table_info('都道府県コード一覧表')"; echo "$query", PHP_EOL; foreach ($db->query($query)->fetchAll(PDO::FETCH_ASSOC) as $assoc) { $fields[] = $assoc['name']; } $assoc = null; echo implode(',',$fields),PHP_EOL; echo PHP_EOL; $query = "SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22;"; echo "$query", PHP_EOL; foreach ($db->query($query)->fetchAll(PDO::FETCH_ASSOC) as $assoc) { array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; }); echo implode(",", $assoc), PHP_EOL; } echo PHP_EOL; $query = "SELECT * FROM 都道府県コード一覧表 WHERE 都道府県 LIKE '%京都%';"; echo "$query", PHP_EOL; foreach ($db->query($query)->fetchAll(PDO::FETCH_ASSOC) as $assoc) { array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; }); echo implode(",", $assoc), PHP_EOL; } $assoc = null; echo PHP_EOL; $query = "SELECT * FROM 都道府県コード一覧表;"; echo "$query", PHP_EOL; foreach ($db->query($query)->fetchAll(PDO::FETCH_ASSOC) as $assoc) { array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; }); echo implode(",", $assoc), PHP_EOL; } $assoc = null; echo PHP_EOL;
- 実行結果
PRAGMA table_info('都道府県コード一覧表') 都道府県コード,都道府県,prefectures SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22; 都道府県コード:22, 都道府県:静岡県, prefectures:Shizuoka SELECT * FROM 都道府県コード一覧表 WHERE 都道府県 LIKE '%京都%'; 都道府県コード:13, 都道府県:東京都, prefectures:Tokyo 都道府県コード:26, 都道府県:京都府, prefectures:Kyoto SELECT * FROM 都道府県コード一覧表; 都道府県コード:1, 都道府県:北海道, prefectures:Hokkaido 都道府県コード:2, 都道府県:青森県, prefectures:Aomori 都道府県コード:3, 都道府県:岩手県, prefectures:Iwate 都道府県コード:4, 都道府県:宮城県, prefectures:Miyagi 都道府県コード:5, 都道府県:秋田県, prefectures:Akita 都道府県コード:6, 都道府県:山形県, prefectures:Yamagata 都道府県コード:7, 都道府県:福島県, prefectures:Fukushima 都道府県コード:8, 都道府県:茨城県, prefectures:Ibaraki 都道府県コード:9, 都道府県:栃木県, prefectures:Tochigi 都道府県コード:10, 都道府県:群馬県, prefectures:Gumma 都道府県コード:11, 都道府県:埼玉県, prefectures:Saitama 都道府県コード:12, 都道府県:千葉県, prefectures:Chiba 都道府県コード:13, 都道府県:東京都, prefectures:Tokyo 都道府県コード:14, 都道府県:神奈川県, prefectures:Kanagawa 都道府県コード:15, 都道府県:新潟県, prefectures:Niigata 都道府県コード:16, 都道府県:富山県, prefectures:Toyama 都道府県コード:17, 都道府県:石川県, prefectures:Ishikawa 都道府県コード:18, 都道府県:福井県, prefectures:Fukui 都道府県コード:19, 都道府県:山梨県, prefectures:Yamanashi 都道府県コード:20, 都道府県:長野県, prefectures:Nagano 都道府県コード:21, 都道府県:岐阜県, prefectures:Gifu 都道府県コード:22, 都道府県:静岡県, prefectures:Shizuoka 都道府県コード:23, 都道府県:愛知県, prefectures:Aichi 都道府県コード:24, 都道府県:三重県, prefectures:Mie 都道府県コード:25, 都道府県:滋賀県, prefectures:Shiga 都道府県コード:26, 都道府県:京都府, prefectures:Kyoto 都道府県コード:27, 都道府県:大阪府, prefectures:Osaka 都道府県コード:28, 都道府県:兵庫県, prefectures:Hyogo 都道府県コード:29, 都道府県:奈良県, prefectures:Nara 都道府県コード:30, 都道府県:和歌山県, prefectures:Wakayama 都道府県コード:31, 都道府県:鳥取県, prefectures:Tottori 都道府県コード:32, 都道府県:島根県, prefectures:Shimane 都道府県コード:33, 都道府県:岡山県, prefectures:Okayama 都道府県コード:34, 都道府県:広島県, prefectures:Hiroshima 都道府県コード:35, 都道府県:山口県, prefectures:Yamaguchi 都道府県コード:36, 都道府県:徳島県, prefectures:Tokushima 都道府県コード:37, 都道府県:香川県, prefectures:Kagawa 都道府県コード:38, 都道府県:愛媛県, prefectures:Ehime 都道府県コード:39, 都道府県:高知県, prefectures:Kochi 都道府県コード:40, 都道府県:福岡県, prefectures:Fukuoka 都道府県コード:41, 都道府県:佐賀県, prefectures:Saga 都道府県コード:42, 都道府県:長崎県, prefectures:Nagasaki 都道府県コード:43, 都道府県:熊本県, prefectures:Kumamoto 都道府県コード:44, 都道府県:大分県, prefectures:Oita 都道府県コード:45, 都道府県:宮崎県, prefectures:Miyazaki 都道府県コード:46, 都道府県:鹿児島県, prefectures:Kagoshima 都道府県コード:47, 都道府県:沖縄県, prefectures:Okinawa
- メモリー上にデータベースを構築し、問合わせと応答を表示しています。
- PDO::prepare() を使うことで、プレースホルダー経由でSQLを実行し、SQLインジェクションの驚異を低減しています。
- $変数 = null; を随所で行っているのは、PHPでは変数のスコープをループに閉じ込めることができないためで、特にループから脱走したリファレンスが関係したバグは原因の特定が困難になりがちです。
SQLite3クラス
編集PDOとは別に、SQLite専用のSQLite3クラスも用意されています。
- 都道府県コード一覧表(PHP/ベンダー固有コード版)
<?php // SQLite3を使用したデータベースハンドリング declare(strict_types=1); header("Content-Type: text/plain"); $db = new SQLite3(":memory:"); $db->exec( "CREATE TABLE 都道府県コード一覧表 (都道府県コード INTEGER, 都道府県 STRING, prefectures STRING)" ); $insert = $db->prepare("INSERT INTO 都道府県コード一覧表 VALUES (:n,:ja,:en)"); try { foreach ( array_map( fn($line): array => explode("\t", $line), explode( "\n", <<<EOS 01 北海道 Hokkaido 02 青森県 Aomori 03 岩手県 Iwate 04 宮城県 Miyagi 05 秋田県 Akita 06 山形県 Yamagata 07 福島県 Fukushima 08 茨城県 Ibaraki 09 栃木県 Tochigi 10 群馬県 Gumma 11 埼玉県 Saitama 12 千葉県 Chiba 13 東京都 Tokyo 14 神奈川県 Kanagawa 15 新潟県 Niigata 16 富山県 Toyama 17 石川県 Ishikawa 18 福井県 Fukui 19 山梨県 Yamanashi 20 長野県 Nagano 21 岐阜県 Gifu 22 静岡県 Shizuoka 23 愛知県 Aichi 24 三重県 Mie 25 滋賀県 Shiga 26 京都府 Kyoto 27 大阪府 Osaka 28 兵庫県 Hyogo 29 奈良県 Nara 30 和歌山県 Wakayama 31 鳥取県 Tottori 32 島根県 Shimane 33 岡山県 Okayama 34 広島県 Hiroshima 35 山口県 Yamaguchi 36 徳島県 Tokushima 37 香川県 Kagawa 38 愛媛県 Ehime 39 高知県 Kochi 40 福岡県 Fukuoka 41 佐賀県 Saga 42 長崎県 Nagasaki 43 熊本県 Kumamoto 44 大分県 Oita 45 宮崎県 Miyazaki 46 鹿児島県 Kagoshima 47 沖縄県 Okinawa EOS ) ) as $pref ) { $insert->bindParam(":n", $pref[0], SQLITE3_INTEGER); $insert->bindParam(":ja", $pref[1], SQLITE3_TEXT); $insert->bindParam(":en", $pref[2], SQLITE3_TEXT); $insert->execute(); } } catch (Exception $e) { echo "Caught exception: " . $e->getMessage(); } $insert = null; $query = "PRAGMA table_info('都道府県コード一覧表')"; echo "$query", PHP_EOL; for ( $result = $db->query($query); ($assoc = $result->fetchArray(SQLITE3_ASSOC)); $fields[] = $assoc["name"] ) {} $assoc = null; echo implode(",", $fields), PHP_EOL; echo PHP_EOL; $query = "SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22;"; echo "$query", PHP_EOL; for ( $result = $db->query($query); ($assoc = $result->fetchArray(SQLITE3_ASSOC)); ) { array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; }); echo implode(",", $assoc), PHP_EOL; } $assoc = null; echo PHP_EOL; $query = "SELECT * FROM 都道府県コード一覧表 WHERE 都道府県 LIKE '%京都%';"; echo "$query", PHP_EOL; for ( $result = $db->query($query); ($assoc = $result->fetchArray(SQLITE3_ASSOC)); ) { array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; }); echo implode(",", $assoc), PHP_EOL; } $assoc = null; echo PHP_EOL; $query = "SELECT * FROM 都道府県コード一覧表;"; echo "$query", PHP_EOL; for ( $result = $db->query($query); ($assoc = $result->fetchArray(SQLITE3_ASSOC)); ) { array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; }); echo implode(",", $assoc), PHP_EOL; } $assoc = null; echo PHP_EOL;
脚註
編集関連リンク
編集- SQL SQL一般の仕様や文法についての解説