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?
An untyped bindparam bypasses the usual type construction:
The type of a bindparam() is significant especially in that the type will apply pre-processing to the value before it is passed to the database. For example, a bindparam() which refers to a datetime value, and is specified as holding the DateTime type, may apply conversion needed to the value (such as stringification on SQLite) before passing the value to the database.
Specifying the target column type will produce the desired behaviour
bindparam('cd', type_=DateTime)
Properly typed, SQLAlchemy will convert a datetime to a string like this: 2024-01-01 12:00:00.000000; without the type the value is just stringified: 2024-01-01 12:00:00. datetime.now() works because it outputs microseconds, so stringifying just happens to produce the required format (except when microseconds is zero).