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]
> <javascript:>> 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] <javascript:>.
> To post to this group, send email to [email protected]
> <javascript:>.
> 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.
