如何在没有ORM的情况下使用SQLAlchemy查询从表中删除行?

时间:2022-10-21 22:26:05

I'm writing a quick and dirty maintenace script to delete some rows and would like to avoid having to bring my ORM classes/mappings over from the main project. I have a query that looks similar to:

我正在编写一个快速而脏的维护脚本来删除一些行,并且希望避免从主项目中删除我的ORM类/映射。我有一个看起来类似于的查询:

address_table = Table('address',metadata,autoload=True)
addresses = session.query(addresses_table).filter(addresses_table.c.retired == 1)

According to everything I've read, if I was using the ORM (not 'just' tables) and passed in something like:

根据我读过的所有内容,如果我使用的是ORM(不仅仅是'表')并传递了类似于:

addresses = session.query(Addresses).filter(addresses_table.c.retired == 1)

I could add a .delete() to the query, but when I try to do this using only tables I get a complaint:

我可以在查询中添加.delete(),但是当我尝试仅使用表格时,我会收到投诉:

File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 2146, in delete
    target_cls = self._mapper_zero().class_
AttributeError: 'NoneType' object has no attribute 'class_'

Which makes sense as its a table, not a class. I'm quite green when it comes to SQLAlchemy, how should I be going about this?

这是一个有意义的表,而不是一个类。在谈到SQLAlchemy时,我非常环保,我该怎么做呢?

2 个解决方案

#1


29  

Looking through some code where I did something similar, I believe this will do what you want.

通过一些代码,我做了类似的事情,我相信这会做你想要的。

d = addresses_table.delete(addresses_table.c.retired == 1)
d.execute()

Calling delete() on a table object gives you a sql.expression (if memory serves), that you then execute. I've assumed above that the table is bound to a connection, which means you can just call execute() on it. If not, you can pass the d to execute(d) on a connection.

在表对象上调用delete()会为您提供sql.expression(如果内存服务),然后执行。我假设上面的表绑定了一个连接,这意味着你可以在它上面调用execute()。如果没有,您可以传递d以在连接上执行(d)。

See docs here.

请参阅此处的文档。

#2


22  

When you call delete() from a query object, SQLAlchemy performs a bulk deletion. And you need to choose a strategy for the removal of matched objects from the session. See the documentation here.

从查询对象调用delete()时,SQLAlchemy执行批量删除。您需要选择一种策略来从会话中删除匹配的对象。请参阅此处的文档。

If you do not choose a strategy for the removal of matched objects from the session, then SQLAlchemy will try to evaluate the query’s criteria in Python straight on the objects in the session. If evaluation of the criteria isn’t implemented, an error is raised.

如果您没有选择从会话中删除匹配对象的策略,那么SQLAlchemy将尝试直接在会话中的对象上评估Python中的查询条件。如果未实施标准评估,则会引发错误。

This is what is happening with your deletion.

这是删除时发生的情况。

If you only want to delete the records and do not care about the records in the session after the deletion, you can choose the strategy that ignores the session synchronization:

如果您只想删除记录而不关心删除后会话中的记录,则可以选择忽略会话同步的策略:

address_table = Table('address', metadata, autoload=True)
addresses = session.query(address_table).filter(address_table.c.retired == 1)
addresses.delete(synchronize_session=False)

#1


29  

Looking through some code where I did something similar, I believe this will do what you want.

通过一些代码,我做了类似的事情,我相信这会做你想要的。

d = addresses_table.delete(addresses_table.c.retired == 1)
d.execute()

Calling delete() on a table object gives you a sql.expression (if memory serves), that you then execute. I've assumed above that the table is bound to a connection, which means you can just call execute() on it. If not, you can pass the d to execute(d) on a connection.

在表对象上调用delete()会为您提供sql.expression(如果内存服务),然后执行。我假设上面的表绑定了一个连接,这意味着你可以在它上面调用execute()。如果没有,您可以传递d以在连接上执行(d)。

See docs here.

请参阅此处的文档。

#2


22  

When you call delete() from a query object, SQLAlchemy performs a bulk deletion. And you need to choose a strategy for the removal of matched objects from the session. See the documentation here.

从查询对象调用delete()时,SQLAlchemy执行批量删除。您需要选择一种策略来从会话中删除匹配的对象。请参阅此处的文档。

If you do not choose a strategy for the removal of matched objects from the session, then SQLAlchemy will try to evaluate the query’s criteria in Python straight on the objects in the session. If evaluation of the criteria isn’t implemented, an error is raised.

如果您没有选择从会话中删除匹配对象的策略,那么SQLAlchemy将尝试直接在会话中的对象上评估Python中的查询条件。如果未实施标准评估,则会引发错误。

This is what is happening with your deletion.

这是删除时发生的情况。

If you only want to delete the records and do not care about the records in the session after the deletion, you can choose the strategy that ignores the session synchronization:

如果您只想删除记录而不关心删除后会话中的记录,则可以选择忽略会话同步的策略:

address_table = Table('address', metadata, autoload=True)
addresses = session.query(address_table).filter(address_table.c.retired == 1)
addresses.delete(synchronize_session=False)