problems creating records when table has compound primary key and server defaults set

[Krzysztof Malinowski]

# -*- coding: utf-8 -*-

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///foo.db')
Base = declarative_base()

Session = sessionmaker(bind=engine)
session = Session()


class Project(Base):

    __tablename__ = 'project'

    id_ = Column(Integer, primary_key=True, unique=True, nullable=False)


class User(Base):

    __tablename__ = 'user'

    id_ = Column(Integer, primary_key=True, unique=True, nullable=False)


class TS(Base):

    __tablename__ = 'timesheet'
    __table_args__ = (
        PrimaryKeyConstraint('user_id', 'project_id', 'date'),
        UniqueConstraint('user_id', 'project_id', 'date')
    )

    user_id = Column(Integer, ForeignKey("user.id_"), nullable=False)
    project_id = Column(Integer, ForeignKey("project.id_"), nullable=False)
    # date = Column(DateTime, server_default=text('CURRENT_TIMESTAMP'), nullable=False)  # mysql ver.
    date = Column(DateTime, server_default=text("(datetime('now','localtime'))"), nullable=False)  # sqlite ver.


Base.metadata.create_all(engine)

u1 = User(id_=1)
p1 = Project(id_=1)
ts1 = TS(user_id=u1.id_, project_id=p1.id_)
session.add(u1)
session.add(p1)
session.add(ts1)
session.flush()
session.commit()

# let's try accesing ts1.date
print ts1.date

and we get the following traceback:

Traceback (most recent call last):
  File "composite_pk.py", line 90, in <module>
    print ts1.date
  File "/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 237, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 578, in get
    value = state._load_expired(state, passive)
  File "/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 474, in _load_expired
    self.manager.deferred_scalar_loader(self, toload)
  File "/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 669, in load_scalar_attributes
    raise orm_exc.ObjectDeletedError(state)
sqlalchemy.orm.exc.ObjectDeletedError: Instance '<TS at 0x7fd96e705650>' has been deleted, or its row is otherwise not present.

But when we look into the db table - the object has been created correctly. When the date column is not part of the compound PK this work just fine. The same happens on a MySQL backend.

[Mike Bayer]

so this definitely at least needs documentation in the area of defaults as special workarounds are needed when the default is part of a primary key. Basically the server defaults are never part of a "prefetch" when a core Insert is run, and in the case of a primary key, it needs to be. The solution is to double it up as a default also, but making this more intricate is that on SQLite at least we need to establish the type-processing for the date as well:

class TS(Base):

    __tablename__ = 'timesheet'

    __table_args__ = (
        PrimaryKeyConstraint('user_id', 'project_id', 'date'),
    )

    user_id = Column(Integer, nullable=False)
    project_id = Column(Integer, nullable=False)
    date = Column(
        DateTime,
        default=literal_column("datetime('now', 'localtime')", DateTime),
        server_default=text("(datetime('now','localtime'))"),
        nullable=False)

Because setting something up as a "default" vs. a "server_default" can be different, as is the case here (return type needed vs. not), it's not clear if this should at least be the way it needs to be done, but that there should be exceptions or warnings spit out if the insert() is being asked for inserted primary key.

the milestone here is for making it so the workaround isn't needed, if it can be decided that's appropriate. A documentation section should be added to http://docs.sqlalchemy.org/en/latest/core/defaults.html#server-side-defaults in the interim that describes special steps to take when the default is part of the primary key and "prefetch" is desired.

[Mike Bayer]

#3923 is part of this and can be merged sooner

[Krzysztof Malinowski]

Thanks for a fast response :)