Make SqlAlchemy ORM a first-class experience with SQLite Cloud

[fmorriso]

As of 2024-07-23, it appears that SQLite Cloud and SQLAlchemy ORM do not "play nice" together when it comes to seamlessly querying (SELECT *) from the chinook.sqlite artists table into python classes based on the SQLAlchemy ORM class Base(MappedAsDataclass,DeclarativeBase) technique.

Specifically, when I try the following:

engine = create_engine(self.connection_string, echo=True)

... eventually I want to:

artists = session.query(Artist).all()

I get the error message:
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:sqlitecloud

In this specific case, I'm trying to query via the SQLAlchemy ORM technique of

from sqlalchemy.orm import DeclarativeBase, MappedAsDataclass, mapped_column
from sqlalchemy import Integer
from typing_extensions import Annotated


class Base(MappedAsDataclass,DeclarativeBase):
    intpkauto = Annotated[int, mapped_column(Integer, primary_key=True, autoincrement=True)]

from chinook.base import Base


class Artist(Base):
    """
    ArtistId INTEGER INTEGER PRIMARY KEY AUTOINCREMENT
    Name VARCHAR(128) NOT NULL
    """
    __tablename__ = 'artists'

    ArtistId: Mapped[Base.intpkauto] = mapped_column(init=False)
    Name: Mapped[str] = mapped_column(NVARCHAR(128), nullable=False)

If this type of feature is not (yet?) available, I'll postpone using SQLite Cloud until such time, if ever, as SQLite Cloud will "play nice" with SQLAlchemy ORM.

[Baylexx]

Same here. My flask app heavily uses SQLAlchemy and until there is a proper way to link SQLAlchemy with SQLite Cloud, I will most likely postpone using SQLite Cloud.

[marcobambini]

@fmorriso @Baylexx thanks for your feedback. I would like to point out that we are already working on an SQLAlchemy integration. I'll let you know more as soon as we have something ready to be tested.

[Baylexx]

Sounds great. Do you have an ETA at all?

[danielebriggi]

We plan to have a first release by the end of August.

[marcobambini]

@fmorriso @Baylexx we update the package to be compatible with SQLAlchemy: https://pypi.org/project/sqlalchemy-sqlitecloud/