CODEWITHSUNDEEP
pythonsqlite
peu ping
peu ping

Reputation: 399

Storing bools in SQLite database

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.

Upvotes: 7

Views: 15257

Answers (2)

Martijn Pieters
Martijn Pieters

Reputation: 1125398

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,)

Upvotes: 26

Jon Clements
Jon Clements

Reputation: 142256

sqlite3 doesn't have a boolean type - just store your field as an integer instead:

>>> import sqlite3
>>> db = sqlite3.connect(':memory:')
>>> db.execute('create table test (col1 integer)')
<sqlite3.Cursor object at 0x3737880>
>>> db.execute('insert into test values (?)', (1,))
<sqlite3.Cursor object at 0x3737960>
>>> print list(db.execute('select * from test'))
[(1,)]

A custom converter type takes the string representation of a stored column, and then you're expected to use that to create a new type from it...

If you did something like:

db = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
def myfunc(col):
    val = int(col) == 1
    print val, type(val)
    return val

sqlite3.register_converter('boolean', myfunc)

db.execute('create table blah (something boolean)')
db.executemany('insert into blah values (?)', [(True,), (False,)])
list(db.execute('select * from blah'))

You'll get a bool returned, which is what I think you want - however, you will need to make sure to handle exceptions, otherwise they get silently suppressed...

Upvotes: 3

Related Questions