[storm] This is a noob question: storing lists

[chandramouli s]

Yes what I was looking for is a way to store the values(400 fields) in
400 columns with just an ID column to identify the record. In the
absence of a convenient method to do that (automatically generate 400
column names) I decided that storing it as a list would be convenient.
Yes pickling also might work, but ideally I would like to generate 400
columns or store it as a list. I intend to share the data with people
coding in Java but a sqlite db seemed more convenient than a flatfile
(to explore the data or to verify parts of it or correct it).

On Thu, May 7, 2009 at 1:49 PM, Vernon Cole <vernondcole at gmail.com> wrote:
> 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
>>
>