sqlite3のデータベースを作る
sqlite3のデータベースを作る
脆弱性管理システムを作っている中でいままでMySQLを使用していましたが、簡易的なデモを行えるようにsqLite3も使用できるように機能追加することにしました。
2025/10/18 にテストを行いプログラム修正を行なった箇所、知見を得たものがあるためフィードバックします。
sqlite3の入手
私のmacには半年前くらいにChatbotを作った際にインストールしたsqlite3がいたのでそのまま使えましたが、一応プラットフォーム別の導入方法を記載します。
#macOSの場合
brew install sqlite
#Ubuntu / Debianの場合（後日、RasberryPiにも導入するので大事）
sudo apt install sqlite3
#Windowsの場合
公式サイトからダウンロード：
👉 https://www.sqlite.org/download.html
「sqlite-tools」パッケージを展開し、
sqlite3.exe を任意のフォルダに置いてPATHを通します。
CLIでデータベースを作成する
データベースとテーブルは環境構築の一環としてCLIから手動作成します。
データベースを作成したいディレクトリに移動して以下のコマンドを実行します。
$ sqlite3 NVD_DB.db
# データベースが作成されていることを確認。
ls -l
-rw-r--r-- 1 ユーザー名 staff 237568 10 13 16:44 NVD_DB.db
テーブルを作成する
まず、Create Table 文を書いたsqlファイルを作成します。
サンプルとして、1テーブルだけ抜粋します。
こちらがsqlite3用のCreate文です。
-- テーブルを作成する
-- CVE情報
-- CVE基本情報
DROP TABLE IF EXISTS NVD_CVE_INFO;
CREATE TABLE IF NOT EXISTS NVD_CVE_INFO
(
cveid TEXT,
sourceidentifier TEXT,
published TIMESTAMP NOT NULL,
lastModified TIMESTAMP,
vulnStatus TEXT,
PRIMARY KEY(cveid, sourceidentifier)
);
CREATE INDEX IF NOT EXISTS idx_sourceidentifier ON NVD_CVE_INFO (sourceidentifier);
同じテーブルのMySQL用のCreate文です。
-- テーブルを作成する
-- CVE情報
-- CVE基本情報
DROP TABLE NVD_DB.NVD_CVE_INFO;
CREATE TABLE IF NOT EXISTS NVD_DB.NVD_CVE_INFO
(
cveid VARCHAR(16),
sourceidentifier VARCHAR(128),
published TIMESTAMP NOT NULL,
lastModified TIMESTAMP,
vulnStatus VARCHAR(32),
PRIMARY KEY(cveid, sourceidentifier),
KEY idx_sourceidentifier(sourceidentifier)
)
ENGINE=InnoDB
COMMENT='CVE情報の基本情報を管理する';
GRANT SELECT, INSERT, UPDATE, DELETE ON NVD_DB.NVD_CVE_INFO TO 'nvdadmin'@'%';
GRANT SELECT ON NVD_DB.NVD_CVE_INFO TO 'nvdoper'@'%';
大きくMySQLと異なるのは Varchar型などがなくTEXTになっています。また、桁数の指定も不要とのことです。
TIMESTAMP型も存在しないとのことですが、この状態でCreateはできています。まだ、動作検証を行なっていないためもしかしたらテーブルを操作した時にエラーになって作り直す可能性があります。
例題のテーブルでは使用していませんが、TINYINT型も存在せずINTGER型、DECIMAL型の代替としてREAL型を使用します。
また、インデックスの書き方も似てはいるのですが、Create Table文内ではなく Create Index で記述する必要がありそうです。
ユーザーの概念（ChatGPTの表現では管理権限）がないためGRANTを使用しないのもミソかなって思います。
続いて作成したsqlファイルの内容を元にテーブルを実際に作成します。
sqlite3コマンドでデータベースを指定した上で、読み込ませるsqlファイルのパスを入力します。
$ sqlite3 NVD_DB.db < ./works/sqlite3/create_sql/create_table.sql
# テーブルが作成されていることを確認
$ sqlite3 NVD_DB.db
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite> .tables
CONFIGURATION METRICS NVD_CVE_TAG REFERENCE
CVECHANGE NVD_CPE_INFO NVD_DEPRECATED_BY VENDERCOMMENT
CVECHANGE_DETAIL NVD_CPE_REFERENCE NVD_DEPRECATES WEAKNESSE
DESCRIPTION NVD_CPE_TITLE NVD_PRODUCT
INSTALLED_PRODUCT NVD_CVE_INFO NVD_VENDER
とりあえず出来はしました。動作確認はしていません。
ちなみに、ターミナルで sqlite3 コマンドを実行した時の、コマンドの終了の仕方は以下のとおりです。
各種命令の前に . をつけるのが慣例のようです。
テーブル構成を見たい場合はPRAGMAコマンドを使用します。
sqlite> PRAGMA table_info(NVD_CVE_INFO);
0|cveid|TEXT|0||1
1|sourceidentifier|TEXT|0||2
2|published|TIMESTAMP|1||0
3|lastModified|TIMESTAMP|0||0
4|vulnStatus|TEXT|0||0
sqlite> .exit
インポート機能を作る
続いてやったのはIMPORT用のSQLの作成です。
ここが大きくMySQLとsqlite3で異なっていました。
まず、MySqlのIMPORT SQLです。抜粋するのが面倒くさいのでプログラムをそのまま記載します。
# MySQLに接続（local_infile=True がポイント）
self.connection: pymysql.Connection[Cursor] = pymysql.connect(
host=hostname,
user=user_id,
password=password,
database=database,
charset=self.CHAR_SET,
local_infile=self.LOCAL_INFILE
csv_path:str = os.path.join(self.userDocPath, filename)
# インポートファイルの存在チェックを行う
if os.path.exists(csv_path):
# CSVファイルの絶対パスを指定
sql = f"""
LOAD DATA LOCAL INFILE '{csv_path}'
{importoption}
INTO TABLE {tablename}
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '{rtnchar}'
"""
# カーソルを生成する
myCorsor = self.connection.cursor()
# SQLを実行する
myCorsor.execute(sql)
# コミットする
self.connection.commit()
TMCommonFunction.printInfomationMessage(f"IMPORT処理が終了しました。")
# カーソルを閉じる
myCorsor.close()
self.connection.close()
次はsqlite3のIMPORT用SQLです。
しつこいですが、動作検証をしていないのであとで修正を行う可能性が高いです。
＊2025/10/18 に動作検証を行い、後述する Replaceの指定外は修正箇所はありませんでした。
# SQLite3に接続
self.connection = sqlite3.connect(self.DBNAME)
csv_path:str = os.path.join(self.userDocPath, filename)
# インポートファイルの存在チェックを行う
if os.path.exists(csv_path):
# CSV読み込み
with open(csv_path, newline=rtnchar, encoding='utf-8') as csvfile:
reader = csv.reader(csvfile)
data = [tuple(row) for row in reader]
placeholders = ", ".join(["?"] * len(headers))
# CSVファイルの絶対パスを指定
sql = f"""
INSERT INTO {tablename} ({', '.join(headers)}) VALUES ({placeholders})
"""
# カーソルを生成する
myCorsor = self.connection.cursor()
# SQLを実行する
myCorsor.executemany(sql, data)
# コミットする
self.connection.commit()
TMCommonFunction.printInfomationMessage(f"IMPORT処理が終了しました。")
# カーソルを閉じる
myCorsor.close()
self.connection.close()
MySQLでは LOAD DATA を使用するのに対して、sqlite3では INSERT INTO を使用しています。
--- 2025/10/18 追記 ここから ---
なお、MySQLのLOAD DATA はデフォルトだと重複データをReplaseしますが、SQLite3 はキー重複エラーとなります。
重複データをReplaseしたい場合は「INSERT or REPLACE INTO」を使用します。
--- 2025/10/18 追記 ここまで ---
KEY と VALUES は以下のようなイメージなるようです。
()内で変数名を指定し、VALUES で格納する値を指定しますが、一括INSERTする場合はここをから部分の ? にしておき、executemany する時に実際の値を与えているようです。
プログラム書いてる時はなんのこっちゃ？と思いましたが、ChatGPTの対話の記録に↓の記録があったので理解できました。
cur.executemany(
"INSERT INTO NVD_CVE_INFO (cveid, sourceidentifier, published, lastModified, vulnStatus) VALUES (?, ?, ?, ?, ?)",
data)
正直、INSERT INTO用に変数名リストを、テーブル数分作るのがしんどかったです。（そういう地味な作業が苦手、、、）
それ以外の部分のコーディングはほとんどMySQLとは変わっていません。ここはDBMSがというよりはPythonがDBMSの差を吸収しているためかなと思います。
今回気になったのはsqlite3は非常にMySQLに似ていていてDDL文含めて、多少のカスタムで転用することができたことです。
ただ、ChatGPT調べではこの2つのDBMSは歴史的にも成り立ち的にも直接的な因果関係はないようで、偶然ぽいです。ChatGPT曰く「いとこ」のような関係とのこと。
最後となりますが、SQlite3は簡易DBMSのため、マルチセッションに対応していません。バッチプログラム等でデータベースを操作中はCLIや他のプログラムからは操作できないため注意が必要です。
インストールの簡便性などからPoC検証などにはもってこいだと思います。
コメント
コメントを投稿