[storm] This is a noob question: storing lists

[Vernon Cole]

Chandramouli:
    I agree.  The "best" thing to do here is a relation between two
tables. I built a database like your example here (using an array) 18
years ago, and am still regretting it. Go with two tables, it works
much better in the long run.

James:
    Would you be kind enough to provide actual code as an example of
how you, an experienced expert, would code this simple operation?
   I have been lurking in the shadows of storm for ages now, and still
don't understand it enough to feel competent in giving even such
simple advice. I will be writing a chapter about SQL data access in
the near future and would like something to build from so that my
storm example is good code.
--
Vernon

On Thu, May 7, 2009 at 2:59 AM, James Henstridge <james at jamesh.id.au> wrote:
> On Thu, May 7, 2009 at 2:09 PM, chandramouli s <naruvimama at gmail.com> wrote:
>> What I would like to do is store a set of data values, 400 or so in a
>> sqlite database (instead of a flatfile as it is normally done). And I
>> tried to run following  the examples, but I wonder if the list storage
>> is right. Any help is much appreciated
>>
>> The program:
>> ############################################################
>>
>> from storm.locals import *
>> class Personner(object):
>>  __storm_table__="personner"
>>  id= Int(primary=True)
>>  name=Unicode()
>>  marks=List()
>
> If you want to use the List() property, you should specify what the
> list holds.  For example:
>
>    marks = List(type=Int())
>
> That said, I'm not sure how well sqlite handles arrays, so it might be
> appropriate to do something else.
>
>
>> database=create_database("sqlite:///fort")
>> store=Store(database)
>> store.execute("CREATE TABLE IF NOT EXISTS personner (id INTEGER
>> PRIMARY KEY, name VARCHAR,marks TEXT)")
>> for x in xrange(200):
>>  d=Personner()
>>  d.name=unicode(str(x))
>
> The double cast here is unneeded.  If you want a unicode
> representation of an integer, just do unicode(x).
>
>>  d.marks=[x,2*x,x-1,x*6] # d.marks=unicode(str([x,2*x,x-1,x*6]))
>>  store.add(d)
>>  store.flush()
>>  print 'success'
>>
>> store.commit()
>> print 'done'
>>
>> #####################################################################
>> The error msg:
>> Traceback (most recent call last):
>>  File "testStorm.py", line 16, in <module>
>>    store.flush()
>>  File "/usr/lib64/python2.6/site-packages/storm/store.py", line 486, in flush
>>    self._flush_one(obj_info)
>>  File "/usr/lib64/python2.6/site-packages/storm/store.py", line 523,
>> in _flush_one
>>    result = self._connection.execute(expr)
>>  File "/usr/lib64/python2.6/site-packages/storm/database.py", line
>> 203, in execute
>>    raw_cursor = self.raw_execute(statement, params)
>>  File "/usr/lib64/python2.6/site-packages/storm/databases/sqlite.py",
>> line 153, in raw_execute
>>    return Connection.raw_execute(self, statement, params)
>>  File "/usr/lib64/python2.6/site-packages/storm/database.py", line
>> 284, in raw_execute
>>    self._check_disconnect(raw_cursor.execute, *args)
>>  File "/usr/lib64/python2.6/site-packages/storm/database.py", line
>> 325, in _check_disconnect
>>    return function(*args, **kwargs)
>> pysqlite2.dbapi2.InterfaceError: Error binding parameter 0 - probably
>> unsupported type.
>
> If this error still happens after changing the property definition, it
> means you'll have to pick another approach.  Some things to try:
>
> 1. manually encode/decode the lists of integers when storing them to the DB.
> 2. use a Pickle() column, which lets you store arbitrary objects
> (doesn't constrain data much, and is not particularly efficient).
> 3. Add a second table to store individual marks, referencing the first
> table in a many to one relationship.  If you ever want to search based
> on the marks values, then this is probably what you want.
>
> James.
>
> --
> storm mailing list
> storm at lists.canonical.com
> Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/storm
>