Object Name Handling

When passing an object name, e.g. the name of a database table, into a SQLAlchemy construct, the name usage is case-insensitive. You could pass table_name.lower() or table_name.upper() as the first argument to Table() in the following example, and both options will work.

examples/features/specific_focuses/_2_object_name.py

from sqlalchemy import (
    CHAR,
    Column,
    Integer,
    MetaData,
    Table,
    text,
)

from examples.config import (
    DEFAULT_SCHEMA_NAME,
    ENGINE,
    SQL_ALCHEMY,
)

# 0. Ensure that the schema exists
SQL_ALCHEMY.create_schema(engine=ENGINE, schema=DEFAULT_SCHEMA_NAME)
metadata = MetaData(schema=DEFAULT_SCHEMA_NAME)

# 1. Create the table
table_name = "hex_lookup"

hex_table = Table(
    # table_name_lower.upper() also would work
    table_name.lower(),
    metadata,
    Column("id", Integer, primary_key=True, autoincrement=True),
    Column("hex_code", CHAR(6), nullable=False),
)

with ENGINE.begin() as conn:
    metadata.create_all(conn)

When using a fully prepared raw SQL statement or using a where clause, you must ensure to send the text as it was saved in the Exasol database instance. In the case of table & schema names, these are always saved in uppercase.

examples/features/specific_focuses/_2_object_name.py

# 2. Select with fully prepared raw SQL statement

# Here only table_name_lower.upper(), etc. would work,
# as the internal values are saved in uppercase.
query = f"""
SELECT *
FROM SYS.EXA_ALL_TABLES
WHERE TABLE_SCHEMA = '{DEFAULT_SCHEMA_NAME.upper()}'
AND TABLE_NAME = '{table_name.upper()}'
"""

with ENGINE.connect() as con:
    results = con.execute(text(query)).fetchall()

print(results)