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