Insert MySQL timestamp column value with SqlAlchemy

Question

I have a sqlalchemy class mapping to a database table in MySQL innoDB. The table has several columns and I am able to successfully populate them all except from a TIMESTAMP column:

The mapping:

class HarvestSources(Base):
    __table__ = Table('harvested', metadata, autoload=True)

The column on MySQL is a TIMESTAMP which has CURRENT_TIMESTAMP as default value, but when I insert a row it's being filled with NULL.

If default is not working then I need to manually set the timestamp, how could I do either of them.

SqlAlchemy code to insert row to table:

source = HarvestSources()
source.url = url
source.raw_data = data
source.date = ?

DB.session.add(source)
DB.session.commit()

Solution 1

datetime objects are converted to timestamps, so you can just use:

from datetime import datetime
...
source.date = datetime.now()

or datetime.utcnow() if you want to save it using utc. The default (CURRENT_TIMESTAMP) uses the local timezone, so datetime.now() is closer to that - but it almost always should be preferrable to store time related data in UTC, and do timezone conversions only when presenting data to the user.

Solution 2

mata answer is very clear on how to add a timestamp value. If you want to add the timestamp added automatically on insert and update. You may consider have a BaseMixin class and register sqlalchemy event for every class. Example implementation is below:

class BaseMixin(object):

  __table_args__ = {'mysql_engine': 'InnoDB'}

  id = sa.Column(sa.Integer, primary_key=True)
  created_at = sa.Column('created_at', sa.DateTime, nullable=False)
  updated_at = sa.Column('updated_at', sa.DateTime, nullable=False)

  @staticmethod
  def create_time(mapper, connection, instance):
     now = datetime.datetime.utcnow()
     instance.created_at = now
     instance.updated_at = now

  @staticmethod
  def update_time(mapper, connection, instance):
     now = datetime.datetime.utcnow()
     instance.updated_at = now

  @classmethod
  def register(cls):
     sa.event.listen(cls, 'before_insert', cls.create_time)
     sa.event.listen(cls, 'before_update', cls.update_time)

change your class HarvestSources(Base): to class HarvestSources(Base, BaseMixin):. call HarvestSources.register() on your model init. The updated_at and created_at column will update automatically.