9

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.

hyperboreean
  • 8,273
  • 12
  • 61
  • 97
  • Not sure I understand you. Are you writing the vanilla SQL statements yourself and want to have PostgreSQL generate the current time as the columns' initial value, or are you using SQLAlchemy to generate the INSERT statement and are hoping that SQLAlchemy will reuse the utcnow method to determine the value of the data used? – Mark Hildreth Mar 18 '11 at 21:33
  • I am using SQLAlchemy only for creating the tables and vanilla SQL for various tasks since I am using it in a high volume data environment and using the ORM is quite slow. – hyperboreean Mar 18 '11 at 21:52

2 Answers2

19

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.

Mark Hildreth
  • 42,023
  • 11
  • 120
  • 109
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)
Dan P.
  • 1,433
  • 1
  • 16
  • 28