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 <[email protected]> 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 <[email protected]> wrote: >> On Thu, May 7, 2009 at 2:09 PM, chandramouli s <[email protected]> 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 >> [email protected] >> Modify settings or unsubscribe at: >> https://lists.ubuntu.com/mailman/listinfo/storm >> > -- storm mailing list [email protected] Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/storm
