Relax `sqlalchemy` version requirements

[cofin]

What happened?

I have been working with sqlalchemy in the main 2.0 branch, and I'm unable to install duckdb-engine. Would it be possible to relax the pinned version? Are there any known issues with SQL Alchemy 2.0?

DuckDB Engine Version

v0.6.1

DuckDB Version

v0.4.0

Relevant log output

❯ poetry add duckdb-engine
Using version ^0.6.1 for duckdb-engine

Updating dependencies
Resolving dependencies... (15.0s)

  SolverProblemError

  Because duckdb-engine (0.6.1) depends on sqlalchemy (>=1.3.19,<2.0.0)
   and no versions of duckdb-engine match >0.6.1,<0.7.0, duckdb-engine (>=0.6.1,<0.7.0) requires sqlalchemy (>=1.3.19,<2.0.0).
  So, because oracle-db-assessment depends on both sqlalchemy (branch main) and duckdb-engine (^0.6.1), version solving failed.

Code of Conduct

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

[Mause]

Hi @cofin, given that Sqlalchemy 2 hasn't been released in any form (to my knowledge anyway, it doesn't even seem to be on pypi yet) and has a number of breaking changes, I'm hesitant to declare it officially supported

If you'd still like this pin changed regardless of the above, I can probably do that

[cofin]

Thanks for the quick reply. After thinking about it a bit, I'll create a local fork to ensure it works. This will hopefully minimize issues/confusion for others using the library.

Once we have a little more evidence that it runs (or not), I can report back here/create a PR with the bump.

[Mause]

In my own testing it looks like there are many breaking changes that would require work in duckdb-engine. Until sqlalchemy 2 is closer to being released (probably when it's actually on pypi), it's not worth doing this work

[cofin]

I agree. I didn't get to do a full test, but I did notice some issues. Sorry it took me so long to come back and update.

[cofin]

I'm going to close this issue for now.

[Mause]

Looks like sqlalchemy 2 is finally on pypi, I'll see if this is possible to implement now

[nardi]

SQLAlchemy 2.0 should be released soon (rc1 is out), any plans to work on this? Or any pointers to what changes need to be made so someone else could pick it up? :)

[Mause]

I've made some headway in #502 and https://github.com/Mause/duckdb_engine/pull/439, but there seems to be a lot of backwards incompatible breakage so far, so I'll need to do some investigation to see how I can limit the hacks required. It might be easiest to drop SQLA 1.3 support (keeping 1.4)

[cpcloud]

SQLAlchemy 2.0 was released yesterday!

[cpcloud]

In doing the 2.0 upgrade for ibis, the easiest thing was to set SQLALCHEMY_WARN_20=1 using a version strictly less than 2.0 -> turn on pytest warnings-as-errors -> crank out the fixes until there are no more failures.

This should give you some backwards compatibility with sqlalchemy>=1.4,<2

[Mause]

@cpcloud yep, I did see that, I'm currently on holiday but next week I'll have some time. If you have a look at the #439 pr, you can see the hacks I mentioned.

The easy fix for those hacks is to fix the .cursor() and .connection() methods to actually return new connections, as they ought to. Unfortunately, thats means everything attached to that connection is lost, so everything attached using .register(), etc, will be lost.

I feel like that's probably a dealbreaker for ibis at least? I'm trying to not break backwards compatibility there

I'm wondering if it's worth raising a bug report upstream to sqlalchemy at this point

[cpcloud]

I guess this basically limits downstream users to using sqlalchemy's StaticPool for duckdb if they want to avoid session state appearing to suddenly expire because for whatever reason a new connection is created during connection pool checkout.

[cpcloud]

For ibis, I think we'd be fine with that.

[jlaneve]

@Mause this library looks great! We're looking to use this but I think we're going to need SQLAlchemy 2.0 support - I see the PR to get support is in draft mode and hasn't been updated recently. Do you happen to have any updates and/or want any help implementing?

[Mause]

@jlaneve I'm still on holiday, and I'm still not sure what to do about the issues I mentioned above

[cpcloud]

@Mause Is there some wisdom we can lift from the sqlite dialect? It seems like that would have to solve the same problems.

[jlaneve]

We figured out a way to relax our SQLAlchemy requirement, so we're all good! Thank you for following up

[Mause]

Hey folks, I've put out a release candidate which hopefully won't break anything! If you could test it out in the next couple of days and report back with bug reports I'd really appreciate it 😁