MySQLのデータベースをOneDriveやGoogleDriveに置いて共有しようとしたところ、うまくいかなかった。データベース系のデータファイルはクラウドストレージと相性が悪い。MySQL より SQLite の方が向いてるということで、今回はSQLiteの使い方について。
目次
MySQLとSQLiteの違い
MySQLは主にネットワーク越しで比較的規模が大きいデータベースの運用に使われる。サービスやデーモンとして常時起動しての使用が主目的で、データファイルやジャーナルはポータブルな使用を想定していない。いわゆるバックアップやエクスポートの操作が必要。
SQLiteはローカル環境で、特にアプリ内などで使われるように、単体のデータファイルとなり、そのままコピーして移動もできる。
SQLiteをダウンロードする
下記の「Precompiled Binaries for Windows」からバイナリをダウンロードして配置します。本記事執筆時点で「sqlite-tools-win32-x86-3400100.zip」と「sqlite-dll-win64-x64-3400100.zip」を使用しました。
https://www.sqlite.org/download.html
インストーラーではなくただのZipファイルなので解凍して任意のフォルダに配置するだけです。今回は例として下記のディレクトリに配置します。
C:\SQLite環境変数を設定する
Windowsの「システム」→「システムの詳細設定」→「(「システムのプロパティ」の「詳細設定」タブの)環境変数」→「（環境変数の）システム環境変数」の一覧にある「Path」を選択し、「編集」をクリックして「環境変数名の編集」の「新規」ボタンをクリックして先ほどの「mysql.exe」がある場所を追加します。
C:\SQLiteコマンドプロンプトまたはPowerShellを起動する
管理者モードでなくてもよいようです。
MySQLを起動する
コマンドプロンプトに、
sqlite3と入力します。下記の通り表示されれば成功です。
SQLite version 3.32.2 2020-06-04 12:58:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>ヘルプを表示する
コマンドプロンプトに以下のように入力します。
sqlite> .help下記の通り表示されます。
sqlite> .help
.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
.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 ?TABLE? 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.
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode
.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
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.save FILE Write in-memory database into FILE
.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
.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 ?on|off? 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 column widths for "column" modeデータベースの作成
コマンド「.open –new ディレクトリ/データベース名.db」でデータベースを作成できます。例えばpython_test_dbという名前のデータベースを作成したい場合のコマンドは下記の通りです。
.open --new C:/sqlite3/python_test_db.dbデータベースの表示
次のコマンドでデータベースを表示できます。
sqlite> .database
main: C:\SQLite\hoge.dbデータベースにテーブルを作成する
コマンド「CREATE TABLE テーブル名 (カラム名 データ型, カラム名 データ型, …);」でテーブルを作成できます。例えばpersonという名前で、カラムにidとnameを持つテーブルを作成したい場合のコマンドと結果は下記の通りです。
mysql> CREATE TABLE test_table (id INT, name VARCHAR(100));データ型は下記の通りです。
| 型 | 説明 | 備考 |
| INT | 整数 | |
| DECIMAL(全体の桁数, 小数点以下の桁数) | 小数 | 引数で整数部の桁数と小数の桁数を指定する。例えばDECIMAL(5, 2) と指定すると、123.45 のような数値が保存できる。 |
| VERCHAR(文字数) | 文字列 | 引数で文字数を指定する。例えばVARCHAR(100) と指定すると、100文字まで保存できる。 |
| DATETIME | 日時 | |
| DATE | 日付 |
テーブルの表示
次のコマンドでテーブルを表示できます。
sqlite> .tables
test_tableテーブルの構造を確認する
次のコマンドでテーブルの構造を確認できます。
sqlite> .schema test_table
CREATE TABLE test_table (id INT, name VARCHAR(100));テーブルの削除
次のコマンドでテーブルを削除できます。
sqlite> DROP TABLE test_table;テーブルが削除されていることを確認します。
sqlite> .tables
sqlite>SQLiteからログアウトする
次のいずれかのコマンドでログアウトできます。ログアウト後はコマンドプロンプトに戻ります。
sqlite> .exitsqlite> .quitデータベースの移動・コピー（バックアップ）・削除
SQLiteからログアウト後、エクスプローラーからコピーや切り取り、削除するだけです。
次回の起動時に既に作成したデータベースに接続するには
SQLite起動後に、コマンド「.open ディレクトリ/データベース名.db」で任意の場所の任意のデータベースに接続できます。。例えば先ほど作成したデータベースに再接続したい場合のコマンドは下記の通りです。
.open C:/sqlite3/python_test_db.dbつまり、本記事のきっかけであった、「OneDriveやGoogleDrive上を含む、任意の場所のデータベースを読み書きしたい」を実現することができます。