STRICT Tables

SQLiteには型制約が無かった。例えばINTEGER列に'123'という文字列を入れようとすると123という数値に変換されて入れられる。また'abc'のような10進表記ではない文字列はそのまま文字列として格納される。

import sqlite3

def main():
    print(f"{sqlite3.sqlite_version = }")
    conn = sqlite3.connect(":memory:")
    c = conn.cursor()
    c.executescript(
        """
        CREATE TABLE Tests (
            id INTEGER PRIMARY KEY,
            "元の型" TEXT NOT NULL,
            "値" INTEGER NOT NULL
        );
        INSERT INTO Tests ("元の型", "値") VALUES
        ('整数', 12), ('整数文字列', '34'), ('小数', 0.8), ('小数文字列', '0.9'), ('非数値文字列', 'abc');
        """
    )
    for row in c.execute("""SELECT id, "元の型", "値" FROM Tests"""):
        print(row, type(row[2]))

if __name__ == "__main__":
    main()

sqlite3.sqlite_version = '3.50.4'
(1, '整数', 12) <class 'int'>
(2, '整数文字列', 34) <class 'int'>
(3, '小数', 0.8) <class 'float'>
(4, '小数文字列', 0.9) <class 'float'>
(5, '非数値文字列', 'abc') <class 'str'>

しかしSQLite 3.37.0 (2021-11-27)で加わったSTRICT Tablesを用いると、整数と整数文字列のみが許されそれ以外はsqlite3.IntegrityErrorで怒られる。

CREATE TABLE Tests (
    id INTEGER PRIMARY KEY,
    "元の型" TEXT NOT NULL,
    "値" INTEGER NOT NULL
) STRICT;

またSTRICT Tablesを用いながらも型制約の無い列を実現するためにANY型が加わった。

CREATE TABLE Tests (
    id INTEGER PRIMARY KEY,
    "NULL以外は何でも入る列" ANY NOT NULL,
    "NULLも含めて何でも入る列" ANY
) STRICT;

RETURNING句

RETURNING句はSQLite 3.35.0 (2021-03-12)で加わった機能で、SQLの標準規格ではないようですが便利そうです。これを用いると操作した行の情報を、別にまたSQLを発行すること無く得られる。

import sqlite3

def main():
    print(f"{sqlite3.sqlite_version = }")
    conn = sqlite3.connect(":memory:")
    c = conn.cursor()
    c.executescript(
        """
        CREATE TABLE Stars (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL
        );
        """
    )
    print("\n挿入")
    for row in c.execute("INSERT INTO Stars (name) VALUES ('Oulan'), ('Ronnie Bell'), ('Sierra Mikain') RETURNING *"):
        print(row)

    print("\n更新")
    for row in c.execute("UPDATE Stars SET name = 'Victor' WHERE name LIKE 'Ron%' RETURNING *"):
        print(row)

    print("\n削除")
    for row in c.execute("DELETE FROM Stars WHERE name LIKE '%r%' RETURNING *"):
        print(row)

if __name__ == "__main__":
    main()

sqlite3.sqlite_version = '3.50.4'

挿入
(1, 'Oulan')
(2, 'Ronnie Bell')
(3, 'Sierra Mikain')

更新
(2, 'Victor')

削除
(2, 'Victor')
(3, 'Sierra Mikain')

ORMの中には行を挿入した後に別にSQLを発行して主キーを取得している物もあるので、これを用いると高速化できそうだ。