can't modify a column name

[Ofir Herzas]

Alembic 0.8.6

cx-Oracle 5.1.3

SQLAlchemy 1.0.14

I have a column named "scope" (without the double quotes) with an existing type of SmallInt and I'm trying to change it to an Integer with the following line:

op.alter_column('t_rule', 'scope', existing_type=sa.SmallInteger(), type_=sa.Integer())

Unfortunately, this results with the following error:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00904: : invalid identifier
 [SQL: 'ALTER TABLE t_rule MODIFY scope INTEGER']

I have found out that the following syntax does work:

ALTER TABLE t_rule MODIFY "SCOPE" INTEGER;

(Notice the caption of "scope" and the double quotes)

I'm not sure why this happens since scope doesn't seem to be a reserved word.

Is it a bug or am I doing something wrong?

[Mike Bayer]

On 07/11/2016 10:48 AM, Ofir Herzas wrote:
> Alembic 0.8.6
> cx-Oracle 5.1.3
> SQLAlchemy 1.0.14
>
>
> I have a column named "scope" (without the double quotes) with an
> existing type of SmallInt and I'm trying to change it to an Integer with
> the following line:
> |
> op.alter_column('t_rule','scope',existing_type=sa.SmallInteger(),type_=sa.Integer())
> |
>
> Unfortunately, this results with the following error:
> |

> sqlalchemy.exc.DatabaseError:(cx_Oracle.DatabaseError)ORA-00904::invalid

> identifier
> [SQL:'ALTER TABLE t_rule MODIFY scope INTEGER']
> |
>
> I have found out that the following syntax does work:
> |
> ALTER TABLE t_rule MODIFY "SCOPE"INTEGER;
> |
>
> (Notice the caption of "scope" and the double quotes)
>

> I'm not sure why this happens since /scope /doesn't seem to be a

> reserved word.
>
> Is it a bug or am I doing something wrong?

If "scope" is not a reserved word, then I'd suggest that the table was
created using quoted "SCOPE" as the column name here, so that it is
case-sensitive and will only match if quoted and uppercased. If you
passed the name as all uppercase SCOPE to alter_column it should trip
the "case sensitive" flag and quote it. There are more direct ways to
turn on the quoting if that doesn't work.




>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Ofir Herzas]

For sure the table was not created using capital letters or quotes but I'll try your suggestion, thanks.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy-alembic/FwVRUgcdVtg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy-alem...@googlegroups.com.

[Ofir Herzas]

It seems that I am wrong since I do see all column names in uppercase using sql developer (all but reserved words) so I don't know how this happened since my models are all lowercase (could it be Base.metadata.create_all?) but that is indeed the problem.

Using 'SCOPE' in the alter method does solve the issue (I just hope it wouldn't introduce problems with mysql...)

Thanks.

an email to sqlalchemy-alembic+unsub...@googlegroups.com
<mailto:sqlalchemy-alembic+unsub...@googlegroups.com>.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy-alembic/FwVRUgcdVtg/unsubscribe.

To unsubscribe from this group and all its topics, send an email to sqlalchemy-alembic+unsub...@googlegroups.com.

[Mike Bayer]

On 07/11/2016 12:08 PM, Ofir Herzas wrote:
> It seems that I am wrong since I do see all column names in uppercase
> using sql developer (all but reserved words) so I don't know how this
> happened since my models are all lowercase (could it be
> Base.metadata.create_all?) but that is indeed the problem.
> Using 'SCOPE' in the alter method does solve the issue (I just hope it
> wouldn't introduce problems with mysql...)

Oracle displays all of its names in UPPERCASE anyway, which is the
opposite convention of the open source DBs like MySQL, Postgresql etc.,
so it's hard to see this. But in Oracle, the name "SCOPE" is not the
same as SCOPE, the former has quotes which mean it's a case sensitive name.

if you build a SQLAlchemy model and name the columns in all UPPERCASE,
which is something we see a lot with people accustomed to working with
Oracle, their tables/columns get created with all case sensitive names
that forever need the quotes. It's kind of a large gotcha, there's a
paragraph on it here:
http://docs.sqlalchemy.org/en/rel_1_0/dialects/oracle.html#identifier-casing
- which is accurate but as I skim it, it's not immediately clear what
it's talking about without more careful reading and could benefit from
examples and exposition to illustrate the concept. But even then it's
not really possible to get all or even a majority of Oracle SQLAlchemy
users to read everything on this page.

>
> Thanks.
>
> On Monday, July 11, 2016 at 6:40:09 PM UTC+3, Ofir Herzas wrote:
>
> For sure the table was not created using capital letters or quotes
> but I'll try your suggestion, thanks.
>
> On Jul 11, 2016 6:02 PM, "Mike Bayer" <mik...@zzzcomputing.com

> an email to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alembic%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alembic%2Bunsu...@googlegroups.com>>.

> For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
>
> --
> You received this message because you are subscribed to a topic
> in the Google Groups "sqlalchemy-alembic" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy-alembic/FwVRUgcdVtg/unsubscribe

> <https://groups.google.com/d/topic/sqlalchemy-alembic/FwVRUgcdVtg/unsubscribe>.

> To unsubscribe from this group and all its topics, send an email

> to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alembic%2Bunsu...@googlegroups.com>.

> For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.

[Ofir Herzas]

Every developer has his own convention for writing code.
For me, it's all lowercase and underscores in table columns so I can't imagine how I could have done it but nevertheless it is what it is...

Thank you for the detailed explanation.

To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy-alembic/FwVRUgcdVtg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy-alem...@googlegroups.com.