SQLAlchemy datetime berekeningen aan de serverzijde

U vindt er veel SQLAlchemy datetime rekenvoorbeelden met behulp van bijvoorbeeld Python's timedelta functie. Waarom? Ik begrijp dit niet, behalve dat dit gemakkelijk is. Maar is het juist?

Stel we willen alle gebruikersrecords of objecten die twee uur geleden zijn aangemaakt en de record / object definitie is:

class User(Base):

    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    created_on = Column(DateTime, server_default=func.now(), index=True)
    email = Column(String(100), server_default='', index=True)

Als we dan Python de laatste 10 minuten de toegevoegde records / objecten kunnen selecteren, kunnen we zoiets als dit doen:

from datetime import datetime, timedelta

now = datetime.now()
two_hours_ago = now - timedelta(hours=2)

# return all users created less then 2 hours ago
db.query(User).filter(User.created_on > two_hours_ago).all()

De gegenereerde SQL is:

SELECT user.id AS user_id, user.created_on AS user_created_on, user.email AS user_email
FROM user 
WHERE user.created_on > %(created_on_1)s
INFO sqlalchemy.engine.base.Engine {'created_on_1': datetime.datetime(2019, 6, 25, 7, 31, 58, 630959)}

Dit werkt alleen, geeft geldige resultaten, als:

• de databaseserver draait op dezelfde server waar de Python code wordt uitgevoerd
• de databaseserver draait op een andere server dan de Python codeserver en de tijd op beide servers is perfect gesynchroniseerd.

Stel je hebt een aparte database server en de tijd van deze server is 2 minuten uit-synchronisatie. Dan heb je verkeerde, onvolledige resultaten. Ik schrijf al vele jaren server -side queries en ben verbaasd dat er weinig aandacht is voor deze SQLAlchemy vragen en antwoorden.

De enige manier om de juiste resultaten te krijgen is door gebruik te maken van de datetime stempels van de database server records en deze toe te voegen datetime aan of af te trekken datetime van deze records. Met MariaDB / MySQL kunt u de intervalopgave gebruiken:

SELECT user.* FROM user WHERE created_on > (NOW() - INTERVAL 2 HOUR)

Helaas kon ik niet een oplossing vinden SQLAlchemy die geldig zou zijn voor alle databases. SQLAlchemy heeft het text() object, het geeft de waarde door aan de query. Met text(), de vraag wordt de SQLAlchemy vraag:

from sqlalchemy import text

two_hours_ago = text('NOW() - INTERVAL 2 HOURS')

# return all users created less then 2 hours ago
db.query(User).filter(User.created_on > two_hours_ago).all()

De gegenereerde SQL is:

SELECT user.id AS user_id, user.created_on AS user_created_on, user.email AS user_email
FROM user 
WHERE user.created_on > NOW() - INTERVAL 2 HOUR

Houd er rekening mee dat deze zoekopdracht mogelijk niet op alle databasesystemen werkt. Het werkt met MariaDB / MySQL maar het werkt zeker niet met SQLite.

Als u een applicatie ontwikkelt en alles op één computer draait, houd er dan altijd rekening mee dat u in de toekomst de database misschien op een aparte server wilt draaien. Het is dus geen slecht idee om uw vragen voor deze situatie te ontwikkelen.

Links / credits

Flask-sqlalchemy query datetime intervals
https://stackoverflow.com/questions/30495935/flask-sqlalchemy-query-datetime-intervals

SQLAlchemy datetime operations on server side
https://stackoverflow.com/questions/12540175/sqlalchemy-datetime-operations-on-server-side

SQLAlchemy default DateTime
https://stackoverflow.com/questions/13370317/sqlalchemy-default-datetime

Using DATEADD in sqlalchemy
https://stackoverflow.com/questions/15572292/using-dateadd-in-sqlalchemy/15573750#15573750