[Bug]: provided example does not work throwing ProgrammingError: (duckdb.CatalogException) Catalog Error

[coffepowered]

What happened?

Hello,
I am trying to use duckdb_engine (thank you for coding this!), but it seems impossible to even run the provided example, see logs. I also tried to run simpler ones without success.
Running on python 3.8 under Linux (via docker on WSL)

from sqlalchemy import Column, Integer, Sequence, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import Session

Base = declarative_base()


class FakeModel(Base):  # type: ignore
    __tablename__ = "fake"

    id = Column(Integer, Sequence("fakemodel_id_sequence"), primary_key=True)
    name = Column(String)


eng = create_engine("duckdb:///:memory:")
Base.metadata.create_all(eng)
session = Session(bind=eng)

session.add(FakeModel(name="Frank"))
session.commit()

frank = session.query(FakeModel).one()

assert frank.name == "Frank"

DuckDB Engine Version

0.7.0

DuckDB Version

0.7.1

Relevant log output

python src/example.py 
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 366, in connect
    return _ConnectionFairy._checkout(self, self._fairy)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 349, in _do_get
    c = self._create_connection()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 664, in __connect
    pool.dispatch.first_connect.for_modify(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 314, in exec_once_unless_exception
    self._exec_once_impl(True, *args, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 285, in _exec_once_impl
    self(*args, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 322, in __call__
    fn(*args, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 1397, in go
    return once_fn(*arg, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 199, in first_connect
    dialect.initialize(c)
  File "/usr/local/lib/python3.8/site-packages/duckdb_engine/__init__.py", line 277, in initialize
    DefaultDialect.initialize(self, connection)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 325, in initialize
    self.default_isolation_level = self.get_isolation_level(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2681, in get_isolation_level
    cursor.execute("show transaction isolation level")
  File "/usr/local/lib/python3.8/site-packages/duckdb_engine/__init__.py", line 140, in execute
    self.__c.execute(statement)
duckdb.CatalogException: Catalog Error: Table with name transaction_isolation does not exist!
Did you mean "pg_constraint"?

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

Traceback (most recent call last):
  File "src/example.py", line 17, in <module>
    Base.metadata.create_all(eng)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4553, in create_all
    bind._run_visitor(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2094, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File "/usr/local/lib/python3.8/contextlib.py", line 113, in __enter__
    return next(self.gen)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2086, in _optional_conn_ctx_manager
    with self._contextual_connect() as conn:
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2302, in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2339, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1583, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 366, in connect
    return _ConnectionFairy._checkout(self, self._fairy)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 349, in _do_get
    c = self._create_connection()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 664, in __connect
    pool.dispatch.first_connect.for_modify(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 314, in exec_once_unless_exception
    self._exec_once_impl(True, *args, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 285, in _exec_once_impl
    self(*args, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 322, in __call__
    fn(*args, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 1397, in go
    return once_fn(*arg, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 199, in first_connect
    dialect.initialize(c)
  File "/usr/local/lib/python3.8/site-packages/duckdb_engine/__init__.py", line 277, in initialize
    DefaultDialect.initialize(self, connection)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 325, in initialize
    self.default_isolation_level = self.get_isolation_level(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2681, in get_isolation_level
    cursor.execute("show transaction isolation level")
  File "/usr/local/lib/python3.8/site-packages/duckdb_engine/__init__.py", line 140, in execute
    self.__c.execute(statement)
sqlalchemy.exc.ProgrammingError: (duckdb.CatalogException) Catalog Error: Table with name transaction_isolation does not exist!
Did you mean "pg_constraint"?
(Background on this error at: http://sqlalche.me/e/13/f405)

Code of Conduct

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

[Mause]

Hi @coffepowered!

I just gave this a go quick go in a clean environment, and it worked without issues for me. Are you able to give me the output of pip freeze in your environment to help me investigate further?

[coffepowered]

For sure! Here it is. I am wondering if that might be related to some sort of IO error since I'm inside mounted folders.

absl-py==1.0.0
asttokens==2.2.1
backcall==0.2.0
blosc==1.11.1
boto3==1.16.11
botocore==1.19.63
certifi==2022.12.7
chardet==3.0.4
comm==0.1.3
cx-Oracle==8.3.0
debugpy==1.6.7
decorator==4.4.2
duckdb==0.7.1
duckdb-engine==0.7.0
executing==1.2.0
idna==2.8
importlib-metadata==6.4.1
ipykernel==6.22.0
ipython==8.12.0
jedi==0.18.2
jmespath==0.10.0
jsonargparse==3.15.0
jupyter_client==8.2.0
jupyter_core==5.3.0
matplotlib-inline==0.1.6
nest-asyncio==1.5.6
networkx==2.5.1
numpy==1.20.3
ortools==9.6.2534
packaging==23.1
pandas==1.2.4
parso==0.8.3
pexpect==4.8.0
pickleshare==0.7.5
platformdirs==3.2.0
plotly==4.14.3
prompt-toolkit==3.0.38
protobuf==4.22.1
psutil==5.9.4
psycopg2-binary==2.8.6
ptyprocess==0.7.0
pure-eval==0.2.2
Pygments==2.15.0
pymssql==2.2.7
python-dateutil==2.8.2
python-dotenv==1.0.0
pytz==2023.3
PyYAML==6.0
pyzmq==25.0.2
requests==2.22.0
requests-aws4auth==1.0.1
retrying==1.3.4
s3transfer==0.3.7
scipy==1.10.1
six==1.16.0
SQLAlchemy==1.3.20
stack-data==0.6.2
tabulate==0.9.0
tornado==6.2
tqdm==4.64.1
traitlets==5.9.0
typing_extensions==4.5.0
urllib3==1.25.11
wcwidth==0.2.6
zipp==3.15.0

[Mause]

If I had to guess, it's probably your SQLAlchemy version then. I do test against 1.3.24, but it looks like you're using 1.3.20. I'll do a proper test later when I'm back at my laptop

[Mause]

Yep, looks like it's SQLAlchemy 1.3.20 that's the issue - 1.3.22 works perfectly fine. Are you able to upgrade to this version?

If so, I'll probably bump the minimum in the pyproject.toml

[coffepowered]

I can confirm that it works with 1.3.22 :).
Thanks a lot for your assistance! Dealing with some NotImplementedErrors now but your library provides a great service 🥇