SQLAlchemy Core How to executemany with Delete

You can bulk delete in SQLAlchemy Core, which uses the DB-API's executemany semantics, in order to delete many rows from a table using one round trip to the database.

The trick is the use of sqlalchemy.bindparam, as shown below:

from sqlalchemy import Table, Column, Integer, MetaData

metadata = MetaData()

Foo = Table( 
    'foos', metadata,
    Column('bar', Integer)
)

conn = sqlalchemy.connect('...')

conn.execute(
    Foo.delete(Foo.c.bar == bindparam('bar')),
    [
        {'bar': 1},
        {'bar': 2},
        {'bar': 3}
    ]
)

You can prove it deletes with one round trip by turning on logging:

import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

This will output the following when you perform the delete:

INFO:sqlalchemy.engine.Engine:DELETE FROM foos WHERE foos.bar = %(bar)s INFO:sqlalchemy.engine.Engine:[generated in 0.00024s] ({'bar': '1'}, {'bar': '2'}, {'bar': '3'})

SQLAlchemy Core How to executemany with Delete

Comments

Add Comment