Incorrect denormalise name happening for all lowercase

[prempiyush]

We have a schema name in all lower case schema01 and a table in mixed case TableName01 in DB2

When we are trying to reflect the table using SQLAlchemy, the code says that the table does not exist.

I found that this method in ibm_db_sa is converting schema01 to SCHEMA01

    def denormalize_name(self, name):
        if name is None:
            return None
        elif name.lower() == name and \
                not self.identifier_preparer._requires_quotes(name.lower()):
            name = name.upper()
        if not self.dialect.supports_unicode_binds:
            if isinstance(name, str):
                name = name
            else:
                name = codecs.decode(name)
        else:
            if version_info[0] < 3:
                name = unicode(name)
            else:
                name = str(name)
        return name

Can you please help @bchoudhary6415 @bimalkjha

[prempiyush]

The has_table method in DB2Reflector is returning False because of this..

The actual query should be

SELECT "SYSCAT"."TABLES"."TABNAME"
FROM "SYSCAT"."TABLES"
WHERE "SYSCAT"."TABLES"."TABSCHEMA" = 'schema01' AND "SYSCAT"."TABLES"."TABNAME" = 'TableName01'

but because the schema01 is getting converted to SCHEMA01 this query becomes ..

SELECT "SYSCAT"."TABLES"."TABNAME"
FROM "SYSCAT"."TABLES"
WHERE "SYSCAT"."TABLES"."TABSCHEMA" = 'SCHEMA01' AND "SYSCAT"."TABLES"."TABNAME" = 'TableName01'

which does not return any results

[bchoudhary6415]

Hello @prempiyush
If possible, Can you please provide the sample repo for the above query?
Please try without giving quotations.
If a name is enclosed in quotation marks, the name is case sensitive.

Thank you

[prempiyush]

@bchoudhary6415 Yes, this happens when the schema/table is created using quotes.

These are customer tables that are created with scripts. We do not have a choice of not enclosing them in quotes.

I only have a free account and can not create DB2 schema there.. but this can be done using

CREEATE SCHEMA "schema01"

[bchoudhary6415]

@prempiyush
If you will create SCHEMA by giving quotations,
Then, you need to Retrieve data from SCHEMA by giving quotation only.

[prempiyush]

@bchoudhary6415 Agreed. In that case, the query then becomes..

SELECT "SYSCAT"."TABLES"."TABNAME"
FROM "SYSCAT"."TABLES"
WHERE "SYSCAT"."TABLES"."TABSCHEMA" = '"schema01"' AND "SYSCAT"."TABLES"."TABNAME" = 'TableName01'

which is again incorrect.. and does not give proper results.

[bchoudhary6415]

@prempiyush
What's error it giving?
Also, if possible can you please share sample repo.

[bchoudhary6415]

@prempiyush
Can you please give me update on this issue?

[prempiyush]

@bchoudhary6415 Apologies for the delay.

I do not have permissions to create new schema in my Cloud DB2 lite plan.. but the same thing happens for a table too..

I created a table testtable (all small case letters in double quotes) in my schema. I will send the DB2 credentials over email.

This is how I am trying to access it

import sqlalchemy

engine = sqlalchemy.create_engine("ibm_db_sa://grg68694:****@fbd88901-ebdb-4a4f-a32e-9822b9fb237b.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32731/bludb;SECURITY=SSL;")

metadata = sqlalchemy.MetaData(bind=engine, schema="grg68694")
table = sqlalchemy.Table(f"testtable", metadata, autoload=True)

The error I am getting is this..

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<string>", line 2, in __new__
  File "/Users/prempiyush/work/code/metric-plugins/.venv/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned
    return fn(*args, **kwargs)
  File "/Users/prempiyush/work/code/metric-plugins/.venv/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 618, in __new__
    with util.safe_reraise():
  File "/Users/prempiyush/work/code/metric-plugins/.venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/Users/prempiyush/work/code/metric-plugins/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/Users/prempiyush/work/code/metric-plugins/.venv/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 614, in __new__
    table._init(name, metadata, *args, **kw)
  File "/Users/prempiyush/work/code/metric-plugins/.venv/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 689, in _init
    self._autoload(
  File "/Users/prempiyush/work/code/metric-plugins/.venv/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 724, in _autoload
    conn_insp.reflect_table(
  File "/Users/prempiyush/work/code/metric-plugins/.venv/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 790, in reflect_table
    raise exc.NoSuchTableError(table_name)
sqlalchemy.exc.NoSuchTableError: testtable

The table does exist in the schema..

[prempiyush]

@bchoudhary6415 Can you please share your email address? I sent an email to [email protected] but it bounced.

[bchoudhary6415]

Hello @prempiyush
You can share me on [email protected]

[bchoudhary6415]

Hello @prempiyush
Can you please try using below way, by removing bind and autoload parameter?

import sqlalchemy

engine = sqlalchemy.create_engine("ibm_db_sa://grg68694:****@fbd88901-ebdb-4a4f-a32e-9822b9fb237b.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32731/bludb;SECURITY=SSL;")

metadata = sqlalchemy.MetaData(schema="grg68694")
table = sqlalchemy.Table(f"testtable", metadata)

[prempiyush]

@bchoudhary6415 Why are you recommending this? This will not load the table contents.

Can you please work towards fixing the bug instead of giving workarounds?

[bchoudhary6415]

@prempiyush
I'm looking into bug.
Can you please share which ibm_db_sa and SQLAlchemy version you are using?

[prempiyush]

@bchoudhary6415

ibm_db_sa==0.3.8
sqlalchemy==1.4.46

[bchoudhary6415]

@prempiyush
I'm not getting any undefined name or sqlalchemy.exc.NoSuchTableError: testtable error,
While retrieving data in below way:
with engine.connect() as conn:
result = conn.execute("SELECT * FROM grg68694."testtable"")
for row in result:
print(row)
You can refer below screenshot.

[bchoudhary6415]

[prempiyush]

@bchoudhary6415 Thank you but I don't want to fetch ALL the data in the table which is what result = conn.execute("SELECT * FROM grg68694."testtable"") will do

I am sure you are aware that by running this snippet

metadata = sqlalchemy.MetaData(bind=engine, schema="grg68694")
table = sqlalchemy.Table(f"testtable", metadata, autoload=True)

I can get the schema of the table without worrying about what my underlying database is.

Thank you but the workaround does not work in my case. I would appreciate if you can squash this bug please.

[prempiyush]

@bchoudhary6415 @bimalkjha Any plans for this?

[bchoudhary6415]

@prempiyush As some parameters are deprecated or modified in latest version of SQLAlchemy. I'm looking into it. As we are giving support for latest version of SQLAlchemy, so we will resolve this issue with latest support.

Thank you

[bchoudhary6415]

@prempiyush Can you please share "list tables" output for the schema grg68694?

[bchoudhary6415]

@prempiyush The fix for this issue is given. Please verify through the latest PR - #126.

Now you can use single quotes (" 'testtable' ") to refer lowercase table name in Db2 database as

metadata = sqlalchemy.MetaData(bind=engine , schema="grg68694")
table = sqlalchemy.Table("'testtable'", metadata, autoload=engine)

If table name is in capital later only, Don't need to give single quotes (" testtable ") and this will be referred as
'TESTTABLE'.

I'm closing this issue now.
Thank you