%sqlcmd profile does not work with postgres "quoted" fields and throw: column does not exist error

[Hubbitus]

What happens?

Postgres has so called quoted identifier where case of table name and fields became significant.

%sqlcmd profile throw "column X does not exist error" on such tables.

To Reproduce

from sqlalchemy import create_engine
from ipython_secrets import *

try:
    db_url = f'postgresql://data:{get_secret("DB_PASSWORD_PG")}@10.1.1.1:10266/apidev'
    engine = create_engine(db_url)

    %sql engine --alias PG
finally:
    db_url = None

%sqlcmd profile --table "group"

---------------------------------------------------------------------------
UndefinedColumn                           Traceback (most recent call last)
File /opt/conda/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1965, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1964     if not evt_handled:
-> 1965         self.dialect.do_execute(
   1966             cursor, str_statement, effective_parameters, context
   1967         )
   1969 if self._has_events or self.engine._has_events:

File /opt/conda/lib/python3.11/site-packages/sqlalchemy/engine/default.py:921, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    920 def do_execute(self, cursor, statement, parameters, context=None):
--> 921     cursor.execute(statement, parameters)

UndefinedColumn: column "createdat" does not exist
LINE 1: SELECT createdAt FROM "group" LIMIT 1
               ^
HINT:  Perhaps you meant to reference the column "group.createdAt".


The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
Cell In[14], line 1
----> 1 get_ipython().run_line_magic('sqlcmd', 'profile --table "group"')
      3 # UndefinedColumn: column "createdat" does not exist
      4 # LINE 1: SELECT createdAt FROM "group" LIMIT 1
      5 # TODO fill BUG

File /opt/conda/lib/python3.11/site-packages/IPython/core/interactiveshell.py:2417, in InteractiveShell.run_line_magic(self, magic_name, line, _stack_depth)
   2415     kwargs['local_ns'] = self.get_local_scope(stack_depth)
   2416 with self.builtin_trap:
-> 2417     result = fn(*args, **kwargs)
   2419 # The code below prevents the output from being displayed
   2420 # when using magics with decodator @output_can_be_silenced
   2421 # when the last Python token in the expression is a ';'.
   2422 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File /opt/conda/lib/python3.11/site-packages/sql/magic_cmd.py:105, in SqlCmdMagic._validate_execute_inputs(self, line)
    102     if command in COMMANDS_SQLALCHEMY_ONLY:
    103         support_only_sql_alchemy_connection(f"%sqlcmd {command}")
--> 105     return self.execute(command, others)
    106 else:
    107     raise exceptions.UsageError(
    108         f"%sqlcmd has no command: {command!r}. "
    109         "Valid commands are: {}".format(
    110             ", ".join(AVAILABLE_SQLCMD_COMMANDS)
    111         )
    112     )

File /opt/conda/lib/python3.11/site-packages/sql/magic_cmd.py:135, in SqlCmdMagic.execute(self, cmd_name, others, cell, local_ns)
    133     return cmd(others)
    134 else:
--> 135     return cmd(others, self.shell.user_ns.copy())

File /opt/conda/lib/python3.11/site-packages/sql/cmd/profile.py:42, in profile(others, user_ns)
     39 if is_rendering_required(" ".join(others)):
     40     expand_args(args, user_ns)
---> 42 report = inspect.get_table_statistics(schema=args.schema, name=args.table)
     44 if args.output:
     45     with open(args.output, "w") as f:

File /opt/conda/lib/python3.11/site-packages/ploomber_core/telemetry/telemetry.py:700, in Telemetry.log_call.<locals>._log_call.<locals>.wrapper(*args, **kwargs)
    698             result = func(_payload, *args, **kwargs)
    699     else:
--> 700         result = func(*args, **kwargs)
    701 except Exception as e:
    702     metadata_error = {
    703         # can we log None to posthog?
    704         "type": getattr(e, "type_", None),
   (...)
    707         **_payload,
    708     }

File /opt/conda/lib/python3.11/site-packages/sql/inspect.py:500, in get_table_statistics(name, schema)
    492 @telemetry.log_call()
    493 def get_table_statistics(name, schema=None):
    494     """Get table statistics for a given connection.
    495 
    496     For all data types the results will include `count`, `mean`, `std`, `min`
    497     `max`, `25`, `50` and `75` percentiles. It will also include `unique`, `top`
    498     and `freq` statistics.
    499     """
--> 500     return TableDescription(name, schema=schema)

File /opt/conda/lib/python3.11/site-packages/ploomber_core/exceptions.py:128, in modify_exceptions.<locals>.wrapper(*args, **kwargs)
    125 @wraps(fn)
    126 def wrapper(*args, **kwargs):
    127     try:
--> 128         return fn(*args, **kwargs)
    129     except (ValueError, TypeError) as e:
    130         _add_community_link(e)

File /opt/conda/lib/python3.11/site-packages/sql/inspect.py:259, in TableDescription.__init__(self, table_name, schema)
    257 # check the datatype of a column
    258 try:
--> 259     result = ConnectionManager.current.raw_execute(
    260         f"""SELECT {column} FROM {table_name} LIMIT 1"""
    261     ).fetchone()
    263     value = result[0]
    264     is_numeric = isinstance(value, (int, float)) or (
    265         isinstance(value, str) and _is_numeric(value)
    266     )

