Michael,
I've found the problem when I stepped back and started from scratch.
It turns out that the error message is actually just a warning (and
I'm still getting it) but the schema reflection works despite this
message. I wrongly assumed this is why my script is failing but it's
actually due to this part of my schema declaration:
forecasts_line_items = Table('forecasts_line_items', metadata,
...
Column('line_item_id', Integer, ForeignKey('line_items.id')),
...
autoload=True, useexisting=True, schema=conf.schema
)
Due a problem with the schema I'm accessing -- it does not always have
FKs set up properly -- I needed to declared the FKs manually for sqla
to pick up the relations. It turns out that I need to write
'SAS.line_items.id' in this case (not sure if the upper case is really
needed but there was a problem with lower case schema prefixes in the
past). This didn't occur to me, because the schema prefix is
automatically applied in other cases, like table and sequence names.
Only when I went back to the bare minimum did I notice it starting to
fail when the ForeignKey declaration came in.
This is also the one difference to the old working code where all FKs
were in place and I never needed to specify ForeignKey(...)
Sorry for the noise,
Sven
On Aug 10, 4:10 pm, "Sven A. Schmidt" <s...@abstracture.de> wrote:
> Thanks, Michael. I wish I could update to 0.6.3 but unfortunately I'll
> have to stick with the deployed version of 0.5.5 for now. But in any
> case the good news is that this used to work once. It's just that
> quite a few parameters are at work here so it may be difficult to
> track down why it's failing now. Maybe your looking into it will give
> the angle to see what really made this break.
>
> I'm currently trying to avoid autoload=True (and thereby the failing
> schema queries, I assume) by manually specifying all columns (it's
> just 4-5 tables, fortunately). Maybe that'll help me work around the
> issue. I'll report back once I know more.
>
> Cheers,
> Sven
>
> On Aug 10, 3:44 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
>
>
> > On Aug 10, 2010, at 9:28 AM, Sven A. Schmidt wrote:
>
> > > Getting this out of the way first, because I always forget ;) :
> > > SQLAlchemy-0.5.5, Python 2.6
>
> > I'll take a look at this later but you should probably be tracking down the
> > issue in 0.6.3, assuming its still present - that's where we'd fix any
> > issues.
>
> > > I'm getting the above error when trying to connect to an Oracle schema
> > > 'TEST' and read from another schema 'SAS' where tables are exposed via
> > > synonyms (permissions have been granted) and I'm hoping that someone
> > > on this list may be able to cast some light on what's going on. I've
> > > googled for the error but only found the source where this exception
> > > is being raised.
>
> > > I've tried connecting with echo=True and running the SQL by hand from
> > > the TEST account and the strange thing is I get no error but a list of
> > > fields as I would expect (see below). First, here's the echo output of
> > > my script:
>
> > > 2010-08-10 14:56:00,221 INFO sqlalchemy.engine.base.Engine.0x...4b90
> > > 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
> > > INFO 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
> > > 2010-08-10 14:56:00,221 INFO sqlalchemy.engine.base.Engine.0x...4b90
> > > {'owner': 'SAS', 'table_name': 'FORECASTS'}
> > > INFO {'owner': 'SAS', 'table_name': 'FORECASTS'}
> > > 2010-08-10 14:56:00,230 INFO sqlalchemy.engine.base.Engine.0x...4b90
> > > 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
> > > 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(+)
> > > -- order multiple primary keys correctly
> > > ORDER BY ac.constraint_name, loc.position, rem.position
> > > INFO 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
> > > 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(+)
> > > -- order multiple primary keys correctly
> > > ORDER BY ac.constraint_name, loc.position, rem.position
> > > 2010-08-10 14:56:00,230 INFO sqlalchemy.engine.base.Engine.0x...4b90
> > > {'owner': 'SAS', 'table_name': 'FORECASTS'}
> > > INFO {'owner': 'SAS', 'table_name': 'FORECASTS'}
> > > /Library/Python/2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/
> > > sqlalchemy/engine/base.py:1265: SAWarning: Got 'None' querying
> > > 'table_name' from all_cons_columns - does the user have proper rights
> > > to the table?
> > > self.dialect.reflecttable(conn, table, include_columns)
> > > 2010-08-10 14:56:00,251 INFO sqlalchemy.engine.base.Engine.0x...4b90
> > > 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
> > > INFO 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
> > > 2010-08-10 14:56:00,251 INFO sqlalchemy.engine.base.Engine.0x...4b90
> > > {'owner': 'SAS', 'table_name': 'GROUPS'}
>
> > > I guess the error message refers to the query with the parmeters
> > > {'owner': 'SAS', 'table_name': 'FORECASTS'} (but I've also tried the
> > > one after, in case the error precedes the query -- it looks the same).
> > > So I ran the query:
>
> > > 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
> > > FROM all_constraints ac,
> > > all_cons_columns loc,
> > > all_cons_columns rem
> > > WHERE ac.table_name = 'FORECASTS'
> > > AND ac.constraint_type IN ('R','P')
> > > AND ac.owner = 'SAS'
> > > 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(+)
> > > -- order multiple primary keys correctly
> > > ORDER BY ac.constraint_name, loc.position, rem.position;
>
> > > and got
>
> > > "CONSTRAINT_NAME" "CONSTRAINT_TYPE" "LOCAL_COLUMN"
> > > "REMOTE_TABLE" "REMOTE_COLUMN" "REMOTE_OWNER"
> > > "FK5E6775D8575C2425" "R" "FORECAST_STATUS_ID" "" "" ""
> > > "FK5E6775D871226E5" "R" "SUB_GROUP_ID" "GROUPS" "ID"
> > > "SAS"
> > > "FK5E6775D87C3474A6" "R" "PROGRAMME_ID" "PROGRAMME_CFG"
> > > "ID" "SAS"
> > > "FK5E6775D88E98545F" "R" "APPROVAL_CYCLE_ID" "APPROVAL_CYCLES"
> > > "ID" "SAS"
> > > "FK5E6775D896C4452F" "R" "BUSINESS_IMPACT_ID" "" "" ""
> > > "FK5E6775D89A1530AE" "R" "DIVISION_ID" "DIVISIONS_CFG" "ID"
> > > "SAS"
> > > "FK5E6775D89F6A3DA5" "R" "BUSINESS_JUSTIFICATION_ID" "" ""
> > > ""
> > > "FK5E6775D8B85D0B4E" "R" "INITIATIVE_ID" "INITIATIVE_CFG"
> > > "ID" "SAS"
> > > "FK5E6775D8FC3CABC6" "R" "GROUP_ID" "GROUPS" "ID" "SAS"
> > > "SYS_C0057558" "P" "ID" "" "" ""
>
> > > (I hope this is readable, here's the same in Google docs:
> > >https://spreadsheets.google.com/ccc?key=0AgdbG5HyoweVdGZiN2RnM1JMY3hD...)
>
> > > I'm connecting to the database with the schema='SAS' parameter on all
> > > Table objects. I've done the same thing in the past (connecting to
> > > another schema I read from) in SQLAlchemy (same version, 0.5.5) but
> > > unfortunately the different scripts are hard to compare as such. They
> > > do look very much alike from how they connect and obtain schema
> > > information (but you're often blind to differences in your own code).
> > > I'll probably have to strip them down further in the end to track this
> > > down but this error looks like something more fundamental may be
> > > wrong.
>
> > > Does anyone on this list have an idea what I could try to investigate
> > > further? I hope I've included all the relevant infos in this (quite
> > > long, sorry!) mail. Let me know if I can provide anything else!
>
> > > Cheers,
> > > Sven
>
> > > --
> > > 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
> > > athttp://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.
