sqlite3 adaptors mystery

[Matej Cepl]

Hi,

I am in the process of creating a small script for filling the
sqlite3 database with data from rather large XML-RPC query (list
of many bugs from the Red Hat Bugzilla) and I would love to use
adaptors and converters for some data types which I am missing.

I have this test script (made hopefully pretty faithfully from the
documentation):

#!/usr/bin/python
import sqlite3
def adapt_boolean(bol):
if bol:
return "True"
else:
return "False"

def convert_boolean(bolStr):
if str(bolStr) == "True":
return bool(True)
elif str(bolStr) == "False":
return bool(False)
else:
raise ValueError, "Unknown value of bool attribute '%s'" \
% bolStr

sqlite3.register_adapter(bool,adapt_boolean)
sqlite3.register_converter("boolean",convert_boolean)

db = sqlite3.connect("test.db")
cur=db.cursor()
cur.execute("create table test(p boolean)")
p=False
cur.execute("insert into test(p) values (?)", (p,))
p=True
cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
print cur.fetchall()

And I would expect to print on output representation of bool values, i.e.,
something like

[False,True]

However, when running this program it seems converter doesn’t seem to work,
because I get:

[matej@viklef dumpBugzilla]$ rm test.db ; python testAdaptors.py
[(u'False',), (u'True',)]
[matej@viklef dumpBugzilla]$

There is probably something quite obvious what I do incorrectly, but I just
don't see it. Could somebody kick me in the right direction, please?

Thanks a lot,

Matěj Cepl

 

[Mel]

Matej Cepl wrote:
[ ... ]

However, when running this program it seems converter doesn’t seem to work,
because I get:

[matej@viklef dumpBugzilla]$ rm test.db ; python testAdaptors.py
[(u'False',), (u'True',)]
[matej@viklef dumpBugzilla]$

There is probably something quite obvious what I do incorrectly, but I just
don't see it. Could somebody kick me in the right direction, please?

There's nothing much wrong. cur.fetchall is returning a list of all
the selected rows, and each row is a tuple of fields. Each tuple is
being converted for display by repr, so the strings are shown as
unicode, which is what they are internally. Change the print to

for (field,) in cur.fetchall():
print field

and you'll see your plain-text strings.

Mel.

 

[Matej Cepl]

There's nothing much wrong. cur.fetchall is returning a list
of all the selected rows, and each row is a tuple of fields.
Each tuple is being converted for display by repr, so the
strings are shown as unicode, which is what they are
internally. Change the print to

for (field,) in cur.fetchall():
print field

and you'll see your plain-text strings.

Thanks for your help, but plain-text strings is not what
I wanted. The boolean variables was what I was after. See this
modified version of the script:

#!/usr/bin/python
import sqlite3
def adapt_boolean(bol):
if bol:
return "True"
else:
return "False"

def convert_boolean(bolStr):
if str(bolStr) == "True":
return bool(True)
elif str(bolStr) == "False":
return bool(False)
else:
raise ValueError, "Unknown value of bool attribute
'%s'" % bolStr

sqlite3.register_adapter(bool,adapt_boolean)
sqlite3.register_converter("boolean",convert_boolean)

db = sqlite3.connect(":memory:")
cur=db.cursor()
cur.execute("create table test(p boolean)")
p=False
cur.execute("insert into test(p) values (?)", (p,))
p=True
cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
for (field,) in cur.fetchall():
print field,type(field)

The output here is:

[matej@viklef dumpBugzilla]$ python testAdaptors.py False <type
'unicode'>
True <type 'unicode'>
[matej@viklef dumpBugzilla]$

I thought that converter is there for just exactly this -- that
I would get back bool values not strings.

Sorry for not being clear in the first run.

Matej

 

[Matej Cepl]

Thanks for your help, but plain-text strings is not what
I wanted. The boolean variables was what I was after. See this
modified version of the script:

OK, I got it -- I was missing detect_types parameter of the
connect method.

Matěj

 

[Mel]

Thanks for your help, but plain-text strings is not what
I wanted. The boolean variables was what I was after. See this
modified version of the script:

#!/usr/bin/python
import sqlite3
def adapt_boolean(bol):
if bol:
return "True"
else:
return "False"

def convert_boolean(bolStr):
if str(bolStr) == "True":
return bool(True)
elif str(bolStr) == "False":
return bool(False)
else:
raise ValueError, "Unknown value of bool attribute
'%s'" % bolStr

sqlite3.register_adapter(bool,adapt_boolean)
sqlite3.register_converter("boolean",convert_boolean)

db = sqlite3.connect(":memory:")
cur=db.cursor()
cur.execute("create table test(p boolean)")
p=False
cur.execute("insert into test(p) values (?)", (p,))
p=True
cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
for (field,) in cur.fetchall():
print field,type(field)

The output here is:

[matej@viklef dumpBugzilla]$ python testAdaptors.py False <type
'unicode'>
True <type 'unicode'>
[matej@viklef dumpBugzilla]$

I thought that converter is there for just exactly this -- that
I would get back bool values not strings.

Sorry for not being clear in the first run.

Sorry about the misunderstanding. It seems you want

db = sqlite3.connect("test.db", detect_types=sqlite3.PARSE_DECLTYPES)

After this, the print shows

False <type 'bool'>
True <type 'bool'>


Mel.