CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Jim Hunziker
Jim Hunziker

Reputation: 15380

How do I insert the database's default for a column in SQLAlchemy?

I'm trying to insert a row into a Postgresql table that looks like this:

CREATE TABLE launch_ids(
  id SERIAL PRIMARY KEY,
  launch_time TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT
    (now() at time zone 'utc')
);

My class looks like this:

class LaunchId(Base):
    """Launch ID table for runs"""
    __tablename__ = 'launch_ids'

    id = Column(Integer, primary_key=True)
    launch_time = Column(DateTime)

The launch_time should be managed by the database. I know it's possible to use default=datetime.datetime.utcnow(), but that uses the current time on the client. I know it's possible to use default=func.now(), but that means that if the database's definition of the default changes, then I need to change the default in two places.

Here is what I get when I try to insert a row in launch_ids without specifying a value:

l = LaunchId()
session.add(l)
session.commit()

IntegrityError: (psycopg2.IntegrityError) null value in column "launch_time" violates not-null constraint
DETAIL:  Failing row contains (1, null).
 [SQL: 'INSERT INTO launch_ids (launch_time) VALUES (%(launch_time)s) RETURNING launch_ids.id'] [parameters: {'launch_time': None}]

Upvotes: 4

Views: 3335

Answers (2)

Craig Burgler
Craig Burgler

Reputation: 1779

Use FetchedValue:

from sqlalchemy.schema import FetchedValue

class LaunchId(Base):
    ...
    launch_time = Column(DateTime, FetchedValue())

Upvotes: 4

Haleemur Ali
Haleemur Ali

Reputation: 28253

Specify the server_default on the column like this:

class LaunchId(Base):
    """Launch ID table for runs"""
    __tablename__ = 'launch_ids'

    id = Column(Integer, primary_key=True)
    launch_time = Column(DateTime, nullable=False
                         server_default=text("(now() at time zone 'utc')"))

Then adding a new launch_id through the session will work. server_default works differently from default in that it is generated on the server side. Official SQLAlchemy documentation: http://docs.sqlalchemy.org/en/latest/core/defaults.html#server-side-defaults

By specifying nullable=False, this model also becomes a true reflection of the CREATE TABLE you specified, and thus can be generated through Base.metadata.create_all or using alembic.

Upvotes: 1

Related Questions