Reputation: 399
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
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
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