Hi gang, Thanks for the link to the discussion with Jenny! That was uber informative.
Chris, thanks for the SQLiteServerInfo class. I've written a more generic serverinfo that uses sqlalchemy based on the sqliteserverinfo class. Also, I don't think we need to pickle any sqlalchemy objects directly. Using the serverinfo classes, we can easily recreate connections to whichever db-backend. This generic class is pickleable so it can be saved within pygr.Data. I've been looking into how to obtain the primary key of a table as well (i'll include that in a future attachment and/or a diff to sqlgraph.py). No need for hacks since sqlalchemy provides (albeit, indirect) methods to obtain the primary key for a table. Aside from restoring the connection and obtaining the primary key, it looks like other sql statements in sqlgraph can be performed without any changes by using the cursor returned by the generic serverinfo object. As I've only truly started looking into the code the past couple of days please please let me know if I'm overlooked other functionality that could break. In any case, we'll be doing more tests tomorrow. Thanks so much for all of your advice =) Paul On Tue, Apr 28, 2009 at 1:30 PM, Christopher Lee <l...@chem.ucla.edu> wrote: > > > On Apr 28, 2009, at 12:43 PM, Paul Rigor (gmail) wrote: > > > Hi, > > > > The support for relational databases seems to be tightly coupled to > > mysql (Please correct me if I'm wrong). In sqlgraph.py for example, > > database connection defaults to mysql with an explicit import of > > MySQLdb, search for mysql configuration file variants, etc. > > In Pygr 0.7 and before, only MySQL was supported. 0.8 provides a > mechanism for handling the SQL variations of different database > servers, and adds sqlite support as a first example of that. The old > mechanisms for connecting to the database server are gradually being > deprecated in favor of the new ServerInfo mechanism (a pickleable > database connection object), which is totally general. > > > > > > Wouldn't a package like SQLalchemy provide a more transparent way of > > accessing a relational database backend? This package can handle > > several database 'engines' and provide a common interface to work > > with. > > Jenny Qian did an analysis of this a while back. You can see that > meaty discussion here: > > http://groups.google.com/group/pygr-dev/browse_thread/thread/a251addd179531e5/1cade214145543de?lnk=gst&q=sqlalchemy#1cade214145543de > > Pygr's goal is to be agnostic about back ends (i.e. work with any back- > end without prejudice). So adding support for working with SQLAlchemy > would be great. The question is whether we can *eliminate our own > support* for SQL back-ends by switching to SQLAlchemy. AFAIK, the > main problem is a major difference in philosophies. > > - Pygr follows a "pickling" model, i.e. to access an existing > resource, *no user code whatsoever* should be required, by the user > simply "saying the name" of the desired resource, it should be > automatically loaded, with all its relations to other data resources > intact. I don't think SQLAlchemy database objects are pickleable in > this way. > > - there are bunch of other issues surrounding the general Pygr > philosophy of providing a consistent graph interface to all data, with > automatic schema relations etc. > > I would be very interested if you see a way to make a SQLAlchemy > interface pickleable, so that it could be saved to pygr.Data. > > > > > > I'm still a bit unsure whether pygr actually uses the db uri stored > > in the PYGRDATAPATH environment variable to obtain the connection > > string. For sure a metabase object is instantiated with the correct > > 'dbpath', but that object doesn't seem to parse that dbpath > > parameter. Also, In loading the 'metabases', there is no way to > > explicitly specify connection parameters (user, host, password, port). > > Actually, you can specify host user password in the PYGRDATAPATH. > From the docs for SQLTable: "If cursor is None, it will attempt to > connect to a MySQL server using authentication information either from > your the name string (treated as a whitespace separated list in the > form tablename host user passwd; at least tablename and host must be > present)..." metabase uses the same syntax. We could add port easily > to this list. > > Sometime soon metabase will switch to using SQLTable explicitly, which > will make the (current) MySQLMetabase backend compatible with sqlite > (and other database servers if we add SQLTable support for them). > This would get rid of the annoying path syntaxes (such as that above) > and employ a really clean mechanism based on the general concept of > ServerInfo (i.e. a pickleable reference to a database). In other > words, a connection to a database would itself be a resource saved in > pygr.Data. > > Officially, this kind of focus on development of pygr.Data is supposed > to happen in release 1.0. But if users like you push for it, it could > be done soon. > > > > > I guess I'm still a little confused and I'm learning by placing > > print statements all over the place... > > Sorry! Updating the docs for 0.8 is high on my list of to-do's... > > -- Chris > > > > -- 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) --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
