A short post illustrating how to specify the type of a SQL expression addded to a model with a `column_property` in SQLAlchemy
I'm posting this as it may help anyone who's having the same problem I faced last week.
I was constructing a query with SQLAlchemy and I had some real
columns and one
column_property. Mapping SQL expressions to a
model with hybrid properties or
column_property is extremely
handy. Here's a link to the
However, one thing I couldn't explicitly find in the
documentation is how to specify the type of a
Usually, SQLAlchemy is able to infer the type of a property.
NullType is used as a default type for those cases
where a type cannot be determined.
For example, in the following example SQLAlchemy cannot determine
the type of the column
best_foot, which returns the value of
the biggest value between
from sqlalchemy import Integer, func from sqlalchemy.orm import ( column_property, declarative_base, mapped_column, ) Base = declarative_base() class Player(Base): __tablename__ = "players" id = mapped_column(Integer, primary_key=True) left_foot = mapped_column(Integer) right_foot = mapped_column(Integer) best_foot = column_property( func.greatest(left_foot, right_foot), ) from sqlalchemy.types import NullType assert type(Player.best_foot.type) == NullType
More often than not, this doesn't make a big difference, and your
queries will work as expected; but when attempting a
query you might run into an error as Postgres expect all columns
UNION ALL query to be of the same type.
You can test this out real quick in
psql with this query:
SELECT 1::int UNION ALL SELECT '1'::text;
And you'll be greeted by an error message along these lines:
ERROR: UNION types integer and text cannot be matched.
While this simple query is obviously something you'll never find
in a real application, what I was trying to do involved a
UNION ALL between two queries with a
NullType kicked in.
Luckily, the solution is very simple, and since I've run into
this issue I always try to be explicit when creating a
... class Player(Base): __tablename__ = "players" id = mapped_column(Integer, primary_key=True) left_foot = mapped_column(Integer) right_foot = mapped_column(Integer) best_foot = column_property( func.greatest(left_foot, right_foot), func.cast(func.greatest(left_foot, right_foot), Integer), ) assert type(Player.best_foot.type) == Integer
This will ensure that
best_foot will have the specified type
and prevent a whole class of errors related to column types.
I hope this can help someone else out there!