ask for help on best practices when changing table name from uppercase to lowercase

[Ke Zhu - kzhu@us.ibm.com]

The use case is migrating data from a Db2 instance (where schema is not
managed under Alembic migration) to a postgresql instance (where I plan
to manage schema via Alembic).

In Db2, by default, it stores unquoted mixed case identifiers in
Uppercase. While the postgres I got it stores unquoted mixed case
indetifiers in lowercase. So If I plan to keep such convention in
postgres (store unquoted mixed case identifiers like table name in
lowercase), what's the suggested practice to migrate an existing table
like "XXX_YYY_ZZZ" to "xxx_yyy_zzz"?

[Mike Bayer]

This is the casing convention of the database and SQLAlchemy does not consider the name to be in "uppercase" or "lowercase" (or even "mixed case") but rather "case insensitive", which really means the name is referred towards without any quoting.    When no quoting is applied, there is essentially no "casing" at all to the identifier that is stored by the database.

When you refer to the table name in SQLAlchemy, such as in a Table() object, or in Alembic as in op.create_table(), refer to it as all lower case at all times in the Python code- this indicates to SQLAlchemy that this is a case insensitive name and should not be quoted (as long as it has no special characters, spaces, etc).  SQLAlchemy will automatically render the name without quotes in DDL, DML and DQL, and render it as UPPERCASE when it queries DB2's catalogs, and as lower case when it queries PostgreSQL's catalogs.

-- 

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/5d4bf2d1f07e6958f94ee08d941db96c96fd91f0.camel%40us.ibm.com.

[kz...@us.ibm.com]

Thanks mike!

I understand that it should work when querying over SQLAlchemy API. the missing part of my use case I forgot to provide is, it only uses Alembic/SQLAlchemy to manage schema changes, while the actual query may comes from other query engine like a dashboard, a desktop DB client or a notebook where data scientists knows SQL only, the name cases would force user to remember such convention from DB design (unpleasant part when switching RDBMS).

A simple question is, if it already created tables via Alembic migration with uppercase table names, is there any API in Alembic simply support the statement like `ALTER TABLE XXX_YYY_ZZZ RENAME TO xxx_yyy_zzz`? Since I noticed that the revision autogenerate is not sensitive to table name. should I manually create a revision for postgres only?

[Mike Bayer]

On Tue, Jan 5, 2021, at 9:50 AM, kz...@us.ibm.com wrote:

Thanks mike!

I understand that it should work when querying over SQLAlchemy API. the missing part of my use case I forgot to provide is, it only uses Alembic/SQLAlchemy to manage schema changes, while the actual query may comes from other query engine like a dashboard, a desktop DB client or a notebook where data scientists knows SQL only, the name cases would force user to remember such convention from DB design (unpleasant part when switching RDBMS).

if these other folks are writing raw SQL then they just use identifiers without quotes, with any casing they want in their SQL string, and the names are case insensitive.  so there should be no need for them to remember any convention since case insensitive means there is no convention in the first place.   they can write all uppercase names in their PostgreSQL query if they'd like:

psql (12.4)

Type "help" for help.

test=> create table foo(colx integer, ColY integer);

CREATE TABLE

test=> SELECT COLX, COLY FROM FOO;

colx | coly

------+------

(0 rows)

if they are writing SQLAlchemy Python code, then they would use all lower case names at all times.

on the other hand, if the names were created *with quotes*, now the names are case sensitive, and users *must* use quotes, uppercase is not enough:

test=> create table "FOO" ("COLX" integer, "COLY" integer);

CREATE TABLE

test=> select colx from FOO;

ERROR:  relation "foo" does not exist

test=> select colx from "FOO";

ERROR:  column "colx" does not exist

test=> select "COLX" from "FOO";

COLX

------

(0 rows)

A simple question is, if it already created tables via Alembic migration with uppercase table names,

"uppercase table names" is not specific enough, were they quoted as UPPERCASE?   which database?

is there any API in Alembic simply support the statement like `ALTER TABLE XXX_YYY_ZZZ RENAME TO xxx_yyy_zzz`? Since I noticed that the revision autogenerate is not sensitive to table name. should I manually create a revision for postgres only?

