sqlalchemy - 炼金术。获取插入的默认值

Question

前言：我的任务是将文件存储在磁盘上，文件名的一部分是时间戳。这些文件的路径存储在数据库中。多个文件可能有一个所有者实体（一条消息可以包含多个附件）。为了使事情更容易，我希望数据库中的文件路径（设置为default now()）和磁盘上的文件具有相同的时间戳。

问题：所以插入后，我需要取回默认插入值（在大多数情况下primary_key_id和created_datetime）。

我试过了：

db_session  # Just for clarity
<sqlalchemy.orm.session.AsyncSession object at 0x7f836691db20>

str(statement)  # Just for clarity. Don't know how to get back the original python (not an SQL) statement
'INSERT INTO users (phone_number, login, full_name, hashed_password, role) VALUES (:phone_number, :login, :full_name, :hashed_password, :role)'

query_result = await db_session.execute(statement=statement)

query_result.returned_defaults_rows  # Primary_key, but no datetime
[(243,)]

query_result.returned_defaults  # Primary_key, but no datetime
(243,)

query_result.fetchall()
[]

我的桌子：

Base = declarative_base()  # Main class of ORM; Put in config by suggestion https://t.me/ru_python/1450665

claims = Table(  # TODO set constraints for status
    "claims",
    Base.metadata,
    Column("id", Integer, primary_key=True),

我的查询

async def create_item(statement: Insert, db_session: AsyncSession, detail: str = '') -> dict:
    try:  # return default created values
        statement = statement.returning(statement.table.c.id, statement.table.c.created_datetime)
        return (await db_session.execute(statement=statement)).fetchone()._asdict()
    except sqlalchemy.exc.IntegrityError as error:
        # if psycopg2_errors.lookup(code=error.orig.pgcode) in (psycopg2_errors.UniqueViolation, psycopg2_errors.lookup):
        detail = error.orig.args[0].split('Key ')[-1].replace('(', '').replace(')', '').replace('"', '')
        raise HTTPException(status_code=422, detail=detail)

PS Sqlalchemy v 1.4

标签： sqlalchemyflask-sqlalchemy