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-alembic+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
>
