Storing bools in SQLite database

Question

In my table schema:

... disabled BOOLEAN, ...

When connecting to db:

db = sqlite3.connect(f, detect_types=sqlite3.PARSE_DECLTYPES)
sqlite3.register_converter("BOOLEAN", myfunc)

I insert a record like so:

INSERT INTO mytable (disabled, ...) VALUES (:disabled, ...)

Along with a parameter dict containing disabled: False.

When I read that record back, myfunc is called to convert the BOOLEAN type:

def myfunc(x):
    print x, type(x)

Result: 0, <type 'str'> (which of course evaluates as True when I want False)

I want bools stored as 1 byte INTEGER, I simply want to convert them to Python bool when reading records (other parts of code expect bools not ints). Is SQLite storing them as strings, or converting them to strings before calling myfunc? Why?

P.S. - I tried using sqlite3.register_adapter(bool, int), to no avail.

Answer 1

You want to use a combination of register_adapter and register_converter for the two directions:

sqlite3.register_adapter(bool, int)
sqlite3.register_converter("BOOLEAN", lambda v: bool(int(v)))

SQLite uses a dynamic type system but for custom types it has no way of knowing for certain if 0 is a string or an integer, so you get a string back here.

Alternatively, use:

sqlite3.register_converter("BOOLEAN", lambda v: v != '0')

which is a lot more flexible and robust when it comes to legacy data, but perhaps you want an exception to be raised.

Demo:

>>> import sqlite3
>>> db = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
>>> sqlite3.register_adapter(bool, int)
>>> sqlite3.register_converter("BOOLEAN", lambda v: bool(int(v)))
>>> db.execute('CREATE TABLE foo (bar BOOLEAN)')
<sqlite3.Cursor object at 0x10a17a340>
>>> db.execute('INSERT INTO foo VALUES (?)', (True,))
<sqlite3.Cursor object at 0x10a17a3b0>
>>> db.execute('INSERT INTO foo VALUES (?)', (False,))
<sqlite3.Cursor object at 0x10a17a340>
>>> for row in db.execute('SELECT * FROM foo'):
...     print row
... 
(True,)
(False,)