a workaround + warning has been committed in latest master, rel_1_0, rel_0_9 branches which will catch the KeyError here, warn, and continue.
On Sep 2, 2014, at 12:39 PM, Ivan Smirnov <[email protected]> wrote: > Yea, I copy/pasted the wrong code block, it's ALL_IND_COLUMNS of course. > > As you said, it looks like this issue calls for two (quite simple) fixes: (1) > try to avoid KeyError for index reflections in general?, (2) use ALL_TAB_COLS > for Oracle (or ignore M_ROW$$)? > > If I remember correctly, SYS_NC* columns are created typically for > "function-based" indexes -- same as M_ROW$$ is created for ROWID indexes in > materialized views; should both be treated the same way? > > On Tuesday, 2 September 2014 16:21:46 UTC+1, Michael Bayer wrote: > I think you meant "ALL_IND_COLUMNS" for indexes > > > On Sep 2, 2014, at 11:13 AM, Ivan Smirnov <[email protected]> wrote: > >> Index query: >> >> SELECT column_name, data_type, char_length, data_precision, data_scale, >> nullable, data_default >> FROM ALL_TAB_COLUMNS >> WHERE table_name = :table_name AND owner = :owner >> ORDER BY column_id >> >> returns >> >> INDEX_NAME COLUMN_NAME UNIQUENESS >> 1 I_SNAP$_FOO M_ROW$$ UNIQUE >> >> Column query: >> >> SELECT column_name, data_type, char_length, data_precision, data_scale, >> nullable, data_default >> FROM ALL_TAB_COLUMNS >> WHERE table_name = 'FOO' >> AND owner = 'BAR' >> ORDER BY column_id >> >> returns >> >> COLUMN_NAME DATA_TYPE CHAR_LENGTH DATA_PRECISION DATA_SCALE NULLABLE >> DATA_DEFAULT >> 1 A VARCHAR2 32 N >> <Long> >> 2 B FLOAT 0 126 N >> <Long> >> >> However, looking at Oracle docs here: >> http://docs.oracle.com/database/121/REFRN/refrn20277.htm#REFRN20277 -- it >> seems like all hidden columns are specifically filtered out of >> ALL_TAB_COLUMNS view. >> >> Instead, one could use ALL_TAB_COLS which seems to work and reveals the >> M_ROW$$: >> >> SELECT column_name, data_type, char_length, data_precision, data_scale, >> nullable, data_default >> FROM ALL_TAB_COLS >> WHERE table_name = 'FOO' >> AND owner = 'BAR' >> ORDER BY column_id >> >> which returns >> >> COLUMN_NAME DATA_TYPE CHAR_LENGTH DATA_PRECISION DATA_SCALE NULLABLE >> DATA_DEFAULT >> 1 A VARCHAR2 32 N >> <Long> >> 2 B FLOAT 0 126 N >> <Long> >> 3 M_ROW$$ VARCHAR2 18 Y >> <Long> >> >> >> On Tuesday, 2 September 2014 15:55:03 UTC+1, Michael Bayer wrote: >> it means that the query we do to reflect the columns in the table isn't >> coming up with this one. >> >> >> A possible workaround now is to just add this column explicitly: >> >> t = Table('mytable', m, Column('m_row$$', integer), autoload=True) >> >> >> >> On Sep 2, 2014, at 10:26 AM, Ivan Smirnov <[email protected]> wrote: >> >>> So, if I understand correctly, in this example m_row$$ is a hidden column >>> automatically generated when a materialized view is created with rowid. >>> It's still accessible though, i.e. you can still select from it explicitly. >>> >>> On Monday, 1 September 2014 18:46:39 UTC+1, Ivan Smirnov wrote: >>> py27/lib/python2.7/site-packages/sqlalchemy/engine/reflection.pyc in >>> reflecttable(self, table, include_columns, exclude_columns) >>> 591 cols_by_orig_name[c] if c in cols_by_orig_name >>> 592 else table.c[c] >>> --> 593 for c in columns >>> 594 ], >>> 595 **dict(unique=unique)) >>> >>> py27/lib/python2.7/site-packages/sqlalchemy/util/_collections.pyc in >>> __getitem__(self, key) >>> 155 >>> 156 def __getitem__(self, key): >>> --> 157 return self._data[key] >>> 158 >>> 159 def __delitem__(self, key): >>> >>> KeyError: u'm_row$$' >>> > py27/lib/python2.7/site-packages/sqlalchemy/util/_collections.py(157)__getitem__() >>> 156 def __getitem__(self, key): >>> --> 157 return self._data[key] >>> 158 >>> >>> >>> If you have an index containing a column with a non-numeric character (like >>> 'm_row$$'), reflection would fail as in the example above, since table.c >>> would not contain it. >>> >>> Is there any way around it? >>> >>> P.S. It seems to be related to this thread from 4 years ago (MS SQL): >>> https://groups.google.com/forum/#!searchin/sqlalchemy/keyerror/sqlalchemy/BcANLqW1D04/sRPMYpDLO8oJ >>> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To unsubscribe from this group and stop receiving emails from it, send an >>> email to [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at http://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
