CODEWITHSUNDEEP
pythonsqlalchemysql-update
Renier
Renier

Reputation: 1535

How to properly update table object(metadata)

Consider the following:
I know that to update a table defined as:

class MessageBoard(Base):
    __tablename__ = "messageboard"
    id = Column(
        Integer,
        Sequence('messageboard_id', optional=True),
        primary_key=True
    )
    title = Column(Unicode(40), index=True)
    message = Column(UnicodeText())
    to = Column(Unicode(40), index=True)
    # message_type = Column(Unicode(40), index=True) #could be email, sms, or PIMS interface
    created_by = Column(Unicode(40), index=True)
    create_stamp = Column(DateTime(), index=True)
    complete_stamp = Column(DateTime(), index=True)
    status = Column(Unicode(40), index=True)
    # message_type_id = Column(Integer, ForeignKey("board_message_type.id"))
    message_type = relationship(BoardMessageType, secondary=message_type_to_message, lazy="joined", join_depth=2, backref="messageboard")
    agents = relationship(Agents, secondary=agent_message, lazy="joined", join_depth=2, backref="messageboard")

Can be updated like:

s.query(
    MessageBoard
).filter(
    id == 1
).update(
    {"title": "How to update?"}
)

But I do not know how to update a table defined like:

agent_message = Table(
    "agent_message",
    Base.metadata,
    Column("agents_id", Integer, ForeignKey("agents.id"), primary_key=True, onupdate="cascade"),
    Column("message_id", Integer, ForeignKey("messageboard.id"), primary_key=True),
    Column("status", Boolean, index=True, default=False) #if READ: True 
)

I have tried:

q = agent_message.update().where(
    and_(agent_message.c.message_id == read, agent_message.c.agents_id == self.user.agents_id)
).values(
    {"status": True}
)
s.execute(q)

But this raises an error in which the sql statement says that I want to update the agents_id and the status:

IntegrityError: (IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`likeminds_prepaid`.`agent_message`, CONSTRAINT `agent_message_ibfk_1` FOREIGN KEY (`agents_id`) REFERENCES `agents` (`id`))', None) u'UPDATE agent_message SET agents_id=?, status=? WHERE agent_message.message_id = ? AND agent_message.agents_id = ?' ('cascade', 1, u'3', 203671L)

Full stacktrace can be found here

I have also Googled a lot and can not find an example of how to update a table as defined above and include multiple where clauses...

UPDATE

My code above is slightly wrong, instead of using:

s.execute(q)

It should be:

q.execute()

Upvotes: 0

Views: 548

Answers (1)

univerio
univerio

Reputation: 20538

I think this is caused by the onupdate="cascade" argument to the Column constructor, which has a different meaning than the onupdate parameter of ForeignKey.

From the Column documentation (emphasis mine):

  • onupdate – A scalar, Python callable, or ClauseElement representing a default value to be applied to the column within UPDATE statements, which wil [sic] be invoked upon update if this column is not present in the SET clause of the update.

It looks like you want the onupdate="cascade" in the ForeignKey constructor instead:

Column("agents_id", Integer, ForeignKey("agents.id", onupdate="cascade"), primary_key=True)

Upvotes: 1

Related Questions