Databases
StackOverflow2013
Postsdbo
POSQLAlchemy StaleDataError on deleting items inserted via ORM sqlalchemy.orm.exc.StaleDataError
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLAlchemy StaleDataError on deleting items inserted via ORM sqlalchemy.orm.exc.StaleDataError
    primarykey
    data
    text
    <p>I'm having an issue where I get a an error such as this one:</p> <pre><code>"MyPyramidApplication Error"&lt;class 'sqlalchemy.orm.exc.StaleDataError'&gt;: DELETE statement on table 'page_view' expected to delete 6 row(s); Only 0 were matched. </code></pre> <p>So, I have a good idea what is causing the issue but I have been unable to solve it.</p> <p>I have a page_view model, that has a foreign key on <code>page_id</code> and a <code>user_id</code>.</p> <p>Here's what the model looks like:</p> <pre><code>page_view_table = sa.Table( 'page_view', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('page_id', sa.Integer, sa.ForeignKey('guide.id')), sa.Column('user_id', sa.Integer, sa.ForeignKey('user.id')), sa.Column('last_view', sa.DateTime, nullable=False), sa.UniqueConstraint('user_id', 'page_id'), mysql_engine='InnoDB', mysql_charset='utf8mb4' ) </code></pre> <p>Here's what the relations look like</p> <pre><code>orm.mapper(Page, page_table, properties = { 'users_viewed': sa.orm.relation( User, secondary=page_view_table, backref='page'), } ) </code></pre> <p>I am adding some items to my database using an insert statement, something similar to this:</p> <pre><code>ins = model.page_view_table.insert() sql = str(ins) sql += ' ON DUPLICATE KEY UPDATE last_view = :last_view' session = model.Session() session.execute(sql, page_views) mark_changed(session) </code></pre> <p>As far as I can tell from the logs, the transactions gets committed properly and I see the items in the DB.</p> <p>However, when I try to delete the page item using the ORM, I get the StaleDataError exception. Looking at the logs, I see the ORM issuing a delete statement but then rolling back due to the error.</p> <p>I have tried experimenting with <code>session.expire_all()</code> as well as <code>session.expunge_all()</code> right after the insert statement but they weren't very helpful and I still the error.</p> <p>Here's what I see in the SQLAlchemy logs.</p> <pre><code>2011-11-05 18:06:08,031 INFO [sqlalchemy.engine.base.Engine][worker 3] DELETE FROM page_view WHERE page_view.page_id = %s AND page_view.user_id = %s 2011-11-05 18:06:08,031 INFO [sqlalchemy.engine.base.Engine][worker 3] (13818L, 259L) 2011-11-05 18:06:08,032 INFO [sqlalchemy.engine.base.Engine][worker 3] DELETE FROM page_view WHERE page_view.page_id = %s AND page_view.user_id = %s 2011-11-05 18:06:08,033 INFO [sqlalchemy.engine.base.Engine][worker 3] (13818L, 259L) 2011-11-05 18:06:08,033 INFO [sqlalchemy.engine.base.Engine][worker 3] ROLLBACK </code></pre> <p>I thought the double delete statement was a suspect, maybe pointing to a misconfigured ORM relation but I don't think that's the case.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScduruk
    1. UScduruk
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQLAlchemy StaleDataError on deleting items inserted via ORM sqlalchemy.orm.exc.StaleDataError
      1. USaculich
      1. VTFavorite
    2. VO
      singulars
      1. POSQLAlchemy StaleDataError on deleting items inserted via ORM sqlalchemy.orm.exc.StaleDataError
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQLAlchemy StaleDataError on deleting items inserted via ORM sqlalchemy.orm.exc.StaleDataError
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload