On May 13, 2008, at 9:08 AM, jean-philippe dutreve wrote:
> > I'd like to delete all Transactions contained in an account hierarchy > without loading any transaction into memory, just DB work with the SQL > DELETE request constructed by SA. > > The query that defines the transactions is: > Session.query(Transaction).join(['entries','account','root'], > aliased=True).filter_by(account_id=1).all() > > How can I use it to construct and execute the DELETE statement? > To use the Query directly in 0.4 (as opposed to constructing the equivalent select() construct) is a little awkward: q = Session.query(Transaction).join(['entries','account','root'], aliased=True).filter_by(account_id=1) stmt = q.statement stmt.use_labels = False Session.execute(transaction_table.delete().where(exists([1], Transaction.id==stmt.c.id)) in 0.5 you can do this in a few ways: q = Session.query(Transaction.id).join(...).filter(...) Session.execute(table.delete().where(Transaction.id.in_(q.statement))) or if you're on Oracle which doesn't like "IN (SELECT ...)": ta = aliased(Transaction) q = Session.query(ta.id).join(...).filter(...) stmt = q.statement Session .execute(table.delete().where(exists(stmt.where(ta.id==Transaction.id))) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
