RE: ask for help on best practices when changing table name from uppercase to lowercase
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
Re: ask for help on best practices when changing table name from uppercase to lowercase
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 >
Re: ask for help on best practices when changing table name from uppercase to lowercase
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? 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.
Re: ask for help on best practices when changing table name from uppercase to lowercase
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 - k...@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-alembic+unsubscr...@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/99214f97-7066-4204-a3c4-bf0438621ec8%40www.fastmail.com.
ask for help on best practices when changing table name from uppercase to lowercase
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-alembic+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/5d4bf2d1f07e6958f94ee08d941db96c96fd91f0.camel%40us.ibm.com.