SQLAlchemy backlog

[amotl]

Hi there,

while working on #391, some backlog items have accumulated. I will gather them within this ticket.

Internals

We've identified a few shortcomings in the internal implementation of the CrateDB SQLAlchemy dialect. While it seems to work in general, those spots can well be improved, in order to better align with the internal API hooks of SQLAlchemy, and how the CrateDB dialect interacts with that.

• [ ] Migrate away from using the before_execute event. Mike Bayer advised us to hook into ExecutionContext.pre_exec() for rewriting UPDATE statements instead of using any kinds of engine- or dialect-events, like our current implementation. Discussions: - https://github.com/crate/crate-python/pull/391#discussion_r874170754 - https://github.com/sqlalchemy/sqlalchemy/discussions/5915#discussioncomment-2782664
• [ ] Investigate CompileError: Unconsumed column names Issue: GH-509
• [ ] GH-508
• [ ] Investigate whether SQLAlchemy's MutableDict implementation can be used for implementing CrateDB's OBJECT type, see https://github.com/crate/crate-python/pull/561#pullrequestreview-1514116649.

More

• [ ] Improve code coverage and reduce code duplication of the visit_update_14 method. Reference: https://github.com/crate/crate-python/pull/391#pullrequestreview-991961268
• [ ] https://github.com/crate/crate-python/issues/423

With kind regards, Andreas.

[robd003]

Getting support for async SQLAlchemy would be super useful

I have a few queries that can take slightly over 1 sec to execute and being able to not block would be HUGE