[sqlalchemy] Re: SQL mass-delete
fine. thank you for your help. jean-philippe --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL mass-delete
On May 13, 2008, at 11:28 AM, jean-philippe dutreve wrote: > > Ok thanks. > > Transaction_id is defined twice, one from transaction table another > from entries: yeah can you just use a select() for this, for now ? Query in 0.4 really wasnt designed to be resused as a subquery. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL mass-delete
Ok thanks. Transaction_id is defined twice, one from transaction table another from entries: ProgrammingError: (ProgrammingError) column reference "transaction_id" is ambiguous LINE 5: ...winancial_integ.acc_transactions.transaction_id = aaa.transa... ^ 'DELETE FROM winancial_integ.acc_transactions WHERE EXISTS (SELECT 1 \nFROM (SELECT winancial_integ.acc_transactions.transaction_id AS transaction_id, winancial_integ.acc_transactions.value_date AS value_date, winancial_integ.acc_transactions.code AS code, winancial_integ.acc_transactions.description AS description, acc_entries_1.entry_id AS entry_id, acc_entries_1.account_id AS account_id, acc_entries_1.transaction_id AS transaction_id On May 13, 5:05 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On May 13, 2008, at 11:03 AM, jean-philippe dutreve wrote: > > > > > I've this error: > > ProgrammingError: (ProgrammingError) subquery in FROM must have an > > alias > > HINT: For example, FROM (SELECT ...) [AS] foo. > > 'DELETE FROM winancial_integ.acc_transactions WHERE EXISTS (SELECT 1 > > \nFROM (SELECT winancial_integ.acc_transactions.transaction_id AS > > transaction_id, ... > > hi, then just call statement.alias() to make it into an alias. > > - mike --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL mass-delete
On May 13, 2008, at 11:03 AM, jean-philippe dutreve wrote: > > I've this error: > ProgrammingError: (ProgrammingError) subquery in FROM must have an > alias > HINT: For example, FROM (SELECT ...) [AS] foo. > 'DELETE FROM winancial_integ.acc_transactions WHERE EXISTS (SELECT 1 > \nFROM (SELECT winancial_integ.acc_transactions.transaction_id AS > transaction_id, ... hi, then just call statement.alias() to make it into an alias. - mike --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL mass-delete
I've this error: ProgrammingError: (ProgrammingError) subquery in FROM must have an alias HINT: For example, FROM (SELECT ...) [AS] foo. 'DELETE FROM winancial_integ.acc_transactions WHERE EXISTS (SELECT 1 \nFROM (SELECT winancial_integ.acc_transactions.transaction_id AS transaction_id, ... The API in 0.5 is very good. On May 13, 4:41 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL mass-delete
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 -~--~~~~--~~--~--~---
