converting sqlite return values

[bolly]

Hi,
I've been putting Python data into a sqlite3 database as tuples but
when I retrieve them they come back as unicode data e.g
'u(1,2,3,4)'.Ho w can I change it back to a tuple so I can use it as a
Python native datatype?
I have looked in the docs and seen there is a decode/encode method but
how do I do this?
Hope you can help.

[Jason Drew]

Re: converting sqlite return values

Hi,

You can use the built-in function "eval" to return how Python evaluates
your string. For example:[color=blue][color=green][color=darkred]
>>> eval( '(1,2,3,4)' )[/color][/color][/color]
(1, 2, 3, 4)

In other words, eval will take your string that looks like a tuple, and
return an actual tuple object.

Note that the 'u' prefix in your string will cause an error if you pass
it to eval, so you should drop that, e.g.:[color=blue][color=green][color=darkred]
>>> utuple = 'u(1,2,3,4)'
>>> eval( utuple[1:] )[/color][/color][/color]
(1, 2, 3, 4)

In general, though, converting your strings/tuples back and forth like
this might not be the best idea, depending on the situation. If the
numbers represent consistent items, like (price, tax, code, quantity),
then you would do better to use a field for each item in your database
and insert/fetch the numbers appropriately.

Storing whole Python objects in single database fields isn't unheard
of, but in general you should only do it when you really need to do it.
When you do, there are various Python modules to help, though I haven't
used this approach much myself.

Jason

[Gerhard Häring]

Re: converting sqlite return values

bolly wrote:[color=blue]
> Hi,
> I've been putting Python data into a sqlite3 database as tuples but
> when I retrieve them they come back as unicode data e.g
> 'u(1,2,3,4)'.[/color]

Looks like you're using pysqlite 2.x.
[color=blue]
> How can I change it back to a tuple so I can use it as a
> Python native datatype?[/color]

You cannot store tuples using pysqlite directly:
[color=blue][color=green][color=darkred]
>>> from pysqlite2 import dbapi2 as sqlite
>>> con = sqlite.connect( ":memory:")
>>> cur = con.cursor()
>>> cur.execute("cr eate table test(foo)")[/color][/color][/color]
<pysqlite2.dbap i2.Cursor object at 0x00C9D2F0>[color=blue][color=green][color=darkred]
>>> t = (3, 4, 5)
>>> cur.execute("in sert into test(foo) values (?)", (t,))[/color][/color][/color]
Traceback (most recent call last):
File "<stdin>", line 1, in ?
pysqlite2.dbapi 2.InterfaceErro r: Error binding parameter 0 - probably
unsupported type.[color=blue][color=green][color=darkred]
>>>[/color][/color][/color]

That's because only a limited set of types that have a sensible mapping
to SQLite's supported data types is supported.

So probably you did something like:
[color=blue][color=green][color=darkred]
>>> cur.execute("in sert into test(foo) values (?)", (str(t),))[/color][/color][/color]
<pysqlite2.dbap i2.Cursor object at 0x00C9D2F0>[color=blue][color=green][color=darkred]
>>> cur.execute("se lect foo from test")[/color][/color][/color]
<pysqlite2.dbap i2.Cursor object at 0x00C9D2F0>[color=blue][color=green][color=darkred]
>>> res = cur.fetchone()[0]
>>> res[/color][/color][/color]
u'(3, 4, 5)'[color=blue][color=green][color=darkred]
>>>[/color][/color][/color]

Aha. You stored a string and got back a Unicode string. That's all ok
because SQLite strings are by definition all UTF-8 encoded that's why
the pysqlite developer decided that what you get back in Python are
Unicode strings.

Now there are different possibilites to attack this problem.

a) Use SQLite as a relational database and don't throw arbitrary objects
at it
b) Write a custom converter and adapter for your tuple type. See

http://initd.org/pub/software/pysqlite/doc/usage-guide.html#sqlite-and-python-types

This way it will all work transparently from you once you've done the
preparations.

c) Store and retrieve the whole thing as a BLOB and convert manually:
[color=blue][color=green][color=darkred]
>>> cur.execute("de lete from test")[/color][/color][/color]
<pysqlite2.dbap i2.Cursor object at 0x00C9D2F0>[color=blue][color=green][color=darkred]
>>> cur.execute("in sert into test(foo) values (?)", (buffer(str(t)) ,))[/color][/color][/color]
<pysqlite2.dbap i2.Cursor object at 0x00C9D2F0>[color=blue][color=green][color=darkred]
>>> cur.execute("se lect foo from test")[/color][/color][/color]
<pysqlite2.dbap i2.Cursor object at 0x00C9D2F0>[color=blue][color=green][color=darkred]
>>> res = cur.fetchone()[0]
>>> res[/color][/color][/color]
<read-write buffer ptr 0x00C9DDC0, size 9 at 0x00C9DDA0>[color=blue][color=green][color=darkred]
>>> eval(str(res))[/color][/color][/color]
(3, 4, 5)

That's the simple apprach, but it sucks because eval() is sloppy
programming IMO.

So I'd rather marshal and demarshal the tuple:
[color=blue][color=green][color=darkred]
>>> import marshal
>>> cur.execute("de lete from test")[/color][/color][/color]
<pysqlite2.dbap i2.Cursor object at 0x00C9D2F0>[color=blue][color=green][color=darkred]
>>> cur.execute("in sert into test(foo) values (?)",[/color][/color][/color]
(buffer(marshal .dumps(t)),))
<pysqlite2.dbap i2.Cursor object at 0x00C9D2F0>[color=blue][color=green][color=darkred]
>>> cur.execute("se lect foo from test")[/color][/color][/color]
<pysqlite2.dbap i2.Cursor object at 0x00C9D2F0>[color=blue][color=green][color=darkred]
>>> res = cur.fetchone()[0]
>>> marshal.loads(r es)[/color][/color][/color]
(3, 4, 5)
[color=blue]
> I have looked in the docs and seen there is a decode/encode method but
> how do I do this?[/color]

You don't. This was for only there in pysqlite 1.x and pysqlite 2.x. In
pysqlite 2.x, you use the Python builtin buffer() callable to convert
strings to buffers to mark them as BLOB values for pysqlite and you
willg et back buffer objects from pysqlite for BLOB values, too.

HTH,

-- Gerhard

[bolly]

Re: converting sqlite return values

Hi Gerhard,
Firstly my apologies for not replying sooner and secondly thanks for
the advice.I went down the route of changing the data I was entering so
that it was always an integer and zap - no more problems.
Thanks again,
Bolly