[Bug]: TypeError: unhashable type: '_duckdb.typing.DuckDBPyType with DuckDB 1.4.0

[rikjongerius]

What happened?

I'm not entirely sure if this is the right place, as it seems related to the release of DuckDB 1.4.0.

Creating all tables using SqlAlchemy create_all fails with an unhashable type, even if there are no tables specified at all:

Minimum example:

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base

engine = create_engine("duckdb:///:memory:")

Base = declarative_base()

Base.metadata.create_all(engine)

Throws exception TypeError: unhashable type: '_duckdb.typing.DuckDBPyType'

The code does not throw an exception when downgrading to duckdb 1.3.2.

Python 3.12

duckdb            1.4.0
duckdb-engine     0.17.0
greenlet          3.2.4
packaging         25.0
sqlalchemy        2.0.43
typing-extensions 4.15.0

DuckDB Engine Version

0.17.0

DuckDB Version

1.4.0

SQLAlchemy Version

2.0.43

Relevant log output

Traceback (most recent call last):
  File "/workspaces/python-33333/unhashable-type/main.py", line 8, in <module>
    Base.metadata.create_all(engine)
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 5924, in create_all
    bind._run_ddl_visitor(
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3251, in _run_ddl_visitor
    with self.begin() as conn:
         ^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3241, in begin
    with self.connect() as conn:
         ^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3277, in connect
    return self._connection_cls(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 143, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3301, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 445, in connect
    return _ConnectionFairy._checkout(self, self._fairy)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1264, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 711, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 427, in _do_get
    c = self._create_connection()
        ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 388, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 673, in __init__
    self.__connect()
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 913, in __connect
    )._exec_w_sync_on_first_run(self.dbapi_connection, self)
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/event/attr.py", line 483, in _exec_w_sync_on_first_run
    self(*args, **kw)
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/event/attr.py", line 497, in __call__
    fn(*args, **kw)
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 1997, in go
    return once_fn(*arg, **kw)
           ^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 767, in first_connect
    dialect.initialize(c)
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/duckdb_engine/__init__.py", line 523, in initialize
    DefaultDialect.initialize(self, connection)
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 533, in initialize
    self.default_schema_name = self._get_default_schema_name(
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3437, in _get_default_schema_name
    return connection.exec_driver_sql("select current_schema()").scalar()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1779, in exec_driver_sql
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2358, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    result = context._setup_result_proxy()
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 1853, in _setup_result_proxy
    result = self._setup_dml_or_text_result()
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 1967, in _setup_dml_or_text_result
    result: _cursor.CursorResult[Any] = _cursor.CursorResult(
                                        ^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/cursor.py", line 1447, in __init__
    metadata = self._init_metadata(context, cursor_description)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/cursor.py", line 1528, in _init_metadata
    self._metadata = metadata = CursorResultMetaData(
                                ^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/cursor.py", line 376, in __init__
    raw = self._merge_cursor_description(
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/cursor.py", line 633, in _merge_cursor_description
    context.get_result_processor(
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 1804, in get_result_processor
    return type_._cached_result_processor(self.dialect, coltype)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/python-33333/unhashable-type/.venv/lib/python3.12/site-packages/sqlalchemy/sql/type_api.py", line 951, in _cached_result_processor
    d["result"][coltype] = rp
    ~~~~~~~~~~~^^^^^^^^^
TypeError: unhashable type: '_duckdb.typing.DuckDBPyType

Code of Conduct

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

[dhirschfeld]

NB: There's a copy paste MCVE in the linked DuckDB repo, in case it's useful.

• [1.4.0] TypeError: unhashable type: _duckdb.typing.DuckDBPyType duckdb/duckdb-python#78

[alexander-beedie]

For bonus context most (all?) SQLAlchemy queries now seem to fail in the same way, eg:

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base

conn = create_engine("duckdb:///:memory:").connect()
conn.execute("select 123").scalar()

# TypeError: unhashable type: '_duckdb.typing.DuckDBPyType'

[dhirschfeld]

This has been resolved by:

• [DuckDBPyType] Add __hash__ method overload duckdb/duckdb-python#61

...which will be in the 1.4.1 duckdb release.