RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
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
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
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
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
-- 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.
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
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
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
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
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
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' -- 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
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
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
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. -- 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
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. -- 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
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
-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] Warnings take a really long time / NotImplementedError
First spin though, I get these errors/warnings: /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'objid' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'LONG RAW' of column 'data' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'source_rowid' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'arowid' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'browid' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'crowid' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'at_rowid' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'drowid' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'row_a' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'row_b' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'row_c' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'textkey' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'object_id' ret = fn(self, con, *args, **kw) 2010-02-10 14:00:33,891 ERROR [SiteError] http://portal-dev.craryindustries.com/testsa.html Traceback (most recent call last): File /home/zope/.buildout/eggs/zope.publisher-3.4.9-py2.5.egg/zope/publisher/publish.py, line 133, in publish result = publication.callObject(request, obj) File /home/zope/.buildout/eggs/grok-1.0-py2.5.egg/grok/publication.py, line 89, in callObject return super(ZopePublicationSansProxy, self).callObject(request, ob) File /home/zope/.buildout/eggs/zope.app.publication-3.4.3-py2.5.egg/zope/app/publication/zopepublication.py, line 167, in callObject return mapply(ob, request.getPositionalArguments(), request) File /home/zope/.buildout/eggs/zope.publisher-3.4.9-py2.5.egg/zope/publisher/publish.py, line 108, in mapply return debug_call(obj, args) File /home/zope/.buildout/eggs/zope.publisher-3.4.9-py2.5.egg/zope/publisher/publish.py, line 114, in debug_call return obj(*args) File /home/zope/.buildout/eggs/grokcore.view-1.12.2-py2.5.egg/grokcore/view/components.py, line 64, in __call__ return mapply(self.render, (), self.request) File /home/zope/.buildout/eggs/zope.publisher-3.4.9-py2.5.egg/zope/publisher/publish.py, line 108, in mapply return debug_call(obj, args) File /home/zope/.buildout/eggs/zope.publisher-3.4.9-py2.5.egg/zope/publisher/publish.py, line 114, in debug_call return obj(*args) File /home/zope/development/grokenv/projects/portal/src/portal/lib/db/sql_alchemy_test.py, line 30, in render session = rdb.Session() File /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/orm/scoping.py, line 52, in __call__ return self.registry() File build/bdist.linux-i686/egg/sqlalchemy/util.py, line 1225, in __call__ File /home/zope/.buildout/eggs/z3c.saconfig-0.9.1-py2.5.egg/z3c/saconfig/scopedsession.py, line 12, in session_factory return utility.sessionFactory() File /home/zope/.buildout/eggs/z3c.saconfig-0.9.1-py2.5.egg/z3c/saconfig/utility.py, line 70, in sessionFactory kw['bind'] = engine_factory() File /home/zope/.buildout/eggs/z3c.saconfig-0.9.1-py2.5.egg/z3c/saconfig/utility.py, line 165, in __call__ notify(EngineCreatedEvent(engine)) File /home/zope/.buildout/eggs/zope.event-3.4.0-py2.5.egg/zope/event/__init__.py, line 23, in notify subscriber(event) File /home/zope/.buildout/eggs/zope.component-3.4.0-py2.5.egg/zope/component/event.py, line 26, in dispatch for ignored in zope.component.subscribers(event, None): File
Re: [sqlalchemy] Warnings take a really long time / NotImplementedError
On Feb 10, 2010, at 3:28 PM, Jeff Peterson wrote: First spin though, I get these errors/warnings: /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'objid' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'LONG RAW' of column 'data' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'source_rowid' ret = fn(self, con, *args, **kw) these are oracle column types that aren't present in the reflected types list. this error is harmless (assuming you don't issue CREATE TABLE like you're doing later). File /home/zope/.buildout/eggs/megrok.rdb-0.10-py2.5.egg/megrok/rdb/setup.py, line 93, in createTables metadata.create_all(engine) NotImplementedError: Can't generate DDL for the null type this is more of a problem. you're reflecting views (and I assume table objects) from your database, and then emitting metadata.create_all() - the views you've reflected are assumed to be tables, which don't exist, and it attempts to issue CREATE TABLE for them, and fails due to the types missing above (but luckily, else it would generate a new table for every view). You shouldn't be calling create_all(). Especially not in a web application when it starts up, thats kind of crazy, and also not when your application receives its table metadata through reflection. Is there any way to skip/speed up the unrecognized column warnings? what evidence do you have that the warnings themselves are slow ? just because thats what you see actually dumped in your output has no relevance to the work that is actually going on, in this case, the vast majority of columns being reflected that do *not* generate any warning, since you would appear to be reflecting at least 12 views. Reflecting a whole database is not a quick operation. -- 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.
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
-- 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 2:38 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Warnings take a really long time / NotImplementedError On Feb 10, 2010, at 3:28 PM, Jeff Peterson wrote: First spin though, I get these errors/warnings: /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'objid' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'LONG RAW' of column 'data' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'source_rowid' ret = fn(self, con, *args, **kw) these are oracle column types that aren't present in the reflected types list. this error is harmless (assuming you don't issue CREATE TABLE like you're doing later). File /home/zope/.buildout/eggs/megrok.rdb-0.10-py2.5.egg/megrok/rdb/setup.py, line 93, in createTables metadata.create_all(engine) NotImplementedError: Can't generate DDL for the null type this is more of a problem. you're reflecting views (and I assume table objects) from your database, and then emitting metadata.create_all() - the views you've reflected are assumed to be tables, which don't exist, and it attempts to issue CREATE TABLE for them, and fails due to the types missing above (but luckily, else it would generate a new table for every view). You shouldn't be calling create_all(). Especially not in a web application when it starts up, thats kind of crazy, and also not when your application receives its table metadata through reflection. The module calling create_all() is a third party lib for Grok, which clearly doesn't handle reflection very well as it makes that create call regardless. It may be I need to step back and try and handle the SQLA stuff on my own. Is there any way to skip/speed up the unrecognized column warnings? what evidence do you have that the warnings themselves are slow ? just because thats what you see actually dumped in your output has no relevance to the work that is actually going on, in this case, the vast majority of columns being reflected that do *not* generate any warning, since you would appear to be reflecting at least 12 views. Reflecting a whole database is not a quick operation. I am, in fact, only reflecting one view, but you got me to thinking, that view has 50+ columns and joins in two other views, does it attempt to reflects all the tables/view that make up that view? -- 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.
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Wednesday, February 10, 2010 2:38 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Warnings take a really long time / NotImplementedError On Feb 10, 2010, at 3:28 PM, Jeff Peterson wrote: First spin though, I get these errors/warnings: /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'objid' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'LONG RAW' of column 'data' ret = fn(self, con, *args, **kw) /home/zope/.buildout/eggs/SQLAlchemy-0.6-py2.5.egg/sqlalchemy/engine/reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'source_rowid' ret = fn(self, con, *args, **kw) these are oracle column types that aren't present in the reflected types list. this error is harmless (assuming you don't issue CREATE TABLE like you're doing later). File /home/zope/.buildout/eggs/megrok.rdb-0.10-py2.5.egg/megrok/rdb/setup.py, line 93, in createTables metadata.create_all(engine) NotImplementedError: Can't generate DDL for the null type this is more of a problem. you're reflecting views (and I assume table objects) from your database, and then emitting metadata.create_all() - the views you've reflected are assumed to be tables, which don't exist, and it attempts to issue CREATE TABLE for them, and fails due to the types missing above (but luckily, else it would generate a new table for every view). You shouldn't be calling create_all(). Especially not in a web application when it starts up, thats kind of crazy, and also not when your application receives its table metadata through reflection. The module calling create_all() is a third party lib for Grok, which clearly doesn't handle reflection very well as it makes that create call regardless. It may be I need to step back and try and handle the SQLA stuff on my own. Is there any way to skip/speed up the unrecognized column warnings? what evidence do you have that the warnings themselves are slow ? just because thats what you see actually dumped in your output has no relevance to the work that is actually going on, in this case, the vast majority of columns being reflected that do *not* generate any warning, since you would appear to be reflecting at least 12 views. Reflecting a whole database is not a quick operation. I am, in fact, only reflecting one view, but you got me to thinking, that view has 50+ columns and joins in two other views, does it attempt to reflects all the tables/view that make up that view? However, I had made some changes to the lib to allow the reflected views to be skipped basically removing them from the metadata, which worked, the views were reflected but it still took 30-40 seconds for it to reflect this one view. The code for this: class CrarySalesPart(rdb.Model): rdb.reflected() rdb.tablename('crary_sales_part') rdb.tableargs(schema='crar1app', useexisting=True) contract = Column('contract', String, nullable=False, primary_key=True) catalog_no = Column('catalog_no', String, nullable=False, primary_key=True) class Index(grok.View): grok.context(Portal) grok.name('testsa.html') def render(self): session = rdb.Session() sp = session.query(CrarySalesPart).filter(CrarySalesPart.contract=='20').limit(10) msg = ''.join(['p style=padding:0;margin:0%s: %s, %s/p' % (o.catalog_no, o.catalog_desc, o.part_product_code) for o in sp]) return htmlhead/headbody%s/body/html % msg The first time I render that view, the reflection takes place and it takes the 30-40 seconds to load the page (during which time the warnings are being generated), once it's mapped it is very fast. -- 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.
Re: [sqlalchemy] Warnings take a really long time / NotImplementedError
On Feb 10, 2010, at 4:13 PM, Jeff Peterson wrote: The first time I render that view, the reflection takes place and it takes the 30-40 seconds to load the page (during which time the warnings are being generated), once it’s mapped it is very fast. You should probably have reflection occur when your app starts, not when a page is hit, but anyway, I dont know why that view takes 40 seconds to reflect and I would advise enabling SQL echoing to see what queries are taking place and roughly how long they take to complete. -- 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.
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
-- 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 3:18 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Warnings take a really long time / NotImplementedError On Feb 10, 2010, at 4:13 PM, Jeff Peterson wrote: The first time I render that view, the reflection takes place and it takes the 30-40 seconds to load the page (during which time the warnings are being generated), once it's mapped it is very fast. You should probably have reflection occur when your app starts, not when a page is hit, but anyway, I dont know why that view takes 40 seconds to reflect and I would advise enabling SQL echoing to see what queries are taking place and roughly how long they take to complete. 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. The other thing I am noticing is the code in the lib does this: reflectTables(metadata) createTables(metadata) notify(DatabaseSetupEvent(metadata)) reflectTables does, among other things, a metadata.reflect(bind=engine) createTables looks like: def createTables(metadata): Create class-specified tables. engine = Engine() metadata.create_all(engine) So, the metadata has (or could have) both reflected tables/views and tables needing to be created, which would be fine assuming the reflected class is actually a table in the DB, which we know in this case it isn't it's a view and so it tried to create it. So, the issue is what's the best solution for stopping this? Should the reflected views simply be removed from the metadata after reflection? Or is there some simpler answer? TIA, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.commailto:sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.commailto: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 sqlalchemy@googlegroups.commailto:sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.commailto: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 sqlalchemy@googlegroups.commailto:sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.commailto: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.
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. The other thing I am noticing is the code in the lib does this: reflectTables(metadata) createTables(metadata) notify(DatabaseSetupEvent(metadata)) reflectTables does, among other things, a metadata.reflect(bind=engine) createTables looks like: def createTables(metadata): Create class-specified tables. engine = Engine() metadata.create_all(engine) So, the metadata has (or could have) both reflected tables/views and tables needing to be created, which would be fine assuming the reflected class is actually a table in the DB, which we know in this case it isn’t it’s a view and so it tried to create it. So, the issue is what’s the best solution for stopping this? Should the reflected views simply be removed from the metadata after reflection? Or is there some simpler answer? as i said, it makes no sense at all for your web app to be calling create_all() when it starts up. create_all() should only be used when you are initially configuring your database or migrating its schema, things that should not happen implicitly. If you must call create_all(), use a separate MetaData object for your reflection step. Its not a good idea to attempt to issue CREATE TABLE for all the tables that you've just reflected. TIA, -- 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.