converting sqlite return values

Discussion in 'Python' started by bolly, Feb 20, 2006.

  1. bolly

    bolly Guest

    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.
     
    bolly, Feb 20, 2006
    #1
    1. Advertising

  2. bolly

    Jason Drew Guest

    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
     
    Jason Drew, Feb 20, 2006
    #2
    1. Advertising

  3. 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
     
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=, Feb 21, 2006
    #3
  4. bolly

    bolly Guest

    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
     
    bolly, Mar 20, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. mike parr

    converting cookie values

    mike parr, May 11, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    513
    mike parr
    May 11, 2004
  2. Greenhorn
    Replies:
    15
    Views:
    840
    Keith Thompson
    Mar 6, 2005
  3. Chris Rebert
    Replies:
    1
    Views:
    686
    Bobby
    May 28, 2009
  4. Carl Youngblood
    Replies:
    1
    Views:
    234
    Carl Youngblood
    Apr 9, 2005
  5. Replies:
    4
    Views:
    352
Loading...

Share This Page