[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.python

sqlite3 adaptors mystery

Matej Cepl

3/1/2008 11:13:00 PM

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
4 Answers

Mel

3/1/2008 11:42:00 PM

0

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

3/2/2008 8:16:00 AM

0

On 2008-03-01, 23:41 GMT, Mel wrote:
> 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

3/2/2008 9:16:00 AM

0

On 2008-03-02, 08:16 GMT, Matej Cepl wrote:
> 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

3/2/2008 12:25:00 PM

0

Matej Cepl wrote:
> 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.