How to delete rows by a condition using sqlalmchemy and fastapi?

Question

I'm trying to delete some rows that a 'null' in a Postgresql db by this:

async def delete_empty_batches():
    query = Batches.delete().where(Batches.c.acquired_by_warehouse_date is None)
    database.execute(query).fetchall()
    s = Batches.select()
    return await database.execute(s).fetchall()

But nothing happens. I have tried to make a query in pgAdmin and it worked but I can't get around with python. I'm green when it comes to programming, how should I be going about this?

Answer 1

Try to use the next code snippet:

from sqlalchemy import select, delete
from sqlalchemy.exc import IntegrityError

async def delete_and_select_batches():
    async with database.transaction():
        # Delete rows with null values in acquired_by_warehouse_date column
        delete_query = delete(Batches).where(Batches.c.acquired_by_warehouse_date == None)
        await database.execute(delete_query)

        # Select remaining rows
        select_query = select([Batches])
        result = await database.execute(select_query)
        return result.fetchall()

Note: == is used instead of is in the where clause to compare values for equality.