if you have a case sensitive name in Alembic and want to migrate to case insensitive, on PostrgreSQL you likely could emit op.rename_table("XXX_YYY_ZZZ", "xxx_yyy_zzz") and it will just work, the first identifier will be quoted and the second one will not.  if you want to make it fully explicit, you can do this:

from sqlalchemy.sql import quoted_name

op.rename_table(quoted_name("XXX_YYY_ZZZ", quote=True), quoted_name("xxx_yyy_zzz", quote=False))

On Monday, January 4, 2021 at 6:57:33 PM UTC-5 mik...@zzzcomputing.com wrote:

This is the casing convention of the database and SQLAlchemy does not consider the name to be in "uppercase" or "lowercase" (or even "mixed case") but rather "case insensitive", which really means the name is referred towards without any quoting.    When no quoting is applied, there is essentially no "casing" at all to the identifier that is stored by the database.

When you refer to the table name in SQLAlchemy, such as in a Table() object, or in Alembic as in op.create_table(), refer to it as all lower case at all times in the Python code- this indicates to SQLAlchemy that this is a case insensitive name and should not be quoted (as long as it has no special characters, spaces, etc).  SQLAlchemy will automatically render the name without quotes in DDL, DML and DQL, and render it as UPPERCASE when it queries DB2's catalogs, and as lower case when it queries PostgreSQL's catalogs.

On Mon, Jan 4, 2021, at 4:12 PM, Ke Zhu - kz...@us.ibm.com wrote:

The use case is migrating data from a Db2 instance (where schema is not

managed under Alembic migration) to a postgresql instance (where I plan

to manage schema via Alembic).

In Db2, by default, it stores unquoted mixed case identifiers in

Uppercase. While the postgres I got it stores unquoted mixed case

indetifiers in lowercase. So If I plan to keep such convention in

postgres (store unquoted mixed case identifiers like table name in

lowercase), what's the suggested practice to migrate an existing table

like "XXX_YYY_ZZZ" to "xxx_yyy_zzz"?

-- 

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/5d4bf2d1f07e6958f94ee08d941db96c96fd91f0.camel%40us.ibm.com.

--

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/ca3b7c29-5309-4292-85bd-f64c6330da15n%40googlegroups.com.

[Ke Zhu - kzhu@us.ibm.com]

On Tue, 2021-01-05 at 13:09 -0500, Mike Bayer wrote:
>    On Tue, Jan 5, 2021, at 9:50 AM, kz...@us.ibm.com wrote:

> Thanks...                                                       
>                                                                  
>                                                            
> This Message Is From an External Sender
>    This message came from outside your organization.

Postgres.

I created the model like this:

employee_key = Column('EMPLOYEE_KEY', Integer, primary_key=True)

So based on the doc:
https://docs.sqlalchemy.org/en/14/core/metadata.html?highlight=column#sqlalchemy.schema.Column

A test shows it requires quoted column name in query via SQL directly.

>
>
> >
> >
> >
> >
> > is there any API in Alembic simply support the statement like
> > `ALTER TABLE XXX_YYY_ZZZ RENAME TO xxx_yyy_zzz`? Since I noticed
> > that the revision autogenerate is not sensitive to table name.
> > should I manually create a revision for postgres only?
>
> if you have a case sensitive name in Alembic and want to migrate to
> case insensitive, on PostrgreSQL you likely could emit
> op.rename_table("XXX_YYY_ZZZ", "xxx_yyy_zzz") and it will just work,
> the first identifier will be quoted and the second one will not.  if
> you want to make it fully explicit, you can do this:
>
> from sqlalchemy.sql import quoted_name
> op.rename_table(quoted_name("XXX_YYY_ZZZ", quote=True),
> quoted_name("xxx_yyy_zzz", quote=False))

Thank you! This is what I need!

I guess it can rename column like this:

op.alter_column('xxx_yyy_zzz', 'EMPLOYEE_KEY',
new_column_name='employee_key')

> > https://groups.google.com/d/msgid/sqlalchemy-alembic/ca3b7c29-5309-4292-85bd-f64c6330da15n%40googlegroups.com
> > .
>