BLOBをSQLiteに保存する
どうも今使ってるモジュールにはsqlite.encodeってのが無いみたい。
SQLite and Python typesにBLOBはBufferって書いてるから直接Byte形を対応させれば良いのかな?
とりあえず出来てるみたいだけど。
SQLite type | Python type | |
---|---|---|
NULL | None | |
INTEGER | :class`int` | |
REAL | float | |
TEXT | depends on text_factory, str by default | |
BLOB | buffer |
>>> import sqlite3, pickle >>> connection = sqlite3.connect(':memory:') >>> cursor = connection.cursor() >>> cursor.execute("Create table justatest (name TEXT, ablob BLOB)") >>> names = 'aramis', 'athos', 'porthos' >>> data = {} >>> for name in names: datum = list(name) datum.sort() data[name] = pickle.dumps(datum, 2) >>> sql = 'INSERT INTO justatest VALUES(?, ?)' >>> for name in names: cursor.execute(sql, (name, data[name])) >>> sql = 'SELECT name, ablob FROM justatest ORDER BY name' >>> cursor.execute(sql) # fetchallは残り全ての行Listを返す。利用可能行が無い場合は空Listが帰る >>> for name, blob in cursor.fetchall(): print(name, pickle.loads(blob), pickle.loads(data[name])) aramis ['a', 'a', 'i', 'm', 'r', 's'] ['a', 'a', 'i', 'm', 'r', 's'] athos ['a', 'h', 'o', 's', 't'] ['a', 'h', 'o', 's', 't'] porthos ['h', 'o', 'o', 'p', 'r', 's', 't'] ['h', 'o', 'o', 'p', 'r', 's', 't'] >>> connection.close() >>>