Unable to select inserted value in sqlalchemy

I have the following script that executes an insert statement and then tries to select the inserted value.

from ast import For from datetime import datetime from sqlalchemy import DateTime, String, ForeignKey, Integer, bindparam, create_engine, insert, literal, select from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, sessionmaker class Base(DeclarativeBase): pass class User(Base): __tablename__ = "users" id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True) name: Mapped[str] = mapped_column(String(200), nullable=False) posts: Mapped[list["Post"]] = relationship(back_populates="author") def __repr__(self) -> str: return f'User(id={self.id!r}, name={self.name!r})' class Post(Base): __tablename__ = "posts" id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True) title: Mapped[str] = mapped_column(String(200), nullable=False) content: Mapped[str] = mapped_column(String, nullable=False) creation_date: Mapped[datetime] = mapped_column(DateTime, nullable=False) author_id: Mapped[int] = mapped_column(ForeignKey("users.id")) author: Mapped["User"] = relationship(back_populates="posts") def __repr__(self) -> str: return f'Post(id={self.id!r}, title={self.title!r}, content={self.content!r}, creation_date={self.creation_date!r})' engine = create_engine("sqlite:///example.db", echo=False) Session = sessionmaker(engine) Base.metadata.create_all(engine) creation_date = datetime(2024, 1, 1, 12, 0, 0) with Session.begin() as sess: sess.add_all([User(name='john')]) sess.flush() sess.execute( insert(Post.__table__) .from_select( ['title', 'content', 'creation_date', 'author_id'], select( literal('Title'), literal('My first content.'), bindparam('cd'), select(User.id).where(User.name == 'john').scalar_subquery() ) ), {'cd': creation_date} ) print(sess.execute(select(Post).where(Post.creation_date == creation_date)).scalar_one())

When running the script I get the following error.

Traceback (most recent call last): File "app/app/backend/sqlalc.py", line 64, in <module> print(sess.execute(select(Post).where(Post.creation_date == creation_date)).scalar_one()) File "app/venv39/lib/python3.9/site-packages/sqlalchemy/engine/result.py", line 1462, in scalar_one return self._only_one_row( File "app/venv39/lib/python3.9/site-packages/sqlalchemy/engine/result.py", line 752, in _only_one_row raise exc.NoResultFound( sqlalchemy.exc.NoResultFound: No row was found when one was required

However, when I change creation_date to the following:

creation_date = datetime.now()

Then, the script runs just fine.

I have found a workaround for this, but I'm curious as to what is cause of this issue.

Does anyone know what causes this issue and why changing creation_date like that solved the issue?