SQLAlchemy: Datetime vergleichen mit Null

Python – 2019-03-08 – 2019-08-17 13:49 – 60

Mit SQLAlchemy datetime mit der aktuellen Zeit zu vergleichen ist mit unter aufwändig - besonders wenn auch NULL zugelassen ist. Mit gut genutzten Hybrideigenschaften lässt sich dieses Problem einfach lösen.

Die Verfügbarkeit eines Artikels kann auf Basis des Veröffentlichungsdatums bestimmt werden. Liegt dieses Datum in der Zukunft - oder es ist noch gar nicht festgelegt - soll ein Artikel ist ein Artikel nicht öffentlich. Liegt das Datum dagegen in der Vergangenheit, schon.

Folgendes ist das Schema eines Artikels:

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:////tmp/test.db')
Base = declarative_base()

class Article(Base):
    __tablename__ = "articles"
    id = db.Column(db.Integer, primary_key=True)
    headline = db.Column(db.String, unique=True, nullable=False)
    content = db.Column(db.Text, nullable=False)
    published_time = db.Column(db.DateTime)

Es sollen nun mittels .filter() oder .filter_by() nur veröffentlichte Artikel gesucht werden. Daher bieten sich Hybridproperties an, die sowohl für einen einzelnen Artikel (ist dieser Artikel veröffentlicht?) als auch in Queries (Suche alle Artikel, die veröffentlicht sind.) verwendet werden können.

Eine naive Pythonumsetzung sieht dann beispielsweise so aus:

...
from sqlalchemy.ext.hybrid import hybrid_property

...
    # Reicht nicht aus!
    @hybrid_property
    def published(self):  # Python
        return self.published_time.isnot(None) and self.published_time < datetime.utcnow()

Dies führt jedoch zu einem Problem: Ist das Veröffentlichungsdatum nicht gesetzt, entspricht dieses NULL. Dieser "Nichtwert" ist unter SQL besonders: Ausdrücke mit Irgendwas < NULL ergibt auch stets NULL, welches jedoch auch nicht FALSE zugewiesen werden kann. In der Pythonumsetzung wird daher auf None - SQLAlchemy repräsentiert so NULL in Python - geprüft. Python "optimiert" so den rechten Teil des Vergleichs weg, der nur geprüft wird, wenn publised_time nicht None ist.

Zwar würden einzelne SQL-Dialekte (z.B. SQLite) mit interner Optimierung mit diesem einfachen Vergleich funktionieren, doch erkennt SQLAlchemy, dass diese Umsetzung nicht portabel ist und gibt kein Boolean zurück. Mit einem explizitem CASE wird dies vermieden:

...
from sqlalchemy.sql import case

...
    @published.expression
    def published(self):  # SQL
        return case([(self.published_time.isnot(None), self.published_time < datetime.utcnow())], else_=False)

Wichtig ist hier, dass die SQL-Umsetzung den gleichen Name trägt, wie die Pythonumsetzung, da sonst diese nicht korrekt zugeordnet wird.

Nun kann korrekt mit

Article.query.filter_by(published=True)

gefilter werden.

Warum wird nicht auf SQL-Funktionen zur Datumserzeugung zurückgegriffen und stattdessen datetime.utcnow() verwendet?

In SQLAlchemy wird ein Objekt des Typs datetime als String umgesetzt, der jedoch nicht dem SQL-Standard ISO 8601 entspricht. Vergleiche, in denen die Uhrzeit wichtig ist, schlagen so fehl.

Vollständiger Code

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import case

engine = create_engine('sqlite:////tmp/test.db')
Base = declarative_base()

class Article(Base):
    __tablename__ = "articles"
    id = db.Column(db.Integer, primary_key=True)
    headline = db.Column(db.String, unique=True, nullable=False)
    content = db.Column(db.Text, nullable=False)
    published_time = db.Column(db.DateTime)

    @hybrid_property
    def published(self):  # Python
        return self.published_time.isnot(None) and self.published_time < datetime.utcnow()

    @published.expression
    def published(self):  # SQL
        return case([(self.published_time.isnot(None), self.published_time < datetime.utcnow())], else_=False)