converting sqlite return values

[Gerhard Häring]

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/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")
<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