[sqlalchemy] Re: [Grok-dev] [Solved? ] Creating a simple 1:2 relationship with MeGrok and SqlAlchemy
Would make a nice addition to the ORM docs on grok.zope.org. -- Jeff Peterson Sent from my iPod On Nov 1, 2010, at 5:31 PM, Hector Blanco white.li...@gmail.com wrote: Hi List... I have been asking a lot lately about a 1:2 relationship with MeGrok and SqlAlchemy, and I think I've solved it. I have created a mini how to just in case it could help anyone. ODT http://www.hectorblanco.org/files/odt/Megrok%20Relation%201:2.odt PDF http://www.hectorblanco.org/files/pdf/Megrok%20Relation%201:2.pdf If you wanna take a look, and criticize, correct... whatever, I'll be thankful. I'd like to thank to everyone who helped me. And thank you to everyone who tried, even by reading my lng emails. ___ Grok-dev mailing list grok-...@zope.org https://mail.zope.org/mailman/listinfo/grok-dev -- 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] Containers/collections with SQLAlchemy
http://grok.zope.org/documentation/how-to/orm-using-megrok.rdb-and-sqlalchemy http://www.sqlalchemy.org/docs/ -- Jeffrey D Peterson Webmaster Crary Industries, Inc. 237 12th St NW West Fargo, ND 58078 P: 701-499-5928 E: jeff.peter...@crary.com -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Hector Blanco Sent: Tuesday, October 26, 2010 5:40 PM To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Containers/collections with SQLAlchemy Hi group! I am trying to migrate my application from ZopeDB to MySql. I am using sqlalchemy under megrok.rdb. I have a class which inherits from list() and has a few extra methods. Reading the chapter about custom collections in sqlalchemy (http://www.sqlalchemy.org/docs/orm/collections.html#custom-collection- implementations), I thought that I'd be great having that class as a custom collection (extending from list() but simulating a set(), where the items can't be repeated) That class that extends from list, acts as a container of other classes (mmm... yeah, as all the collections do... pretty obvious). It is used (as an attribute) in other classes as well: class MyEntry(object): def __init__(self): self.field1 = field1 self.field2 = field2 class MyContainer(list): __emulates__ = set def __init__(self): super(MyContainer,self).__init__() def add(self, myEntry): if isinstance(myEntry, MyEntry): if not(myEntry in self): super(MyContainer, self).append(myEntry) def getByField1(self, field1): for element in self: if element.field1 == field1: return element return None # [ ... # more useful methods, # overloading to make the list behave like a set, # yada yada yada # ...] class MyClass(object): def __init__(self): self.container1 = MyContainer() self.container2 = MyContainer() self.anotherField = hello world def getContainer1(): return self.container1 def getContainer2(): return self.container2 I see clearly the MyEntry and (more or less clearly) MyClass classes modeled in tables. I also see clearly the intermediate table for MyContainer: my_containers_table = Table( my_containers_table, metadata, Column(id, Integer, primary_key=True), Column(my_entry_id, Integer, ForeignKey(my_entries_table.id)) ) So, in the MyClass class, each of MyContainer() instances can have an id and when someone wants to retrieve the MyEntry() elements that are in container1 (to say so), the my_containers_table can be used as a middle table to get said MyEntries. but I don't know how to link the MyContainer(list) object with my_containers_table (and from there with MyClass) :-( I'm not even sure whether MyClass.container1 and MyClass.container2 should be ForeignKeys or Relations. How can I establish the relationship between MyClass.container1 or MyClass.container2 with my_containers_table? On one hand, I want MyClass.container1 and MyClass.container2 to be foreign keys, but on the other, I want them to be instances of MyContainer(list)... And that's where I start banging my head on the wall :-) In my mind (preferably before banging it against the wall) I see this schema: +---Entry+ | id = 1| |field1 | +container1---+ |field2 | |id = 10| +-+ | foreign[0] = 1 | | foreign[1] = 2 |+- myClass + +---Entry---++--+ | id = 101 | |id = 2 | | anotherField| |field1 | | container1 = 10 | |field2 | +container2---+| container2 = 20 | +-+ | id = 20 | +--- --+ | foreign[0] = 3 | +---Entry---++-+ |id = 3| |field1 | |field2 | ++ [I hope the Ascii thing is properly displayed] When I want to get all what is in myClass.container1, the system should go to my_containers_table with the myClass.container1's id (10) and retrieve all the MyEntries (id=1 and id=2 in the example above) pointed by the ForeingKey of my_containers_table. That's what I want the system to do. But that's not what it's doing. Any tip will be deeply appreciated. Links to manuals, documentations... whatever (I'm a total newbie in sqlmyalchemy) Thank you again! -- You
[sqlalchemy] Microsoft Access
Microsoft Access is supported, albeit experimentally in 0.5.8. Will this functionality make it past that at any point or has that been basically scrapped? -- Jeffrey D Peterson Webmaster Crary Industries, Inc. 237 12th St NW West Fargo, ND 58078 P: 701-499-5928 E: jeff.peter...@crary.com -- 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.
[sqlalchemy] Calling functions from a package from the session
I have a function in an Oracle package called CRARY_WEB_USERS_SECURITY_API it looks like this: function GetPassword( parent_ in varchar2, user_ in varchar2 ) return varchar2; How would I call that function from a session? Or can I even do that. TIA, -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -- 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 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
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
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
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
-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
[sqlalchemy] RE: Warnings take a really long time / NotImplementedError
BTW, this is using 0.6 beta1 build 6743 on Grok, reflecting a view from an Oracle (10.2) 10g DB. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Jeff Peterson Sent: Wednesday, February 10, 2010 2:29 PM To: sqlalchemy@googlegroups.com Subject: [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
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
-- 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] Oracle, ownership and unrecognized type warnings
Sorry for not getting back sooner.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Wednesday, January 06, 2010 1:23 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Oracle, ownership and unrecognized type warnings
crary_web wrote:
For production I will need to connect as user webserv who has no
ownership at all only select grants, and will only have access to
views. Currently, with what I have deciphered for myself, I can't do
this. I cannot reflect a view at all, it complains about primary keys
which I can understand, but is there a way around this as it's not
practical to ask our DBA to put pks on the hundreds of views I will
possibly need to access, if he can at all.
its true that there's no built in functionality to reflect views. In the
case of your views, just create Table instances manually, specifying the
view names, column names, and column types explicitly. Set the
primary_key=True flag on those columns which you'd like to consider as
part of the primary key within your application. No database change is
necessary.
So, does doing this actually create a new table in the DB? I tried this, and
in my instance it failed, but I could see it trying to run a create table
command. Is this the way it works? Is there no other way? The user I connect
with will not have privileges to do this.
If you need to reflect an actual table in the DB, and you'd like to override
what columns are considered as part of the primary key within your
application, you specify those columns explicitly as in
http://www.sqlalchemy.org/docs/05/metadata.html#overriding-reflected-columns.
I can reflect a table as long as I connect as the table owner which as
I stated before I will not be able to do. What configuration flag am
I missing or parameter I am not passing to make this ok?
pass the schema='someowner' flag to each Table object.
This worked, for the case of tables. FYI, using megrok.rdb you must include
the class property __table_args__ as a dictionary i.e.
Class ReflectedTable(megrok.rdb.Model):
megrok.rdb.reflected()
__table_args__ = {'schema':'someowner'}
--
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] Oracle, ownership and unrecognized type warnings
Hmmm, must be something megrok.rdb is doing I am not seeing, I will look into it. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Monday, January 11, 2010 2:31 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Oracle, ownership and unrecognized type warnings Jeff Peterson wrote: its true that there's no built in functionality to reflect views. In the case of your views, just create Table instances manually, specifying the view names, column names, and column types explicitly. Set the primary_key=True flag on those columns which you'd like to consider as part of the primary key within your application. No database change is necessary. So, does doing this actually create a new table in the DB? no. SQLAlchemy never creates tables unless you tell it to. By create, i meant, sometable = Table(...), i.e., you are creating a Python object in your application which represents the structure of a table or view that is present in the database. *Not* calling table.create(). -- 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] Oracle, ownership and unrecognized type warnings
OK, it is definitely megrok.rdb, the last thing it does is call metadata.create_all() so, I will email the megrok folks. Thanks a bunch. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Jeff Peterson Sent: Monday, January 11, 2010 3:14 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Oracle, ownership and unrecognized type warnings Hmmm, must be something megrok.rdb is doing I am not seeing, I will look into it. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Monday, January 11, 2010 2:31 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Oracle, ownership and unrecognized type warnings Jeff Peterson wrote: its true that there's no built in functionality to reflect views. In the case of your views, just create Table instances manually, specifying the view names, column names, and column types explicitly. Set the primary_key=True flag on those columns which you'd like to consider as part of the primary key within your application. No database change is necessary. So, does doing this actually create a new table in the DB? no. SQLAlchemy never creates tables unless you tell it to. By create, i meant, sometable = Table(...), i.e., you are creating a Python object in your application which represents the structure of a table or view that is present in the database. *Not* calling table.create(). -- 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.
