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()

mata · Accepted Answer · 2013-07-23 20:54:23Z

20

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.

edited Jul 23, 2013 at 20:54

answered Jul 23, 2013 at 20:21

mata

69.3k10 gold badges168 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

parsecer Over a year ago

TypeError: float() argument must be a string or a number, not 'datetime.datetime'

mata Over a year ago

@parsecer No idea what you're actually doing and how that's releant to the original question

iChux · Accepted Answer · 2015-05-16 08:18:23Z

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.

Collectives™ on Stack Overflow

Insert MySQL timestamp column value with SqlAlchemy

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related