Search code examples
pythonormsqlalchemy

How to delete rows from a table using an SQLAlchemy query without ORM?

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:

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:

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:

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?

Solution

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

    d = addresses_table.delete().where(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.

    See docs here.