[Bug]: Cannot create sqlalchemy Table from duckdb view

[zbs]

What happened?

This snippet works for (sqlalchemy=1.4.49, duckdb_engine=0.15.1,duckdb=1.0.0, python=3.8)

from sqlalchemy import create_engine
from sqlalchemy import text

from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy import select
from sqlalchemy import text

import pandas as pd

engine = create_engine("duckdb:///:memory:")
with engine.connect() as conn:
    conn.execute(text("CREATE VIEW my_view AS SELECT * FROM 'foo.parquet'"))
metadata = MetaData()
t = Table('my_view', metadata, autoload_with=engine)
print(pd.read_sql(select(t), engine))

but does not work in the most recent version:

Traceback (most recent call last):
  File "/physical/gpfs/carp2-home/car_home02/data_files/team/zsilversmith/dev/git/eis5/sparta/sparta/production/tradelogs/test_sqlalchemy.py", line 23, in <module>
    t = Table('my_view', metadata, autoload_with=engine)
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in __new__
  File "/team/public/conda/miniconda3-team.3/envs/conda3.12-team.35/lib/python3.12/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^
  File "/team/public/conda/miniconda3-team.3/envs/conda3.12-team.35/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 430, in __new__
    return cls._new(*args, **kw)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/team/public/conda/miniconda3-team.3/envs/conda3.12-team.35/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 484, in _new
    with util.safe_reraise():
         ^^^^^^^^^^^^^^^^^^^
  File "/team/public/conda/miniconda3-team.3/envs/conda3.12-team.35/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/team/public/conda/miniconda3-team.3/envs/conda3.12-team.35/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 480, in _new
    table.__init__(name, metadata, *args, _no_init=False, **kw)
  File "/team/public/conda/miniconda3-team.3/envs/conda3.12-team.35/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 862, in __init__
    self._autoload(
  File "/team/public/conda/miniconda3-team.3/envs/conda3.12-team.35/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 894, in _autoload
    conn_insp.reflect_table(
  File "/team/public/conda/miniconda3-team.3/envs/conda3.12-team.35/lib/python3.12/site-packages/sqlalchemy/engine/reflection.py", line 1548, in reflect_table
    raise exc.NoSuchTableError(table_name)
sqlalchemy.exc.NoSuchTableError: my_view

If I move the table definition inside the with block, it complains about nested transactions:

sqlalchemy.exc.OperationalError: (duckdb.duckdb.TransactionException) TransactionContext Error: cannot start a transaction within a transaction

DuckDB Engine Version

0.17.0

DuckDB Version

1.3.2

SQLAlchemy Version

2.0.39

Relevant log output

Code of Conduct

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

[zbs]

Tentative workaround is to a) not use a with block and b) autoload with the same connection (specifically not the engine):

engine = create_engine("duckdb:///:memory:")
conn = engine.connect() 
conn.execute(text("CREATE VIEW my_view AS SELECT * FROM 'foo.parquet'"))
metadata = MetaData()
t = Table('my_view', metadata, autoload_with=conn)

I'm guessing previously the with block did not automatically close the connection and now it clears out everything that was created.

[zbs]

Hi @Mause , any updates on this?