Hi Paul, I think the problem is simply that you aren't using Pygr's brand-new support for sqlite, which is no fault of your own, since we haven't even added documentation about that yet! We added support for sqlgraph to work transparently with sqlite. All you have to do is give SQLTable a cursor that was obtained from sqlite. We will try to add docs on this in the next couple weeks, but for the moment the easiest way to see what you need to do is to look at the test suite cases that test our sqlite support:
- see the pygr/tests/testlib/testutil.py class SQLite_Mixin. Its setUp() method is a good example of creating a cursor for sqlite using our convenience methods. Once you create a cursor, just pass that to sqlgraph.SQLTable's constructor and everything should work exactly the same as if you were using MySQL. - you can also look at pygr/tests/sqltable_test.py, but it may be more complicated than helpful, since it tries a bunch of different permutations of mysql vs. sqlite, read-only vs. writeable databases etc. Note: one thing we haven't done yet is to create a picklable object for the sqlite db connection. This would be analogous to pygr.sqlgraph.DBServerInfo. This gets passed in to the SQLTable as serverInfo (instead of passing a cursor, since cursors can't be pickled AFAIK). Making a variant of DBServerInfo for sqlite is trivial -- it just needs to remember the path to the db file, and to provide a cursor() method for getting a cursor. If you're in a hurry go ahead and write it; otherwise I'll try to remember to add this as soon as possible. -- Chris On Apr 23, 2009, at 5:39 PM, Paul Rigor (uci) wrote: > Hi gang, > > Thanks for the tip. For a couple of hours today Kenny and I tried > using an sqlite backend usng SQLTable when creating the slicedb. > I've attached the code along with some minor modifications of > sqlgraph.py in order to work with sqlite's schema. > > We were successful in creating fake annotations and creating a > mapping between seqdb and annotationdb. However, we were unable to > reload the data. For example, we get a MySQLdb error when running > the simple getResource() command. I've attached the file containing > the error messages as well. > > Is there any way to specify an sqlite resource? We're trying to > limit the complexity of our setup that's why we're not using mysql > (for now). > > File listing: > htsdb.py contains the code for generating the genome and annotation > sqlgraph.py.diff contains the diff to get sqlite's primary key > column name > sacCer.fa is the fasta file to build the SequenceFileDB > and pygr_sqlite_error.txt contains the error trace when loading the > resource. > > Thanks, > Paul > > > > > > On Wed, Apr 22, 2009 at 6:20 PM, C. Titus Brown <c...@msu.edu> wrote: > On Wed, Apr 22, 2009 at 06:14:07PM -0700, Paul Rigor (uci) wrote: > -> Hi gang, > -> I work with Kenny Daily here at UCI and he had previously sent > some sample > -> data to the group which contained all scripts/data necessary to > recreate > -> what we've found to be an odd behavior when loading a pygr > resource. (Ref: > -> > http://groups.google.com/group/pygr-dev/browse_thread/thread/98e36696870b2518/ee70b6106df72193?hl=en&lnk=gst&q=Kenny+Daily#ee70b6106df72193 > -> ) > > Is the code at > > http://www.ics.uci.edu/~baldig/pygr_nlmsa_test.tar.gz > > up to date? > > I had problems with this when I was using the wrong objects for my > basic > annotation storage; it looks to me like you're using an in-memory > dictionary to store the read locations. This in-memory dictionary is > pickled & restored by pygr.Data, and this could be where your problems > lie. > > Switch to using an on-disk dictionary (bsddb?) or a MySQL db to store > your basic info, and this won't happen. > > cheers, > --titus > > > > -- > Paul Rigor > Graduate Student > Institute for Genomics and Bioinformatics > Donald Bren School of Information and Computer Sciences > University of California in Irvine > 248 ICS2 Bldg. > +1 (760) 536 - 6767 (skype) > <pygr_sqlite_error.txt><htsdb.py><sqlgraph.py.diff><sacCer.fa> --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "pygr-dev" group. To post to this group, send email to pygr-dev@googlegroups.com To unsubscribe from this group, send email to pygr-dev+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pygr-dev?hl=en -~----------~----~----~----~------~----~------~--~---
