Support sqlalchemy 1.4

[holymonson]

Due to https://github.com/sdementen/piecash/commit/525dfe5dd32ac97567908fa80b26b201a9b1c221, may I ask if there is any plan to fix it? I hope we could leave this issue to track supporting sqlalchemy 1.4 (or even 2.0). Thanks.

[sdementen]

I have not yet planned to upgrade piecash to use SA 2.0 but am ready to support and help anyone wanting to propose a PR for this. I think it would be great to update it even though I am not sure if it is a blocking factor for anyone. would you have a specific use case that requires an updated version of SA?

[holymonson]

Not really, I'm mixing piecash with other projects sharing sqlalchemy but I would seperate it out.

Besides, I'm maintaining https://github.com/conda-forge/piecash-feedstock, so it would be nice to get informed so I could release the version constraint.

[rothloup]

Unless someone can tell me how I can have two versions of sqlalchemy in the same python program, this is a blocking issue for me. I need SA 2.0 for my application, but piecash requires SA 1.x.

I'm fine with mixing the two versions, if only I knew how...

[kapinga]

I've just started using this project and am interested in getting SA 1.4 / 2.0 compatibility working (along with a few other improvements).

Some initial playing around with this seems to show that getting piecash to work on SA 1.4 is pretty straightfoward. There's obviously much more to do to get all the way to 2.0. @sdementen - I'll reply back to this thread if I hit any road blocks.

And by way of introduction - I recently got back into using GnuCash and came across this package when looking into ways to leverage my experience in Python in analyzing my GnuCash data. I work with Python and some MySQL in my day job, although I've not dug into the ORM portions of sqlalchemy prior to this.

[Wm-X]

My reading is that SA 1.4 includes most of the stuff I'd like to use in SA 2.n ; I think moving piecash from SA 1.3 to SA 1.4 would be important, if only as a base for testing SA 2.0; I have basic frustrations like not being able to do a SELECT that 1.4 allows and 2.0 seems to encourage.

[Wm-X]

@all I am happy to test sa 1.3 => 1.4 ideas if anyone has them; then we can look at sa 2.n

[tim-rohrer]

I've forked this repo in order to try and resolve a kvp problem. However, I can't installed the deps. pipenv install -e . results in

ERROR:pip.subprocessor:[present-rich] python setup.py egg_info exited with 1
[ResolutionFailure]:   File "/opt/local/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pipenv/resolver.py", line 811, in _main
[ResolutionFailure]:       resolve_packages(
[ResolutionFailure]:   File "/opt/local/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pipenv/resolver.py", line 759, in resolve_packages
[ResolutionFailure]:       results, resolver = resolve(
[ResolutionFailure]:       ^^^^^^^^
[ResolutionFailure]:   File "/opt/local/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pipenv/resolver.py", line 738, in resolve
[ResolutionFailure]:       return resolve_deps(
[ResolutionFailure]:       ^^^^^^^^^^^^^
[ResolutionFailure]:   File "/opt/local/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pipenv/utils/resolver.py", line 1100, in resolve_deps
[ResolutionFailure]:       results, hashes, markers_lookup, resolver, skipped = actually_resolve_deps(
[ResolutionFailure]:       ^^^^^^^^^^^^^^^^^^^^^^
[ResolutionFailure]:   File "/opt/local/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pipenv/utils/resolver.py", line 899, in actually_resolve_deps
[ResolutionFailure]:       resolver.resolve()
[ResolutionFailure]:   File "/opt/local/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pipenv/utils/resolver.py", line 687, in resolve
[ResolutionFailure]:       raise ResolutionFailure(message=str(e))
[pipenv.exceptions.ResolutionFailure]: Warning: Your dependencies could not be resolved. You likely have a mismatch in your sub-dependencies.
  You can use $ pipenv install --skip-lock to bypass this mechanism, then run $ pipenv graph to inspect the situation.
  Hint: try $ pipenv lock --pre if it is a pre-release dependency.
ERROR: metadata generation failed

If I take the suggested steps to determine the cause of the situation, I get

➜  piecash git:(master) ✗ pipenv graph
piecash==1.2.0
  - click [required: Any, installed: 7.1.2]
  - pytz [required: Any, installed: 2020.1]
  - SQLAlchemy [required: >=1.0,<1.4, installed: 1.3.20]
  - SQLAlchemy-Utils [required: !=0.36.8, installed: 0.36.8]
    - six [required: Any, installed: 1.15.0]
    - SQLAlchemy [required: >=1.0, installed: 1.3.20]
  - tzlocal [required: Any, installed: 2.1]
    - pytz [required: Any, installed: 2020.1]
pytz-deprecation-shim==0.1.0.post0
  - tzdata [required: Any, installed: 2023.3]

I'm pretty new to Python, but the output above seems to indicate I'm required to install SQLAlchemy-Utils other than 0.36.8, but that the installer did just that. I'll keep poking around, but if my error is related to changes in piecash and SQLAlchemy, I'm blocked until I/we can get this resolved.

Thank you.

[Wm-X]

@tim-rohrer I think the basic mismatch is that piecash has a <SQLAlchemy limit, when I first looked at this I presumed the change in SQLAlchemy was on the major number, it is more complicated, SQLAlchemy made significant changes that piecash can't account for without someone doing some work.

I am a rubbish python programmer but very good at database stuff so I am reverting to SQL wherever I find a limit in piecash.

I don't blame, S, he had his interest, he did fantastic work. It is ordinary if someone moves on.

[kapinga]

I've submitted a PR to support SQLAlchemy 1.4. There was one change in SQLAlchemy's API that needed to be addressed, but otherwise it works pretty much as-is.

I also explored what might be needed to make this library SQLAlchemy 2.0 compatible. tl;dr: I don't see how that would be possible without changing the external API of piecash. Most of the changes needed are relatively minor, but SA 2.0 disables merging objects into the session via the backref cascade, with no obvious means to reinstate the old behavior. This means that example code on the piecash docs would stop working as expected because newly created objects like Split would not be automatically added to the session. We might be able to hack the constructor to automatically add objects into the session upon creation, but that might cause objects to get merged in that otherwise wouldn't be.

Given that SA 1.4 supports SA 2.0-style usage, that should be good enough for my needs. The changes needed to properly support SA 2.0 are far more work than can be justified right now, and would likely warrant a major version bump to piecash too.

[Wm-X]

should we close this issue and roll it up (or whatever the github term is) into piecash 2.0 ?

[sdementen]

the 1.2.1 release should work with SA 1.4. If it works for you @holymonson could you close this issue ?