File /opt/conda/lib/python3.11/site-packages/sql/connection/connection.py:824, in SQLAlchemyConnection.raw_execute(self, query, parameters, with_)
    822 else:
    823     try:
--> 824         return self._connection_execute(query, parameters)
    825     except StatementError as e:
    826         # add a more helpful message if the users passes :variable but
    827         # the feature isn't enabled
    828         if parameters is None:

File /opt/conda/lib/python3.11/site-packages/sql/connection/connection.py:731, in SQLAlchemyConnection._connection_execute(self, query, parameters)
    728     raise NotImplementedError("Only one statement is supported.")
    730 operation = partial(self._execute_with_parameters, query, parameters)
--> 731 out = self._execute_with_error_handling(operation)
    733 if self._requires_manual_commit:
    734     # Calling connection.commit() when using duckdb-engine will yield
    735     # empty results if we commit after a SELECT or SUMMARIZE statement,
    736     # see: https://github.com/Mause/duckdb_engine/issues/734.
    737     if self.dialect == "duckdb":

File /opt/conda/lib/python3.11/site-packages/sql/connection/connection.py:862, in SQLAlchemyConnection._execute_with_error_handling(self, operation)
    859 rollback_needed = False
    861 try:
--> 862     out = operation()
    864 # this is a generic error but we've seen it in postgres. it helps recover
    865 # from a idle session timeout (happens in psycopg 2 and psycopg 3)
    866 except PendingRollbackError:

File /opt/conda/lib/python3.11/site-packages/sql/connection/connection.py:767, in SQLAlchemyConnection._execute_with_parameters(self, query, parameters)
    765     out = self._connection.execute(sqlalchemy.text(query), **parameters)
    766 else:
--> 767     out = self._connection.execute(
    768         sqlalchemy.text(query), parameters=parameters
    769     )
    771 return out

File /opt/conda/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1412, in Connection.execute(self, statement, parameters, execution_options)
   1410     raise exc.ObjectNotExecutableError(statement) from err
   1411 else:
-> 1412     return meth(
   1413         self,
   1414         distilled_parameters,
   1415         execution_options or NO_OPTIONS,
   1416     )

File /opt/conda/lib/python3.11/site-packages/sqlalchemy/sql/elements.py:483, in ClauseElement._execute_on_connection(self, connection, distilled_params, execution_options)
    481     if TYPE_CHECKING:
    482         assert isinstance(self, Executable)
--> 483     return connection._execute_clauseelement(
    484         self, distilled_params, execution_options
    485     )
    486 else:
    487     raise exc.ObjectNotExecutableError(self)

File /opt/conda/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1635, in Connection._execute_clauseelement(self, elem, distilled_parameters, execution_options)
   1623 compiled_cache: Optional[CompiledCacheType] = execution_options.get(
   1624     "compiled_cache", self.engine._compiled_cache
   1625 )
   1627 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1628     dialect=dialect,
   1629     compiled_cache=compiled_cache,
   (...)
   1633     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1634 )
-> 1635 ret = self._execute_context(
   1636     dialect,
   1637     dialect.execution_ctx_cls._init_compiled,
   1638     compiled_sql,
   1639     distilled_parameters,
   1640     execution_options,
   1641     compiled_sql,
   1642     distilled_parameters,
   1643     elem,
   1644     extracted_params,
   1645     cache_hit=cache_hit,
   1646 )
   1647 if has_events:
   1648     self.dispatch.after_execute(
   1649         self,
   1650         elem,
   (...)
   1654         ret,
   1655     )

File /opt/conda/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1844, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1839     return self._exec_insertmany_context(
   1840         dialect,
   1841         context,
   1842     )
   1843 else:
-> 1844     return self._exec_single_context(
   1845         dialect, context, statement, parameters
   1846     )

File /opt/conda/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1984, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1981     result = context._setup_result_proxy()
   1983 except BaseException as e:
-> 1984     self._handle_dbapi_exception(
   1985         e, str_statement, effective_parameters, cursor, context
   1986     )
   1988 return result

File /opt/conda/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2339, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context, is_sub_exec)
   2337 elif should_wrap:
   2338     assert sqlalchemy_exception is not None
-> 2339     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
   2340 else:
   2341     assert exc_info[1] is not None

File /opt/conda/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1965, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1963                 break
   1964     if not evt_handled:
-> 1965         self.dialect.do_execute(
   1966             cursor, str_statement, effective_parameters, context
   1967         )
   1969 if self._has_events or self.engine._has_events:
   1970     self.dispatch.after_cursor_execute(
   1971         self,
   1972         cursor,
   (...)
   1976         context.executemany,
   1977     )

File /opt/conda/lib/python3.11/site-packages/sqlalchemy/engine/default.py:921, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    920 def do_execute(self, cursor, statement, parameters, context=None):
--> 921     cursor.execute(statement, parameters)

ProgrammingError: (psycopg2.errors.UndefinedColumn) column "createdat" does not exist
LINE 1: SELECT createdAt FROM "group" LIMIT 1
               ^
HINT:  Perhaps you meant to reference the column "group.createdAt".

[SQL: SELECT createdAt FROM "group" LIMIT 1]
(Background on this error at: https://sqlalche.me/e/20/f405)

OS:

Linux

JupySQL Version:

0.10.12

Full Name:

Pavel Alexeev

Affiliation:

GID