Hi Chris, Attached is a file containing both the generic db server info class along with a method (which can be pushed out as a stand-alone method) which obtains the primary_key for a table regardless of database type. The sqlalchemy group was very helpful with this latter functionality.
sqlite =
GenericDBServerInfo("sqlite:////home/prigor/projects/genomics/hts/src/sql/pygrdata/test_sqlite.db")
sqlite.get_primary_key()
Out[16]: u'name'
mysql = GenericDBServerInfo("mysql://
genomics:genom...@motifmap.ics.uci.edu:5000/hts")
mysql.get_primary_key()
Out[17]: u'name'
On Tue, Apr 28, 2009 at 9:24 PM, Christopher Lee <l...@chem.ucla.edu> wrote:
>
>
> On Apr 28, 2009, at 8:19 PM, Paul Rigor (gmail) wrote:
>
> > 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.
>
> Could you send some examples of your code? This would help educate me
> about how we could try to work with sqlalchemy as a back-end...
>
> >
> > 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.
>
> Just to make sure I understand, are you talking about sqlite, or about
> sqlalchemy? Pygr's current code analyzes the sqlite schema to
> determine the primary key. See sqlgraph.sqlite_table_schema() as an
> example of such a schema analysis function.
>
> SQLTable has a general mechanism for plugging in new functionality to
> analyze the schema. sqlgraph._schemaModuleDict is a dictionary that
> maps a module name (derived from the cursor object) to a schema
> analysis function. You can add your own functions to this mapping,
> and then just pass in a cursor object to SQLTable, or better yet, pass
> in a pickleable serverInfo object (which can return a usable cursor,
> to again be automatically handled by the above system...).
>
> >
> > 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.
>
> It sounds like you are using SQLTable, which will then query your back-
> end using SQL. In that case, you may want to be able to specify the
> SQL variations of your back-end, so that SQLTable will use the proper
> variant for your back-end. For examples of this, see the
> _sqliteMacros and _mysqlMacros in sqlgraph.py.
>
> If you create your own *_table_schema() function for your back-end, it
> should create an appropriate SQLFormatDict with the paramstyle and
> macro dict for that back-end. Again, see the mysql_table_schema() and
> sqlite_table_schema() code for examples.
>
>
> -- 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
-~----------~----~----~----~------~----~------~--~---
GenericDBServerInfo.py
Description: Binary data
