On Mar 27, 2013, at 1:02 PM, Moritz Schlarb <[email protected]> wrote:
> Hi there everyone, > > I am kind of looking for a "best practice" on how to implement > automatically setting and updating columns for created and modified > timestamps in SQLAlchemy, preferrably database-agnostic. > > First of all, is DateTime the appropriate column type or should it be > timestamp instead? Both render to datetime on the Python side, so the > arguments of generic SQL discussions on that topic aren't so relevant here. DateTime should be the best choice here, as TIMESTAMP in some cases implies automatic behavior like on MySQL. > > Now for the automatic updating, I have two variants: > > 1) > created = Column(DateTime, nullable=False, server_default=func.now()) > modified = Column(DateTime, nullable=False, server_default=func.now(), > server_onupdate=func.now()) > > Which only work if the database supports an ON UPDATE statement, which > e.g. sqlite doesn't seem to. > > 2) > created = Column(DateTime, nullable=False, server_default=func.now()) > modified = Column(DateTime, nullable=False, server_default=func.now(), > onupdate=func.now()) > > Which would account for that, or are there databases that don't even > support a DEFAULT value? MySQL might not even support server_default for datetimes here (I'd have to check). On SQLite, you'd be getting the server's datetime format that probably doesn't match the one we use for the sqlite.DateTime type (SQLite only stores dates as strings or integers). The most foolproof system would be to use "default" and "onupdate" across the board, the "now()" function, since it is a SQL function, is rendered inline into the INSERT/UPDATE statement in any case so there's no performance hit. > But the second solution isn't really aesthetic - since the modified > timestamp will now always be updated by SQLAlchemy. > > Isn't there a way to make SQLAlchemy decide whether to omit data for > modified or not based on the actual database dialect used? there's ways this could be achieved, namely that you can use events, or use callable functions for "default" and "onupdate", but IMHO this is unnecessarily awkward. There's no downside to "default=func.now()" except for worrying about people emitting INSERTs directly to the database, which isn't the typical case for a packaged application that's targeting arbitrary database backends. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
