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 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.
