Re: [sqlalchemy] Oracle, ownership and unrecognized type warnings
On Jan 6, 2010, at 2:22 PM, Michael Bayer wrote: its true that there's no built in functionality to reflect views. Correction. The 0.6 release, currently in trunk, has the capacity to reflect views fully in the same way as tables. Although constraints such as primary and foreign keys aren't part of what it loads, since views technically don't have these, so those remain elements that need to be specified explicitly when reflecting a 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.
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
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
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.
[sqlalchemy] Oracle, ownership and unrecognized type warnings
New to this group so, first, Hello to all. I have been trying to work SQLAlchemy into Grok via megrok.rdb, I was able to do it immediately for postgres without issue, and now have been at it for a bit with Oracle and I have some questions/issues. First, some background: python 2.5.4 grok 1.0 SQLA 0.5.7 and 0.6beta1 megrok.rdb 0.10.0 Here is what I am seeing: 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. 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? Is it possible at all? In my reading I get the impression it is but I can't see how. Also. When I do get it to work I get a list of warnings while reflection takes place (when using 0.5.7 this come from base.py): /home/zope/.buildout/eggs/SQLAlchemy-0.6.0-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.0-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.0-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.0-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.0-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.0-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.0-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.0-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.0-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.0-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.0-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.0-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.0-py2.5.egg/sqlalchemy/engine/ reflection.py:40: SAWarning: Did not recognize type 'ROWID' of column 'object_id' ret = fn(self, con, *args, **kw) it takes a very long time for this to finish. Once it's finished the query works and any subsequent page refresh is fast, i.e. it's not being repeated. Hopefully I have explained this well enough. TIA, Jeff Peterson -- 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
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. 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. Also. When I do get it to work I get a list of warnings while reflection takes place (when using 0.5.7 this come from base.py): that only means SQLA doesn't have a type matched up to those columns, so it will not apply any bind param or result processing to values for that column, and will also treat it agnostically when constructing expressions. -- 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.
