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)'.How 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]

Hi,

You can use the built-in function "eval" to return how Python evaluates
your string. For example1, 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.:

utuple = 'u(1,2,3,4)'
eval( utuple[1:] )

(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

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

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)'.

Looks like you're using pysqlite 2.x.

How can I change it back to a tuple so I can use it as a
Python native datatype?

You cannot store tuples using pysqlite directly:
Traceback (most recent call last):
File "<stdin>", line 1, in ?
pysqlite2.dbapi2.InterfaceError: Error binding parameter 0 - probably
unsupported type.
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:

>>> cur.execute("insert into test(foo) values (?)", (str(t),))

>>> cur.execute("select foo from test")

>>> res = cur.fetchone()[0]
>>> res u'(3, 4, 5)'
>>>

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:

>>> cur.execute("delete from test")

>>> cur.execute("insert into test(foo) values (?)", (buffer(str(t)),))

>>> cur.execute("select foo from test")

>>> res = cur.fetchone()[0]
>>> res

>>> eval(str(res))

(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:

(buffer(marshal.dumps(t)),))

>>> cur.execute("select foo from test")

>>> res = cur.fetchone()[0]
>>> marshal.loads(res)

(3, 4, 5)

I have looked in the docs and seen there is a decode/encode method but
how do I do this?

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]

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