Support for SQLAlchemy 2.0

[AlejandroFNadal]

Hello everyone

This issue is mostly a question than an actual issue, it might become one though. Does this connector support SQLAlchemy 2.0 or 1.4? (Or both).

I am running this in SQLAlchemy 2.0:

from sqlalchemy import create_engine, text
engine = create_engine("access+pyodbc://@Test_Db_Arbrapp")

with engine.connect() as msaccess_conn:
    result = msaccess_conn.execute(text("SELECT 'hello world'"))
    print(result.all())

I am getting the following warnings:

connectivity_test.py:3: SADeprecationWarning: The dbapi() classmethod on dialect classes has been renamed to import_dbapi().  Implement an import_dbapi() classmethod directly on class <class 'sqlalchemy_access.pyodbc.AccessDialect_pyodbc'> to remove this warning; the old .dbapi() classmethod may be maintained for backwards compatibility.
  engine = create_engine("access+pyodbc://@Test_Db")

connectivity_test.py:7: SAWarning: Dialect access:pyodbc will not make use of SQL compilation caching as it does not set the 'supports_statement_cache' attribute to ``True``.  This can have significant performance implications including some performance degradations in comparison to prior SQLAlchemy versions.  Dialect maintainers should seek to set this attribute to True after appropriate development and testing for SQLAlchemy 1.4 caching support.   Alternatively, this attribute may be set to False which will disable this warning. (Background on this error at: https://sqlalche.me/e/20/cprf)

The code itself works.

I am willing to put some developing effort as well to help solving this issue with a PR if I get some guidance on how to proceed. If this is a problem of the tool being aimed at sqlalchemy 1.4, should a new sqlalchemy-access be created aimed to sqlalchemy 2.0? Or can this be done inside a single project checking for the version?

Thanks a lot for everyone's work Alejandro

[gordthompson]

The current version of this dialect (v1.1.3) supports SQLA 1.4.

Once SQLAlchemy 2.0 is officially released I planned to abandon any attempt at semver and just release version 2.0.0 of this dialect with the required changes for SQLA 2.0. That is, the major/minor version numbers of this dialect would match those of the SQLA version it supports. That seems to be a more common way for dialects to handle versioning.

[gordthompson]

SADeprecationWarning: The dbapi() classmethod on dialect classes has been renamed to import_dbapi().

I just made this change in the v_2_wip branch. It will be included in the 2.0 release.

SAWarning: Dialect access:pyodbc will not make use of SQL compilation caching as it does not set the 'supports_statement_cache' attribute to True.

https://github.com/gordthompson/sqlalchemy-access/blob/ab28e43fd70ee6d5ee75320d981a1fcb28fe3377/sqlalchemy_access/pyodbc.py#L102

was added in 6f95910cd65b5685219c5285d13747c7fae7a864 and included in the version 1.1.2 release. Are you using an older version of this dialect?

[AlejandroFNadal]

Thanks a lot. I installed sqlalchemy-access using pip, but for some reason it installed version 1.0.8. I set up version 1.1.2 in my requirements.txt and did a pip install -r requirements.txt and now these warnings have dissapeared.

I have another question (if it is useful later on, I could set it up as its own issue) I am setting a column with type Date:

date: Mapped[str] = mapped_column(name="Datum", type_ = Date)

However, when reading data from access, I get the type datetime.datetime MS_Arbrapp.date 2005-01-03 00:00:00 of type <class 'datetime.datetime'>

Is this related to sqlalchemy-access? I could try creating a small reproducible example

[gordthompson]

All "Date" columns in Access are actually Date/Time columns. (Access does not have a date-only type.) Details here:

https://stackoverflow.com/a/26587881/2144390

[AlejandroFNadal]

Thanks a lot for your reply and the link to SO. It has been very helpful.