Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > converting sqlite return values

Reply
Thread Tools

converting sqlite return values

 
 
bolly
Guest
Posts: n/a
 
      02-20-2006
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.

 
Reply With Quote
 
 
 
 
Jason Drew
Guest
Posts: n/a
 
      02-20-2006
Hi,

You can use the built-in function "eval" to return how Python evaluates
your string. For example:
>>> eval( '(1,2,3,4)' )

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

 
Reply With Quote
 
 
 
 
=?ISO-8859-1?Q?Gerhard_H=E4ring?=
Guest
Posts: n/a
 
      02-21-2006
bolly wrote:
> 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:

>>> from pysqlite2 import dbapi2 as sqlite
>>> con = sqlite.connect(":memory:")
>>> cur = con.cursor()
>>> cur.execute("create table test(foo)")

<pysqlite2.dbapi2.Cursor object at 0x00C9D2F0>
>>> t = (3, 4, 5)
>>> cur.execute("insert into test(foo) values (?)", (t,))

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),))

<pysqlite2.dbapi2.Cursor object at 0x00C9D2F0>
>>> cur.execute("select foo from test")

<pysqlite2.dbapi2.Cursor object at 0x00C9D2F0>
>>> 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/pysqli...d-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")

<pysqlite2.dbapi2.Cursor object at 0x00C9D2F0>
>>> cur.execute("insert into test(foo) values (?)", (buffer(str(t)),))

<pysqlite2.dbapi2.Cursor object at 0x00C9D2F0>
>>> cur.execute("select foo from test")

<pysqlite2.dbapi2.Cursor object at 0x00C9D2F0>
>>> res = cur.fetchone()[0]
>>> res

<read-write buffer ptr 0x00C9DDC0, size 9 at 0x00C9DDA0>
>>> 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:

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

<pysqlite2.dbapi2.Cursor object at 0x00C9D2F0>
>>> cur.execute("insert into test(foo) values (?)",

(buffer(marshal.dumps(t)),))
<pysqlite2.dbapi2.Cursor object at 0x00C9D2F0>
>>> cur.execute("select foo from test")

<pysqlite2.dbapi2.Cursor object at 0x00C9D2F0>
>>> 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
 
Reply With Quote
 
bolly
Guest
Posts: n/a
 
      03-20-2006
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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Unpack less values from function's return values Chris Rebert Python 1 05-28-2009 02:47 PM
Ruby sqlite/gem error: Could not load sqlite adapter jhs408@gmail.com Ruby 4 04-18-2009 12:53 AM
Installing sqlite-ruby when sqlite is in non-standard location Carl Youngblood Ruby 1 04-09-2005 03:32 AM
what value does lack of return or empty "return;" return Greenhorn C Programming 15 03-06-2005 08:19 PM
converting cookie values mike parr ASP .Net 2 05-11-2004 02:18 PM



Advertisments