SQLite「no such table」の原因と直し方

要約

テーブルは作ってあるのに、SELECT を実行すると無いと言われる。
Python の sqlite3 と sqlite3 コマンドでは次のように出た（Python 3.12・SQLite 3.46.1）。

sqlite3.OperationalError: no such table: users

Error: in prepare, no such table: users

no such table は、いま開いているデータベースにそのテーブルが無いという意味だ。
テーブルを作ったファイルとは別のファイルを開いていることが多い。
SQLite は、開こうとしたファイルが無いと新しい空のデータベースを作る（sqlite3_open（新しいタブで開く）↗ の既定の動き）。
そのため、パスを間違えてもエラーにならず、最初のクエリで no such table になる。

解決策

1. 絶対パスで開く

import sqlite3
from pathlib import Path
 
DB = Path(__file__).resolve().parent / "app.db"
con = sqlite3.connect(DB)

スクリプトの場所を基準にしたところ、どこから実行しても data/app.db が開かれ、[(1, 'user')] が返った。

2. 開いているファイルを確かめる

sqlite3 data/app.db "PRAGMA database_list;"
sqlite3 data/app.db ".tables"

PRAGMA database_list は 0|main|/proj/data/app.db のように実際のパスを返す。.tables か SELECT name FROM sqlite_schema WHERE type='table'（The Schema Table（新しいタブで開く）↗）で、そのファイルにあるテーブル名を確かめる。

3. 無いファイルは作らせない

con = sqlite3.connect("file:app.db?mode=rw", uri=True)

mode=rw ではファイルが無いと unable to open database file で止まり、空のファイルは作られなかった。
パスの誤りをその場で見つけられる。

4. インメモリは共有キャッシュの URI で開く

con = sqlite3.connect("file:shared?mode=memory&cache=shared", uri=True)

同じ URI で開いた 2 つ目の接続から、1 つ目の接続で作ったテーブルを参照できた。
データベースは、最後の接続を閉じた時点で消える。

よくある原因

1. 相対パスが、実行した場所を基準に解決された。data/app.py の中で sqlite3.connect("app.db") と書き、プロジェクトのルートから python data/app.py で実行した。
   開かれたのは data/app.db ではなく、ルートに新しくできた 0 バイトの app.db だった。
2. :memory: を接続ごとに開いた。:memory: は接続ごとに別のデータベースになるので、別の接続で作ったテーブルは見えない。
3. テーブル名が違うか、テーブルを作る前に参照している。users で作ったテーブルを user で参照すると、同じく no such table: user になる。