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.
>
>
>
> 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?
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')
>
>
>
> >
> > 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
> > https://groups.google.com/d/msgid/sqlalchemy-alembic/ca3b7c29-5309-4292-85bd-f64c6330da15n%40googlegroups.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
https://groups.google.com/d/msgid/sqlalchemy-alembic/1f44ccdd24492f27c3d48103b753254fc23e134f.camel%40us.ibm.com.
smime.p7s
Description: S/MIME cryptographic signature
