SQLAlchemy StaleDataError删除通过ORM sqlalchemy.orm.exc.StaleDataError插入的项目 [英] SQLAlchemy StaleDataError on deleting items inserted via ORM sqlalchemy.orm.exc.StaleDataError
问题描述
我遇到一个问题，我收到一个错误，例如这个：
MyPyramidApplication Error class'sqlalchemy.orm.exc.StaleDataError'> ;:表'page_view'上的DELETE语句希望删除6行;只有0匹配。
所以，我有一个好主意是什么导致的问题，但我一直无法解决。
我有一个page_view模型，外键 page_id 和 user_id
以下是模型的外观：
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'
）
p>
orm.mapper（Page，page_table，
properties = {
'users_viewed'：sa.orm.relation （
User，
secondary = page_view_table，
backref ='page'），
}
）
我使用insert语句向数据库添加了一些项目，类似于：
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）
但是，当我尝试删除的时候，我可以告诉从日志，事务获得正确提交和我看到的项目。页面项使用ORM，我得到StaleDataError异常。查看日志，我看到ORM发出一个删除语句，但由于错误回滚。
我已经尝试了会话。 expire_all（）以及 session.expunge_all（），但它们不是很有帮助，我仍然是错误。 / p>
这是我在SQLAlchemy日志中看到的。
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
我认为double delete语句是一个可疑的，可能指向一个错误配置的ORM关系，但我不认为这种情况。
我想我可以给这个问题提示。简短的版本是：你可能需要手动修改数据库中的数据来解决问题。
更长的版本：我有一个类似的问题SQLite。我有下面的映射表：
ingredients =表（'配料'，元数据，
列（'recipe_title' ，Unicode，ForeignKey（'recipes.title'），primary_key = True），
列（'product_title'，Unicode，ForeignKey（'products.title'），primary_key = True）
看到复合主键？我不知何故设法插入两行相同的recip_title / product_title对。我惊讶地发现，在SQLite的侧面没有一个单一的约束（没有主键，没有fereign键 - 它只是一个简单的vanilla表），但是 - 这是sqlalchemy的方式，而不是我的
然后，当我试图删除涉及这两行的persited对象时，sqlalchemy看到它的约束被违反了，它抛出了'StaleDataError'。最后，我只需要从SQLite表中手动删除一个重复的行。
I'm having an issue where I get a an error such as this one:
"MyPyramidApplication Error"<class 'sqlalchemy.orm.exc.StaleDataError'>: DELETE statement on table 'page_view' expected to delete 6 row(s); Only 0 were matched.
So, I have a good idea what is causing the issue but I have been unable to solve it.
I have a page_view model, that has a foreign key on page_id and a user_id.
Here's what the model looks like:
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'
)
Here's what the relations look like
orm.mapper(Page, page_table,
properties = {
'users_viewed': sa.orm.relation(
User,
secondary=page_view_table,
backref='page'),
}
)
I am adding some items to my database using an insert statement, something similar to this:
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)
As far as I can tell from the logs, the transactions gets committed properly and I see the items in the DB.
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.
I have tried experimenting with session.expire_all() as well as session.expunge_all() right after the insert statement but they weren't very helpful and I still the error.
Here's what I see in the SQLAlchemy logs.
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
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.
I guess I can give a hint on this problem. The short version is: "You will probably have to modify data in the Database manually to solve the issue".
The longer version: I had a similar issue with SQLite. I had the following table mapped:
ingredients = Table('ingredients', metadata,
Column('recipe_title', Unicode, ForeignKey('recipes.title'), primary_key=True),
Column('product_title', Unicode, ForeignKey('products.title'), primary_key=True),
Column('amount', Integer, nullable=False),
Column('unit_title', Unicode, ForeignKey('units.title')))
see that composite primary key? I somehow managed to insert two rows with the same recipe_title/product_title pair. I was surprized to find out that there were not a single constraint on the side of SQLite for this table (no primary key, no fereign key - it was just a plain vanilla table), but well - thats the way sqlalchemy goes, not my business.
Then when I tried to delete a persited object involving those two rows, sqlalchemy saw that it's constraints were violated and it threw the 'StaleDataError'. Finally I just had to remove one duplicatinng row manually from SQLite table.
这篇关于SQLAlchemy StaleDataError删除通过ORM sqlalchemy.orm.exc.StaleDataError插入的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
