1

I'm currently setting up a class using SQL Alchemy. This class has a start_date attribute, defined such as:

from sqlalchemy import Column, DateTime
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Foo(Base):
    __tablename__ = "foo"

    id = Column(UUID(as_uuid=True), primary_key=True)
    start_date = Column(DateTime, nullable=True)

Yet, I'm facing a type issue:

foo = Foo(start_date="2019-05-05")
print(foo.start_date, type(foo.start_date))

# 2019-05-05 <class 'str'>

I would expect a datetime here, especially as when fetching the same record from the database, I retrieve a date time object:

foo = Foo(id=uuid.uuid4(), start_date="2019-05-05")
db_session.add(foo)
db_session.commit()

db_session.refresh(foo)
print(foo.start_date, type(foo.start_date))

# 2019-03-01 00:00:00 <class 'datetime.datetime'>

Is there any way to force a cast from the Column declaration?

Jonathan Petitcolas
  • 4,254
  • 4
  • 31
  • 42
  • 1
    Yeah, you have to parse it yourself. You can use a validator method and `datetime.strptime` to abstract it away: https://docs.sqlalchemy.org/en/13/orm/mapped_attributes.html#simple-validators – SuperShoot Nov 04 '19 at 20:07
  • 1
    Very much related: https://stackoverflow.com/questions/8980735/how-can-i-verify-column-data-types-in-the-sqlalchemy-orm – "SQLAlchemy doesn't build this in as it defers to the DBAPI/database as the best and most efficient source of validation and **coercion** of values." – Ilja Everilä Nov 05 '19 at 06:05

1 Answers1

1

I suspect what's happening here is you're actually accessing in the first snippet is the very string you passed as the start_date keyword argument. This would suggest that SQLAlchemy does not actually coerce the type of your input.

When the object is committed to the database, this string ('2019-05-05') is passed to Postgres. Since SQL does type casting on quoted values, and this is in the correct format, it is added to the database as a timestamp.

After the insert the whole row is read from the database. This would return a datetime instance and the attribute would be set to a datetime value.

Keep in mind that this is partly conjecture on my part and I haven't actually looked up the way SQLAlchemy handles the input values.

Kendas
  • 1,963
  • 13
  • 20
  • I'm thinking the same. But that's kind of annoying if you want to compare dates **before** inserting data in the database. :) – Jonathan Petitcolas Nov 04 '19 at 18:30
  • in that case, look into [`python-dateutil`](https://pypi.org/project/python-dateutil/) and in particular the [`dateutil.parser.parse`](https://dateutil.readthedocs.io/en/stable/parser.html#dateutil.parser.parse) function. – Kendas Nov 04 '19 at 18:37
  • Also, if the formatting is consistent (dashes in the same places, all separating characters the same and no timezone), the ISO8601 has a convenient way of being in lexical and logical order at the same time. – Kendas Nov 04 '19 at 18:41