[1.4.0] TypeError: unhashable type: `_duckdb.typing.DuckDBPyType`

[dhirschfeld]

What happens?

Since the release of 1.4.0 I'm observing the below error in CI, which appears to be linked to the usage of duckdb_engine.

I wasn't sure which repo to open the issue in, but opened it here as the catalyst for observing this bug was the release of 1.4.0:

Tagging @Mause, just for visibility.

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

>>> Base.metadata.create_all(db_creds.engine)

/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:5924: in create_all
    bind._run_ddl_visitor(
        bind       = Engine(duckdb:///:memory:)
        checkfirst = True
        self       = MetaData()
        tables     = None
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3251: in _run_ddl_visitor
    with self.begin() as conn:
        element    = MetaData()
        kwargs     = {'checkfirst': True, 'tables': None}
        self       = Engine(duckdb:///:memory:)
        visitorcallable = <class 'sqlalchemy.sql.ddl.SchemaGenerator'>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/contextlib.py:135: in __enter__
    return next(self.gen)
        self       = <contextlib._GeneratorContextManager object at 0x7f1b3a5cbc10>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3241: in begin
    with self.connect() as conn:
        self       = Engine(duckdb:///:memory:)
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3277: in connect
    return self._connection_cls(self)
        self       = Engine(duckdb:///:memory:)
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py:143: in __init__
    self._dbapi_connection = engine.raw_connection()
        _allow_autobegin = True
        _allow_revalidate = True
        _has_events = None
        connection = None
        dialect    = <duckdb_engine.Dialect object at 0x7f1b3a5ca7a0>
        engine     = Engine(duckdb:///:memory:)
        self       = <sqlalchemy.engine.base.Connection object at 0x7f1b3a5ca470>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3301: in raw_connection
    return self.pool.connect()
        self       = Engine(duckdb:///:memory:)
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/pool/impl.py:445: in connect
    return _ConnectionFairy._checkout(self, self._fairy)
        self       = <sqlalchemy.pool.impl.SingletonThreadPool object at 0x7f1b3b61fb80>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/pool/base.py:1264: in _checkout
    fairy = _ConnectionRecord.checkout(pool)
        cls        = <class 'sqlalchemy.pool.base._ConnectionFairy'>
        fairy      = None
        pool       = <sqlalchemy.pool.impl.SingletonThreadPool object at 0x7f1b3b61fb80>
        threadconns = <_thread._local object at 0x7f1b3a44aa70>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/pool/base.py:711: in checkout
    rec = pool._do_get()
        cls        = <class 'sqlalchemy.pool.base._ConnectionRecord'>
        pool       = <sqlalchemy.pool.impl.SingletonThreadPool object at 0x7f1b3b61fb80>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/pool/impl.py:427: in _do_get
    c = self._create_connection()
        self       = <sqlalchemy.pool.impl.SingletonThreadPool object at 0x7f1b3b61fb80>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/pool/base.py:388: in _create_connection
    return _ConnectionRecord(self)
        self       = <sqlalchemy.pool.impl.SingletonThreadPool object at 0x7f1b3b61fb80>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/pool/base.py:673: in __init__
    self.__connect()
        connect    = True
        pool       = <sqlalchemy.pool.impl.SingletonThreadPool object at 0x7f1b3b61fb80>
        self       = <sqlalchemy.pool.base._ConnectionRecord object at 0x7f1b3a49d780>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/pool/base.py:913: in __connect
    )._exec_w_sync_on_first_run(self.dbapi_connection, self)
        connection = <duckdb_engine.ConnectionWrapper object at 0x7f1b4b793cd0>
        pool       = <sqlalchemy.pool.impl.SingletonThreadPool object at 0x7f1b3b61fb80>
        self       = <sqlalchemy.pool.base._ConnectionRecord object at 0x7f1b3a49d780>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/event/attr.py:483: in _exec_w_sync_on_first_run
    self(*args, **kw)
        args       = (<duckdb_engine.ConnectionWrapper object at 0x7f1b4b793cd0>, <sqlalchemy.pool.base._ConnectionRecord object at 0x7f1b3a49d780>)
        kw         = {}
        self       = <sqlalchemy.event.attr._ListenerCollection object at 0x7f1b3a426340>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/event/attr.py:497: in __call__
    fn(*args, **kw)
        args       = (<duckdb_engine.ConnectionWrapper object at 0x7f1b4b793cd0>, <sqlalchemy.pool.base._ConnectionRecord object at 0x7f1b3a49d780>)
        fn         = <function only_once.<locals>.go at 0x7f1b3a4aa170>
        kw         = {}
        self       = <sqlalchemy.event.attr._ListenerCollection object at 0x7f1b3a426340>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:1997: in go
    return once_fn(*arg, **kw)
        arg        = (<duckdb_engine.ConnectionWrapper object at 0x7f1b4b793cd0>, <sqlalchemy.pool.base._ConnectionRecord object at 0x7f1b3a49d780>)
        fn         = <function create_engine.<locals>.first_connect at 0x7f1b3a4aa050>
        kw         = {}
        once       = [<function create_engine.<locals>.first_connect at 0x7f1b3a4aa050>]
        once_fn    = <function create_engine.<locals>.first_connect at 0x7f1b3a4aa050>
        retry_on_exception = True
        strong_fn  = <function create_engine.<locals>.first_connect at 0x7f1b3a4aa050>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/create.py:767: in first_connect
    dialect.initialize(c)
        c          = <sqlalchemy.engine.base.Connection object at 0x7f1b4b793d60>
        connection_record = <sqlalchemy.pool.base._ConnectionRecord object at 0x7f1b3a49d780>
        dbapi_connection = <duckdb_engine.ConnectionWrapper object at 0x7f1b4b793cd0>
        dialect    = <duckdb_engine.Dialect object at 0x7f1b3a5ca7a0>
        engine     = Engine(duckdb:///:memory:)
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/duckdb_engine/__init__.py:523: in initialize
    DefaultDialect.initialize(self, connection)
        connection = <sqlalchemy.engine.base.Connection object at 0x7f1b4b793d60>
        self       = <duckdb_engine.Dialect object at 0x7f1b3a5ca7a0>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/default.py:533: in initialize
    self.default_schema_name = self._get_default_schema_name(
        connection = <sqlalchemy.engine.base.Connection object at 0x7f1b4b793d60>
        self       = <duckdb_engine.Dialect object at 0x7f1b3a5ca7a0>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/base.py:3437: in _get_default_schema_name
    return connection.exec_driver_sql("select current_schema()").scalar()
        connection = <sqlalchemy.engine.base.Connection object at 0x7f1b4b793d60>
        self       = <duckdb_engine.Dialect object at 0x7f1b3a5ca7a0>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1779: in exec_driver_sql
    ret = self._execute_context(
        dialect    = <duckdb_engine.Dialect object at 0x7f1b3a5ca7a0>
        distilled_parameters = ()
        execution_options = immutabledict({})
        parameters = None
        self       = <sqlalchemy.engine.base.Connection object at 0x7f1b4b793d60>
        statement  = 'select current_schema()'
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1846: in _execute_context
    return self._exec_single_context(
        args       = ('select current_schema()', ())
        conn       = <sqlalchemy.pool.base._AdhocProxiedConnection object at 0x7f1b3b288140>
        constructor = <bound method DefaultExecutionContext._init_statement of <class 'sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2'>>
        context    = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        dialect    = <duckdb_engine.Dialect object at 0x7f1b3a5ca7a0>
        execution_options = immutabledict({})
        kw         = {}
        parameters = None
        self       = <sqlalchemy.engine.base.Connection object at 0x7f1b4b793d60>
        statement  = 'select current_schema()'
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1986: in _exec_single_context
    self._handle_dbapi_exception(
        context    = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        cursor     = <duckdb_engine.CursorWrapper object at 0x7f1b4b7935e0>
        dialect    = <duckdb_engine.Dialect object at 0x7f1b3a5ca7a0>
        effective_parameters = ()
        evt_handled = False
        parameters = [()]
        self       = <sqlalchemy.engine.base.Connection object at 0x7f1b4b793d60>
        statement  = 'select current_schema()'
        str_statement = 'select current_schema()'
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py:2358: in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
        context    = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        cursor     = <duckdb_engine.CursorWrapper object at 0x7f1b4b7935e0>
        e          = TypeError("unhashable type: '_duckdb.typing.DuckDBPyType'")
        exc_info   = (<class 'TypeError'>, TypeError("unhashable type: '_duckdb.typing.DuckDBPyType'"), <traceback object at 0x7f1b3a420dc0>)
        invalidate_pool_on_disconnect = True
        is_exit_exception = False
        is_sub_exec = False
        ismulti    = False
        newraise   = None
        parameters = ()
        self       = <sqlalchemy.engine.base.Connection object at 0x7f1b4b793d60>
        should_wrap = False
        sqlalchemy_exception = None
        statement  = 'select current_schema()'
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1983: in _exec_single_context
    result = context._setup_result_proxy()
        context    = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        cursor     = <duckdb_engine.CursorWrapper object at 0x7f1b4b7935e0>
        dialect    = <duckdb_engine.Dialect object at 0x7f1b3a5ca7a0>
        effective_parameters = ()
        evt_handled = False
        parameters = [()]
        self       = <sqlalchemy.engine.base.Connection object at 0x7f1b4b793d60>
        statement  = 'select current_schema()'
        str_statement = 'select current_schema()'
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/default.py:1853: in _setup_result_proxy
    result = self._setup_dml_or_text_result()
        exec_opt   = immutabledict({})
        self       = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/default.py:1967: in _setup_dml_or_text_result
    result: _cursor.CursorResult[Any] = _cursor.CursorResult(
        compiled   = None
        cursor_description = [('current_schema()', VARCHAR, None, None, None, None, ...)]
        self       = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        strategy   = <sqlalchemy.engine.cursor.CursorFetchStrategy object at 0x7f1b73ca8e60>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py:1447: in __init__
    metadata = self._init_metadata(context, cursor_description)
        context    = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        cursor_description = [('current_schema()', VARCHAR, None, None, None, None, ...)]
        cursor_strategy = <sqlalchemy.engine.cursor.CursorFetchStrategy object at 0x7f1b73ca8e60>
        echo       = False
        self       = <sqlalchemy.engine.cursor.CursorResult object at 0x7f1b3b1db1c0>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py:1528: in _init_metadata
    self._metadata = metadata = CursorResultMetaData(
        context    = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        cursor_description = [('current_schema()', VARCHAR, None, None, None, None, ...)]
        self       = <sqlalchemy.engine.cursor.CursorResult object at 0x7f1b3b1db1c0>
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py:376: in __init__
    raw = self._merge_cursor_description(
        ad_hoc_textual = False
        cols_are_ordered = False
        context    = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        cursor_description = [('current_schema()', VARCHAR, None, None, None, None, ...)]
        loose_column_name_matching = False
        num_ctx_cols = False
        parent     = <sqlalchemy.engine.cursor.CursorResult object at 0x7f1b3b1db1c0>
        result_columns = False
        self       = <sqlalchemy.engine.cursor.CursorResultMetaData object at 0x7f1b3a412c70>
        textual_ordered = False
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py:626: in _merge_cursor_description
    return [
        ad_hoc_textual = False
        cols_are_ordered = False
        context    = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        cursor_description = [('current_schema()', VARCHAR, None, None, None, None, ...)]
        loose_column_name_matching = False
        num_ctx_cols = False
        raw_iterator = <generator object CursorResultMetaData._merge_cols_by_none at 0x7f1b3a412c00>
        result_columns = False
        self       = <sqlalchemy.engine.cursor.CursorResultMetaData object at 0x7f1b3a412c70>
        textual_ordered = False
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py:633: in <listcomp>
    context.get_result_processor(
        .0         = <generator object CursorResultMetaData._merge_cols_by_none at 0x7f1b3a412c00>
        coltype    = VARCHAR
        context    = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        cursor_colname = 'current_schema()'
        idx        = 0
        mapped_type = NullType()
        obj        = None
        ridx       = None
        untranslated = None
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/engine/default.py:1804: in get_result_processor
    return type_._cached_result_processor(self.dialect, coltype)
        colname    = 'current_schema()'
        coltype    = VARCHAR
        self       = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f1b4b7908b0>
        type_      = NullType()
/opt/hostedtoolcache/Python/3.10.18/x64/lib/python3.10/site-packages/sqlalchemy/sql/type_api.py:951: in _cached_result_processor
    d["result"][coltype] = rp
E   TypeError: unhashable type: '_duckdb.typing.DuckDBPyType'
        coltype    = VARCHAR
        d          = {'impl': DuckDBNullType(), 'result': {}}
        dialect    = <duckdb_engine.Dialect object at 0x7f1b3a5ca7a0>
        rp         = None
        self       = NullType()

To Reproduce

The below MCVE code will reproduce the issue I'm observing in CI:

import sqlalchemy as sa

engine = sa.create_engine("duckdb:///:memory:")
with engine.connect() as conn:
    res = conn.execute(sa.text("select 1")).scalar_one()

OS:

Ubuntu Linux - x86_64

DuckDB Version:

1.4.0

DuckDB Client:

Python

Hardware:

Full Name:

David Hirschfeld

Affiliation:

Contractor / Software Engineer

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[Tishj]

Thanks, as stated in the v1.4.0 release blog, duckdb-python has moved to its own repository
@szarnyasg can you move this please?

[Tishj]

If I'd have to guess, it's related to #43
The previous implementation of description was so unhelpful/uninformative that I really wouldn't expect anyone to be using it, guess I was wrong

If they really need to hash it we can add a dunder method I guess, or they can stringify it themselves

[rikjongerius]

Just for completeness, I also created the same bugreport in the duckdb-engine repo: Mause/duckdb_engine#1338

[alexander-beedie]

I've raised an equivalent issue in #68 for visibility, as this has caused issues for us too (we've had to block 1.4 from installing). It seems that SQLAlchemy support/integration is completely broken 😅

[evertlammerts]

I just merged @Tishj 's fix and did a quick sanity check:

Python 3.11.13 (main, Jul  8 2025, 20:42:09) [Clang 20.1.4 ] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy as sa
>>> engine = sa.create_engine("duckdb:///:memory:")
>>> with engine.connect() as conn:
...     res = conn.execute(sa.text("select 1")).scalar_one()
...
>>> print(res)
1

You have two options for now:

• Starting tomorrow the 1.4.1.devX nightlies will contain the fix (note: assuming the nightlies publish successfully - they don't always)
• The next stable release 1.4.1 will also contain the fix. There's no date set for 1.4.1 yet but keep an eye on the release calendar - we release relatively often. Until then you can pin DuckDB on < 1.4.0 in your requirements.

[ruiyang2015]

trying the new 1.4.1.dev113, it seems fixed this issue, but introduce new issue, all install_extentions are failing.
is it possible to publish the extensions for the nightly dev build?

[evertlammerts]

@ruiyang2015 That's unrelated. See my reply on your ticket.