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

Question

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?

qris · Accepted Answer · 2019-01-08 10:44:04Z

66

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.

edited Jan 8, 2019 at 10:44

qris

8,2523 gold badges48 silver badges51 bronze badges

answered Mar 27, 2012 at 3:14

rob

2,18315 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Bear Over a year ago

Thanks. I see where I went wrong. A combination of an old tutorial and out of date documentation.

Alexander Price · Accepted Answer · 2022-10-28 16:38:22Z

55

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.

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.

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)

edited Oct 28, 2022 at 16:38

Alexander Price

1431 silver badge8 bronze badges

answered Dec 1, 2015 at 2:12

Carlo Pires

5,0347 gold badges35 silver badges34 bronze badges

3 Comments

makkasi Over a year ago

False - don’t synchronize the session. This option is the most efficient and is reliable once the session is expired, which typically occurs after a commit(), or explicitly using expire_all(). Before the expiration, objects may still remain in the session which were in fact deleted which can lead to confusing results if they are accessed via get() or already loaded collections. kite.com/docs/python;sqlalchemy.orm.query.Query.delete

Shubhank Gupta Over a year ago

Hi, Just want to know how we will get to know if objects are fetched using the filter query or not? What if it's empty and after executing the addresses.delete statement. It deleted 0 items. Is there any way to determine the length or number of rows fetched using addresses object just before executing delete statement?

Deepam Gupta Over a year ago

Do not forget db.session.commit()

Justin · Accepted Answer · 2023-08-18 15:41:07Z

2

create_engine(
            'mssql+pyodbc://@' + server + '/' + database + f'?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server&fast_executemany={fast_executemany}')



with engine.connect() as connection:
            result = connection.execute(text("DELETE FROM [metadata].[test_table] WHERE [id] = 1309"))
            connection.commit()
            print(
                f"Deleted {result.rowcount} row(s) from ")

answered Aug 18, 2023 at 15:41

Justin

12112 bronze badges

1 Comment

John Langford Feb 12 at 15:46

I had been missing the "connection.commit()" part which makes the query actually work. Without this even though the result.rowcount shows the expected non-zero value, it still doesn't actually delete anything.

snakecharmerb · Accepted Answer · 2025-05-25 07:43:37Z

1

Given a Table object, a delete statement may be formed by calling the table's delete method, as demonstrated in the accepted answer by Rob:

stmt = tbl.delete().where(addresses_table.c.retired == 1)

or by passing the table to the generic delete function

import sqlalchemy as sa
...
stmt = sa.delete(tbl).where(addresses_table.c.retired == 1)

In either case, when using SQLAlchemy 2.0+, or 1.4.x with the future flag enabled, the resulting delete object must be explicitly executed by a connection:

with engine.begin() as conn:
    conn.execute(stmt)

(The statement could be executed by a session too, but this question is about deleting without using the ORM)

answered May 25 at 7:43

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Comments

MoxJet · Accepted Answer · 2025-10-25 21:54:37Z

0

Post.query.where(Post.id == 1).delete()
db.session.commit()

This worked for me

answered Oct 25 at 21:54

MoxJet

291 silver badge3 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related