Re: [sqlalchemy] Automatic created and modified timestamp columns (best practice?!)
On 03/27/2013 14:26, Moritz Schlarb 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. 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? 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? Hope my questions came out clear and maybe someone can help me! Cheers If you use the ORM part of SQLAlchemy then I would use a 'before_update' event for that. It has the advantage is that the event can be propagated (thanks to propagate=True), which can be really usefull if you use inheritance. For example: def update_updated_listener(mapper, connection, target): target.updated = datetime.now() event.listen(YourClass, 'before_update', update_updated_listener, propagate=True) I usually create a 'last_update' column on the mapped class, something like: 'last_update' : orm.column_property( sql.func.coalesce(table['content'].c.updated, table['content'].c.added) ) -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Automatic created and modified timestamp columns (best practice?!)
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. 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? 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? Hope my questions came out clear and maybe someone can help me! Cheers -- Moritz Schlarb -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Automatic created and modified timestamp columns (best practice?!)
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. 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? 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? Hope my questions came out clear and maybe someone can help me! Cheers, Moritz -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Automatic created and modified timestamp columns (best practice?!)
On Mar 27, 2013, at 1:02 PM, Moritz Schlarb mosch...@metalabs.de 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
