[Bug]: MetaData.reflect() raises exception with sqlalchemy v2.0.36

[daniel-thom]

What happened?

This commit in sqlalchemy v2.0.36 causes a regression when creating a table in DuckDB. A SQLAlchemy developer says that this package will need to override the new statement.

from sqlalchemy import MetaData, create_engine, text

engine = create_engine("duckdb:///:memory:")
metadata = MetaData()
with engine.connect() as conn:
    conn.execute(text("CREATE TABLE tbl(col1 INTEGER)"))
    conn.commit()
metadata.reflect(engine)

/private/tmp/env/lib/python3.11/site-packages/duckdb_engine/__init__.py:174: DuckDBEngineWarning: duckdb-engine doesn't yet support reflection on indices
  warnings.warn(
Traceback (most recent call last):
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
  File "/private/tmp/env/lib/python3.11/site-packages/duckdb_engine/__init__.py", line 140, in execute
    self.__c.execute(statement, parameters)
duckdb.duckdb.CatalogException: Catalog Error: Type with name REGCLASS does not exist!

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

Traceback (most recent call last):
  File "/Users/dthom/repos/chronify/scripts/repro_alchemy.py", line 8, in <module>
    metadata.reflect(engine)
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 5828, in reflect
    _reflect_info = insp._get_reflection_info(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 2024, in _get_reflection_info
    table_comment=run(self.get_multi_table_comment, optional=True),
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 2001, in run
    res = meth(filter_names=_fn, **kw)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 1377, in get_multi_table_comment
    self.dialect.get_multi_table_comment(
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/base.py", line 4709, in get_multi_table_comment
    result = connection.execute(query, params)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
  File "/private/tmp/env/lib/python3.11/site-packages/duckdb_engine/__init__.py", line 140, in execute
    self.__c.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (duckdb.duckdb.CatalogException) Catalog Error: Type with name REGCLASS does not exist!
[SQL: SELECT pg_catalog.pg_class.relname, pg_catalog.pg_description.description 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_class.oid = pg_catalog.pg_description.objoid AND pg_catalog.pg_description.objsubid = $1 AND pg_catalog.pg_description.classoid = CAST($2 AS REGCLASS) JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[$3, $4, $5]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != $6 AND pg_catalog.pg_class.relname IN ($7)]
[parameters: (0, 'pg_catalog.pg_class', 'r', 'p', 'f', 'pg_catalog', 'tbl')]
(Background on this error at: https://sqlalche.me/e/20/f405)

DuckDB Engine Version

0.13.2

DuckDB Version

1.1.2

SQLAlchemy Version

2.0.36

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[Aarya2004]

Hello! I've taken a look at both this codebase and the sqlalchemy codebase and I think I have an idea for how to fix the problem. My group and I are students at the University of Toronto so we'd love to take a crack at this issue!

[Aarya2004]

Hello @Mause! We were able to make a fix for this but we'd like someone to review the code, is it possible that I could be made the assignee to create the PR?

[NickCrews]

@Aarya2004 you should be able to create the PR without needing to be an assignee. You will need to make a fork of this repo, push your commits to a branch there, and then submit a PR from that branch to here. If you need help with that let me know

[Aarya2004]

Hey @NickCrews, thank you so much! We've created the PR but we'd appreciate a code review if possible. Thanks for the tip!