Using the Simple CRUD Example, Article Creation is not Persisted

[starryknight64]

Using the example found here:
https://github.com/amisadmin/fastapi-amis-admin/tree/master/fastapi_amis_admin/crud

Any attempt made to persist an "Article" in the SQLite DB fails. Also, starting a new project from scratch with this example fails over and over. Details below...

The example doesn't utilize the package that it claims to be an example for:
pip install fastapi-sqlmodel-crud

Installing fastapi-sqlmodel-crud causes the example to fail at this line:
from fastapi_amis_admin.crud import SQLModelCrud

Clearly, it appears that the fastapi_amis_admin package is what's required in this example. However, after installing it, the import line STILL doesn't work!
from fastapi_amis_admin.crud import SQLModelCrud

It isn't until I change that line to the following that things start lining up somewhat:
from fastapi_amis_admin.crud import SqlalchemyCrud

Then, changing this line makes python 3.11 happy again:
article_crud = SqlalchemyCrud(model=Article, engine=engine).register_crud()

However, when trying to run it, I'm forced to deal with this:

    from pydantic_settings import BaseSettings  # noqa: F401
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ModuleNotFoundError: No module named 'pydantic_settings'

Which led me to this note:
https://github.com/amisadmin/fastapi-amis-admin/tree/master#note

And then led me to use this command:
pip install fastapi_amis_admin[sqlmodel]

Which allowed me to (FINALLY) run the example code, BUT got this lovely DeprecationWarning about "on_event" being deprecated.

Oh well, it's just a warning! I should be able to navigate to http://127.0.0.1:8000 right? WRONG. Can't do that yet because uvicorn (or comparable) wasn't used to actually start the app such that it be usable via browser! Ok no problem... I'm a grown man, I can add that in... so I installed uvicorn with pip and imported it and added the following line after the (deprecated!) startup function:
uvicorn.run(app, host="localhost", port=8000)

All should be well now, right? Starting it up and navigating to http://localhost:8000/docs does FINALLY start to look promising!

So I use the "Create Article" POST functionality on that page to create the following Article:

[
  {
    "title": "This is an article",
    "description": "Such description!",
    "status": true,
    "content": "Much wow"
  }
]

Clicking "Execute" does SEEM that the article was created, as it returns HTTP Status Code 200 and returns a new Article with ID 1. Additionally, when I use the "List Articles" POST function it also lists the article with ID 1 that I just created.

HOWEVER, when I open up amisadmin.db with an SQLite DB Client and look in the article table, no articles exist!

What am I doing wrong and why does the "Simple" example need so much work to get to this point?

I've attached my (modified) main.py as a txt file as well as my requirements.txt and SQLite DB in case this helps any:
main.py.txt
requirements.txt
amisadmin.db.zip

[starryknight64]

After HOURS of debugging, I figured out the following:

1. You need to be using session.commit() instead of session.flush(). This is also indicated in SQLModel's (and FastAPI's) documentation:
   https://sqlmodel.tiangolo.com/tutorial/fastapi/simple-hero-api/#create-heroes-path-operation
   https://fastapi.tiangolo.com/tutorial/sql-databases/#read-data
2. sqlite (specifically the aiosqlite package) required a newer version to be installed than the one specified in your pdm.lock file. aiosqlite==0.19.0 (was aiosqlite==0.17.0)
3. For my use case, I'm using MySQL (specifically mysqlclient), and it also requires a newer version to be installed than what I had. mysqlclient==2.2.1

Finally, after updating aiosqlite, I figured out that the versions of the packages in your pdm.lock file were out-of-date. After updating them, this example started working as expected! Woo!

Below are the package versions I ended up using getting this to work properly. The commented text is the previous version found in your pdm.lock file. Also, if it's not too much trouble, it'd be fantastic if you included a requirements.txt file in this repo.

It should be noted that at the time of writing this, I was using fastapi_amis_admin==0.7.0 of this package.

# aiosqlite==0.17.0
aiosqlite==0.19.0
# fastapi==0.92.0
fastapi==0.108.0
# pydantic==1.10.2
pydantic==2.5.3
# SQLAlchemy==1.4.41
SQLAlchemy==2.0.23
# sqlalchemy_database==0.1.0
sqlalchemy_database==0.1.1
# sqlmodel==0.0.8
sqlmodel==0.0.14
# typing_extensions==4.3.0
typing_extensions==4.9.0

The full requirements.txt file I ended up with is below:
requirements.txt

Thanks for creating this ability to automatically generate the CRUD! It has been quite an adventure learning FastAPI, SQLModel, and now this in order to get this up and moving. Thank you!

[amisadmin]

Hello and thank you for your question.
Question 1. You can use sqlalchemy_database to add a fastapi main application middleware to achieve automatic submission.

app.add_middleware(site.db.asgi_middleware)

Issues 2 and 3. Dependent Version issues pyproject.toml contains dependent versions that are generally the latest available. I really didn't test the other versions.
The content in pdm.lock is not updated in a timely manner. Later, I will pay attention to this problem and try to synchronize the update.

[starryknight64]

You can use sqlalchemy_database to add a fastapi main application middleware to achieve automatic submission.

app.add_middleware(site.db.asgi_middleware)

Shouldn't that be in the example then? Like I said, it doesn't work as-is. Regardless, thanks for letting me know about this! In the days I've been learning about this I've locally subclassed your SqlalchemyCrud class with session.commit() in place of session.flush() and it's working as expected. Is there any particular reason you decided not to use session.commit()?

Dependent Version issues pyproject.toml contains dependent versions that are generally the latest available. I really didn't test the other versions. The content in pdm.lock is not updated in a timely manner. Later, I will pay attention to this problem and try to synchronize the update.

Honestly, all I'm after is some way to use your (quite convenient!) codebase to implement all the CRUD for whatever I'm working on (and possibly other things too as I know this repo is intended for more than just this one use-case). If it means I need to look somewhere else for the prerequisite dependencies then I'd want to know where that is and how I can best install those.