Michael Bayer wrote: > If you prefer, you can reflect your database once and store the resulting > MetaData (or individual Table objects) into a pickled datafile. Your > application can then read the datafile upon startup to configure its > previously loaded table metadata. > > The serializer extension makes this possible, requiring just a MetaData > object when deserializing (the Session is optional). Pass a MetaData or > Table to serializer.dumps() to generate the dump and use > serializer.loads() to load it back: > > http://www.sqlalchemy.org/docs/reference/ext/serializer.html
slight adjustment. don't use the serializer extension, just use plain pickle.dumps()/loads() of the whole MetaData object. > > > > Jeff Peterson wrote: > >>> -----Original Message----- >>> From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] >>> On Behalf Of Michael Bayer >>> Sent: Thursday, February 11, 2010 1:26 PM >>> To: sqlalchemy@googlegroups.com >>> Subject: RE: [sqlalchemy] Warnings take a really long time / >>> NotImplementedError >>> >>> Jeff Peterson wrote: >>> > The view name itself isn't but the names of all the tables that make >>> up >>> > that view are. So I guess that must be why. >>> >>> It is only looking at the columns declared in your view - the Table >>> reflection logic doesn't actually look at the original definition of >>> the >>> view (there is a function for that available but that's not what you're >>> using here). >>> >>> I'm not familiar with what Oracle does here but if it places view >>> columns >>> into ALL_CONS_COLUMNS corresponding to the table column they represent, >>> that would be the effect. But it seems strange that would be the case, >>> since there's no "constraint" on your view. >>> >>> The other possibility is that you are actually reflecting tables >>> somewhere >>> else. >> >> If I am it's not on purpose. ;) I was able to make one observation >> though...during my test, trying to get all the kinks worked out I setup >> 2 >> connection strings, 1) the schema owner (who has rights to everything) >> and >> 2) my limited user that only has select rights on certain views. When >> this happens, I am connected as the schema user. When connected as the >> limited user it's lightning fast (I commented out the create code in the >> lib, I can't create new tables as it sits but it'll reflect just fine). >> So, bottom line is, despite the strangeness, I guess I can, just not >> worry >> about it, at least for now. But it's clear that when it can't touch >> those >> tables it doesn't perform those commands. >> >>> >>> >>> > >>> > -- >>> > Jeffrey D Peterson >>> > Webmaster >>> > Crary Industries, Inc. >>> > >>> > >>> >> -----Original Message----- >>> >> From: sqlalchemy@googlegroups.com >>> [mailto:sqlalch...@googlegroups.com] >>> >> On Behalf Of Michael Bayer >>> >> Sent: Thursday, February 11, 2010 12:59 PM >>> >> To: sqlalchemy@googlegroups.com >>> >> Subject: RE: [sqlalchemy] Warnings take a really long time / >>> >> NotImplementedError >>> >> >>> >> Jeff Peterson wrote: >>> >> > That is the troubling part, I am reflecting a view, and yet it is >>> >> still >>> >> > touching all those tables in the DB for schema='CRAR1APP' >>> >> >>> >> does the name of your view appear at all in >>> ALL_CONS_COLUMNS.TABLE_NAME >>> >> ? >>> >> that's the only way reflection of a view could get the name of a >>> table >>> >> to >>> >> reflect. if you turn on echo='debug' or set "sqlalchemy.engine" >>> to >>> >> DEBUG level logging, you'd see all the rows returned from every >>> query. >>> >> >>> >> >>> >> > >>> >> > -- >>> >> > Jeffrey D Peterson >>> >> > Webmaster >>> >> > Crary Industries, Inc. >>> >> > >>> >> > >>> >> >> -----Original Message----- >>> >> >> From: sqlalchemy@googlegroups.com >>> >> [mailto:sqlalch...@googlegroups.com] >>> >> >> On Behalf Of Michael Bayer >>> >> >> Sent: Thursday, February 11, 2010 12:05 PM >>> >> >> To: sqlalchemy@googlegroups.com >>> >> >> Subject: RE: [sqlalchemy] Warnings take a really long time / >>> >> >> NotImplementedError >>> >> >> >>> >> >> I thought you were reflecting a view ? a table will fan out to >>> all >>> >> of >>> >> >> its >>> >> >> constraints, yes. >>> >> >> >>> >> >> >>> >> >> Jeff Peterson wrote: >>> >> >> > Right, and there is that same code outputted for every table >>> in >>> >> the >>> >> >> > schema, when reflecting that one view. What I posted was just >>> the >>> >> >> one >>> >> >> > snippet, it is repeated over and over for each different table. >>> >> >> > >>> >> >> > -- >>> >> >> > Jeffrey D Peterson >>> >> >> > Webmaster >>> >> >> > Crary Industries, Inc. >>> >> >> > >>> >> >> >> -----Original Message----- >>> >> >> >> From: sqlalchemy@googlegroups.com >>> >> >> [mailto:sqlalch...@googlegroups.com] >>> >> >> >> On Behalf Of Michael Bayer >>> >> >> >> Sent: Thursday, February 11, 2010 11:46 AM >>> >> >> >> To: sqlalchemy@googlegroups.com >>> >> >> >> Subject: RE: [sqlalchemy] Warnings take a really long time / >>> >> >> >> NotImplementedError >>> >> >> >> >>> >> >> >> that SQL output is specific to 'table_name': >>> >> >> 'CFA_CASH_FLOW_STATUS_TAB' >>> >> >> >> and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables. >>> >> >> >> >>> >> >> >> >>> >> >> >> Jeff Peterson wrote: >>> >> >> >> > >>> >> >> >> > >>> >> >> >> > -- >>> >> >> >> > Jeffrey D Peterson >>> >> >> >> > Webmaster >>> >> >> >> > Crary Industries, Inc. >>> >> >> >> > >>> >> >> >> > From: sqlalchemy@googlegroups.com >>> >> >> >> [mailto:sqlalch...@googlegroups.com] On >>> >> >> >> > Behalf Of Michael Bayer >>> >> >> >> > Sent: Wednesday, February 10, 2010 6:30 PM >>> >> >> >> > To: sqlalchemy@googlegroups.com >>> >> >> >> > Subject: Re: [sqlalchemy] Warnings take a really long time / >>> >> >> >> > NotImplementedError >>> >> >> >> > >>> >> >> >> > >>> >> >> >> > On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote: >>> >> >> >> > >>> >> >> >> > >>> >> >> >> > >>> >> >> >> > It's touching a ton of tables, hundreds...if I had to guess >>> I'd >>> >> >> say >>> >> >> >> every >>> >> >> >> > table in the schema. The reasons for this are unknown to >>> me, >>> >> >> >> certainly >>> >> >> >> > all those tables are not related specifically to the single >>> >> view I >>> >> >> am >>> >> >> >> > attempting to reflect. >>> >> >> >> > >>> >> >> >> > that shouldn't be possible on a single table reflect, if its >>> a >>> >> >> view. >>> >> >> >> > Views have no foreign key metadata so it would have no >>> reason >>> >> to >>> >> >> go >>> >> >> >> > anywhere else. you'd have to provide more specifics in >>> order >>> >> for >>> >> >> us >>> >> >> >> to >>> >> >> >> > see how that might be reproduced. >>> >> >> >> > >>> >> >> >> > Seeing this for basicall(I think) everything in the Schema: >>> >> >> >> > >>> >> >> >> > INFO [sqlalchemy.engine.base.Engine.0x...5bcc] >>> >> >> >> > SELECT a.index_name, a.column_name, b.uniqueness >>> >> >> >> > FROM ALL_IND_COLUMNS a, >>> >> >> >> > ALL_INDEXES b >>> >> >> >> > WHERE >>> >> >> >> > a.index_name = b.index_name >>> >> >> >> > AND a.table_owner = b.table_owner >>> >> >> >> > AND a.table_name = b.table_name >>> >> >> >> > >>> >> >> >> > AND a.table_name = :table_name >>> >> >> >> > AND a.table_owner = :schema >>> >> >> >> > ORDER BY a.index_name, a.column_position >>> >> >> >> > 2010-02-11 11:01:43,410 INFO >>> >> >> sqlalchemy.engine.base.Engine.0x...5bcc >>> >> >> >> > {'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': >>> >> 'CRAR1APP'} >>> >> >> >> > 2010-02-11 11:01:43,410 INFO >>> >> >> >> [sqlalchemy.engine.base.Engine.0x...5bcc] >>> >> >> >> > {'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': >>> >> 'CRAR1APP'} >>> >> >> >> > 2010-02-11 11:01:43,413 INFO >>> >> >> sqlalchemy.engine.base.Engine.0x...5bcc >>> >> >> >> > SELECT column_name, data_type, data_length, data_precision, >>> >> >> >> data_scale, >>> >> >> >> > nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name >>> = >>> >> >> >> :table_name >>> >> >> >> > AND owner = :owner ORDER BY column_id >>> >> >> >> > 2010-02-11 11:01:43,413 INFO >>> >> >> >> [sqlalchemy.engine.base.Engine.0x...5bcc] >>> >> >> >> > SELECT column_name, data_type, data_length, data_precision, >>> >> >> >> data_scale, >>> >> >> >> > nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name >>> = >>> >> >> >> :table_name >>> >> >> >> > AND owner = :owner ORDER BY column_id >>> >> >> >> > 2010-02-11 11:01:43,414 INFO >>> >> >> sqlalchemy.engine.base.Engine.0x...5bcc >>> >> >> >> > {'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'} >>> >> >> >> > 2010-02-11 11:01:43,414 INFO >>> >> >> >> [sqlalchemy.engine.base.Engine.0x...5bcc] >>> >> >> >> > {'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'} >>> >> >> >> > 2010-02-11 11:01:43,421 INFO >>> >> >> sqlalchemy.engine.base.Engine.0x...5bcc >>> >> >> >> > SELECT >>> >> >> >> > ac.constraint_name, >>> >> >> >> > ac.constraint_type, >>> >> >> >> > loc.column_name AS local_column, >>> >> >> >> > rem.table_name AS remote_table, >>> >> >> >> > rem.column_name AS remote_column, >>> >> >> >> > rem.owner AS remote_owner, >>> >> >> >> > loc.position as loc_pos, >>> >> >> >> > rem.position as rem_pos >>> >> >> >> > FROM all_constraints ac, >>> >> >> >> > all_cons_columns loc, >>> >> >> >> > all_cons_columns rem >>> >> >> >> > WHERE ac.table_name = :table_name >>> >> >> >> > AND ac.constraint_type IN ('R','P') >>> >> >> >> > AND ac.owner = :owner >>> >> >> >> > AND ac.owner = loc.owner >>> >> >> >> > AND ac.constraint_name = loc.constraint_name >>> >> >> >> > AND ac.r_owner = rem.owner(+) >>> >> >> >> > AND ac.r_constraint_name = rem.constraint_name(+) >>> >> >> >> > AND (rem.position IS NULL or >>> >> loc.position=rem.position) >>> >> >> >> > ORDER BY ac.constraint_name, loc.position >>> >> >> >> > 2010-02-11 11:01:43,421 INFO >>> >> >> >> [sqlalchemy.engine.base.Engine.0x...5bcc] >>> >> >> >> > SELECT >>> >> >> >> > ac.constraint_name, >>> >> >> >> > ac.constraint_type, >>> >> >> >> > loc.column_name AS local_column, >>> >> >> >> > rem.table_name AS remote_table, >>> >> >> >> > rem.column_name AS remote_column, >>> >> >> >> > rem.owner AS remote_owner, >>> >> >> >> > loc.position as loc_pos, >>> >> >> >> > rem.position as rem_pos >>> >> >> >> > FROM all_constraints ac, >>> >> >> >> > all_cons_columns loc, >>> >> >> >> > all_cons_columns rem >>> >> >> >> > WHERE ac.table_name = :table_name >>> >> >> >> > AND ac.constraint_type IN ('R','P') >>> >> >> >> > AND ac.owner = :owner >>> >> >> >> > AND ac.owner = loc.owner >>> >> >> >> > AND ac.constraint_name = loc.constraint_name >>> >> >> >> > AND ac.r_owner = rem.owner(+) >>> >> >> >> > AND ac.r_constraint_name = rem.constraint_name(+) >>> >> >> >> > AND (rem.position IS NULL or >>> >> loc.position=rem.position) >>> >> >> >> > ORDER BY ac.constraint_name, loc.position >>> >> >> >> > 2010-02-11 11:01:43,421 INFO >>> >> >> sqlalchemy.engine.base.Engine.0x...5bcc >>> >> >> >> > {'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'} >>> >> >> >> > >>> >> >> >> > 2010-02-111:01:43,421 INFO >>> >> >> [sqlalchemy.engine.base.Engine.0x...5bcc] >>> >> >> >> > {'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'} >>> >> >> >> > >>> >> >> >> > >>> >> >> >> > >>> >> >> >> > * Snip * >>> >> >> >> > >>> >> >> >> > -- >>> >> >> >> > You received this message because you are subscribed to the >>> >> Google >>> >> >> >> Groups >>> >> >> >> > "sqlalchemy" group. >>> >> >> >> > To post to this group, send email to >>> >> sqlalch...@googlegroups.com. >>> >> >> >> > To unsubscribe from this group, send email to >>> >> >> >> > sqlalchemy+unsubscr...@googlegroups.com. >>> >> >> >> > For more options, visit this group at >>> >> >> >> > http://groups.google.com/group/sqlalchemy?hl=en. >>> >> >> >> > >>> >> >> >> > >>> >> >> >> >>> >> >> >> -- >>> >> >> >> You received this message because you are subscribed to the >>> >> Google >>> >> >> >> Groups "sqlalchemy" group. >>> >> >> >> To post to this group, send email to >>> sqlalch...@googlegroups.com. >>> >> >> >> To unsubscribe from this group, send email to >>> >> >> >> sqlalchemy+unsubscr...@googlegroups.com. >>> >> >> >> For more options, visit this group at >>> >> >> >> http://groups.google.com/group/sqlalchemy?hl=en. >>> >> >> > >>> >> >> > -- >>> >> >> > You received this message because you are subscribed to the >>> Google >>> >> >> Groups >>> >> >> > "sqlalchemy" group. >>> >> >> > To post to this group, send email to >>> sqlalch...@googlegroups.com. >>> >> >> > To unsubscribe from this group, send email to >>> >> >> > sqlalchemy+unsubscr...@googlegroups.com. >>> >> >> > For more options, visit this group at >>> >> >> > http://groups.google.com/group/sqlalchemy?hl=en. >>> >> >> > >>> >> >> > >>> >> >> >>> >> >> -- >>> >> >> You received this message because you are subscribed to the >>> Google >>> >> >> Groups "sqlalchemy" group. >>> >> >> To post to this group, send email to sqlalch...@googlegroups.com. >>> >> >> To unsubscribe from this group, send email to >>> >> >> sqlalchemy+unsubscr...@googlegroups.com. >>> >> >> For more options, visit this group at >>> >> >> http://groups.google.com/group/sqlalchemy?hl=en. >>> >> > >>> >> > -- >>> >> > You received this message because you are subscribed to the Google >>> >> Groups >>> >> > "sqlalchemy" group. >>> >> > To post to this group, send email to sqlalch...@googlegroups.com. >>> >> > To unsubscribe from this group, send email to >>> >> > sqlalchemy+unsubscr...@googlegroups.com. >>> >> > For more options, visit this group at >>> >> > http://groups.google.com/group/sqlalchemy?hl=en. >>> >> > >>> >> > >>> >> >>> >> -- >>> >> You received this message because you are subscribed to the Google >>> >> Groups "sqlalchemy" group. >>> >> To post to this group, send email to sqlalch...@googlegroups.com. >>> >> To unsubscribe from this group, send email to >>> >> sqlalchemy+unsubscr...@googlegroups.com. >>> >> For more options, visit this group at >>> >> http://groups.google.com/group/sqlalchemy?hl=en. >>> > >>> > -- >>> > You received this message because you are subscribed to the Google >>> Groups >>> > "sqlalchemy" group. >>> > To post to this group, send email to sqlalch...@googlegroups.com. >>> > To unsubscribe from this group, send email to >>> > sqlalchemy+unsubscr...@googlegroups.com. >>> > For more options, visit this group at >>> > http://groups.google.com/group/sqlalchemy?hl=en. >>> > >>> > >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "sqlalchemy" group. >>> To post to this group, send email to sqlalch...@googlegroups.com. >>> To unsubscribe from this group, send email to >>> sqlalchemy+unsubscr...@googlegroups.com. >>> For more options, visit this group at >>> http://groups.google.com/group/sqlalchemy?hl=en. >> >> -- >> You received this message because you are subscribed to the Google >> Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalch...@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
