Column default value persisted to the table

Question

I am currently using a Column that has the following signature:

Column('my_column', DateTime, default=datetime.datetime.utcnow)

I am trying to figure out how to change that in order to be able to do vanilla sql inserts (INSERT INTO ...) rather than through sqlalchemy. Basically I want to know how to persist the default on the table without lossing this functionality of setting the column to the current utc time.

The database I am using is PostgreSQL.

Answer 1

There are multiple ways to have SQLAlchemy define how a value should be set on insert/update. You can view them in the documentation.

The way you're doing it right now (defining a default argument for the column) will only affect when SQLAlchemy is generating insert statements. In this case, it will call the callable function (in your case, the datetime.datetime.utcnow function) and use that value.

However, if you're going to be running straight SQL code, this function will never be run, since you'll be bypassing SQLAlchemy altogether.

What you probably want to do is use the Server Side Default ability of SQLAlchemy.

Try out this code instead:

from sqlalchemy.sql import func
...
Column('my_column', DateTime, server_default=func.current_timestamp())

SQLAlchemy should now generate a table that will cause the database to automatically insert the current date into the my_column column. The reason this works is that now the default is being used at the database side, so you shouldn't need SQLAlchemy anymore for the default.

Note: I think this will actually insert local time (as opposed to UTC). Hopefully, though, you can figure out the rest from here.

Answer 2

If you wanted to insert UTC time you would have to do something like this:

from sqlalchemy import text
...
created = Column(DateTime,
                 server_default=text("(now() at time zone 'utc')"),
                 nullable=False)