Python の SQLite3 データベースで LIST型 や BOOL型 を扱う方法

はじめに

sqlite3 にbool型 や list型 は存在しない。このままだと、JSONを読み込む際などに不都合がある。それらをsqlite3の組み込みの関数を用いて解決する

実装

Pythonの型をSQLiteで使える型に変換するためにはsqlite3.register_adapterを使う。これはユーザ定義型でも扱うことができる。反対に、SQliteからPythonに変換するためには、sqlite3.register_conveter を使う。sqlite3.register_adapter と sqlite3.register_conveter はペアになっており、片方だけでは相互に変換することができない。この2つを合わせて適合関数と呼びます。

import sqlite3

# ユーザ定義型 その1
# データベース内部ではセミコロン区切りのテキストになっている、。区切り文字は変更してもいい
sqlite3.register_adapter(list, lambda l: ';'.join([str(i) for i in l]))
sqlite3.register_converter('LIST', lambda s: [item.decode('utf-8')  for i in s.split(bytes(b';'))])

# ユーザ定義型 その2
sqlite3.register_adapter(bool, lambda b: str(b))
sqlite3.register_converter('BOOL', lambda l: bool(eval(l)))

使用例

データベースに接続する際の、detect_types=sqlite3.PARSE_DECLTYPES は必須(カラムの型を読み取るようになる)

test.py

import sqlite3

sqlite3.register_adapter(list, lambda l: ';'.join([str(i) for i in l]))
sqlite3.register_converter('List', lambda s: [item.decode('utf-8')  for item in s.split(bytes(b';'))])

sqlite3.register_adapter(bool, lambda b: str(b))
sqlite3.register_converter('Bool', lambda l: bool(eval(l)))

def main():
    conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
    conn.execute('''
    CREATE TABLE IF NOT EXISTS test_table (
        id      INTEGER PRIMARY KEY,
        list    LIST,
        flag    BOOL
    );
    ''')

    insert_data = [([1, 2, 3], True), ([4, 5, 6], False), (['hoge', 'fuga'], True)]
    conn.executemany('INSERT INTO test_table(list, flag) VALUES(?, ?)', insert_data)
    conn.commit()

    c = conn.cursor()
    for row in c.execute('SELECT * FROM test_table WHERE flag = "True"'):
        print(row)
        for item in row:
            print(f'{str(type(item)):15}:{item}')

if __name__ == '__main__':
    main()

out

(1, ['1', '2', '3'], True)
<class 'int'>  :1
<class 'list'> :['1', '2', '3']
<class 'bool'> :True
(3, ['hoge', 'fuga'], True)
<class 'int'>  :3
<class 'list'> :['hoge', 'fuga']
<class 'bool'> :True

参考文献

(Py2バージョン)
https://qiita.com/maueki/items/4aae7b2d9a34758ef465
(公式Docs)
https://docs.python.org/ja/3/library/sqlite3.html#using-adapters-to-store-additional-python-types-in-sqlite-databases