Re: [sqlalchemy] Batch Delete with ORM
On 04/10/2010 13:16, Mark Erbaugh wrote: If I were doing this in SQL, I would to the first command as SELECT count(*) FROM period WHERE period.cycle = ? Why would you do this first? Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Batch Delete with ORM
Hi All Just my 2c; The original question was why is SA doing the select before it does the delete? and then the comment was added that he would have done a simple count instead.. It appears that he was not aware that the DELETE could return the count as well (indirectly) so in actual fact, NEITHER the SELECT count OR the SELECT that SA inserts in front of the DELETE appear to be required. So, back to the thread of the question - Why is SA doing the query that it does BEFORE it does the DELETE? It's purpose is not obvious. Hope that helps!? :-) On 5 October 2010 18:48, Chris Withers ch...@simplistix.co.uk wrote: On 04/10/2010 13:16, Mark Erbaugh wrote: If I were doing this in SQL, I would to the first command as SELECT count(*) FROM period WHERE period.cycle = ? Why would you do this first? Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Batch Delete with ORM
On Oct 5, 2010, at 4:48 AM, Chris Withers wrote: On 04/10/2010 13:16, Mark Erbaugh wrote: If I were doing this in SQL, I would to the first command as SELECT count(*) FROM period WHERE period.cycle = ? Why would you do this first? I wasn't sure why SA was issuing a select realperiod from period where period.cycle = ? before deleting the records. I incorrectly assumed that it was trying to get the count of records to be deleted. Michael Bayer pointed out that cursor.rowcount was used to get that value and that the code in question was used by the session to keep the local (in memory) copy of the data up to date. Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Batch Delete with ORM
I have a table described with: BASE = declarative_base(name=APP_ID) class Period(BASE): __tablename__ = 'period' realperiod = Column(Integer, primary_key=True) cycle = Column(Integer) [more columns] I want to delete all records with a given value for the cycle column. With the SQLAlchemy session in the variable session and the desired cycle in y, I execute q = session.query(Period).filter(Period.cycle==y).delete() This correctly deletes the desired rows and returns the number of rows deleted; however, if I turn echo on I see the following SQL commands: SELECT period.realperiod AS period_realperiod FROM period WHERE period.cycle = ? DELETE FROM period WHERE period.cycle = ? If I were doing this in SQL, I would to the first command as SELECT count(*) FROM period WHERE period.cycle = ? to get the count of rows to be deleted so I am wondering if I am doing things correctly. Thanks, Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Batch Delete with ORM
On Oct 4, 2010, at 8:16 AM, Mark Erbaugh wrote: I have a table described with: BASE = declarative_base(name=APP_ID) class Period(BASE): __tablename__ = 'period' realperiod = Column(Integer, primary_key=True) cycle = Column(Integer) [more columns] I want to delete all records with a given value for the cycle column. With the SQLAlchemy session in the variable session and the desired cycle in y, I execute q = session.query(Period).filter(Period.cycle==y).delete() This correctly deletes the desired rows and returns the number of rows deleted; however, if I turn echo on I see the following SQL commands: SELECT period.realperiod AS period_realperiod FROM period WHERE period.cycle = ? DELETE FROM period WHERE period.cycle = ? If I were doing this in SQL, I would to the first command as SELECT count(*) FROM period WHERE period.cycle = ? to get the count of rows to be deleted so I am wondering if I am doing things correctly. Most relational databases report the number of rows matched by any UPDATE or DELETE statement that just executed, and SQLA acquires this value via cursor.rowcount on any such statement, so a separate count() call is not required. Thanks, Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Batch Delete with ORM
On Oct 4, 2010, at 5:46 PM, Michael Bayer wrote: On Oct 4, 2010, at 8:16 AM, Mark Erbaugh wrote: I have a table described with: BASE = declarative_base(name=APP_ID) class Period(BASE): __tablename__ = 'period' realperiod = Column(Integer, primary_key=True) cycle = Column(Integer) [more columns] I want to delete all records with a given value for the cycle column. With the SQLAlchemy session in the variable session and the desired cycle in y, I execute q = session.query(Period).filter(Period.cycle==y).delete() This correctly deletes the desired rows and returns the number of rows deleted; however, if I turn echo on I see the following SQL commands: SELECT period.realperiod AS period_realperiod FROM period WHERE period.cycle = ? DELETE FROM period WHERE period.cycle = ? If I were doing this in SQL, I would to the first command as SELECT count(*) FROM period WHERE period.cycle = ? to get the count of rows to be deleted so I am wondering if I am doing things correctly. Most relational databases report the number of rows matched by any UPDATE or DELETE statement that just executed, and SQLA acquires this value via cursor.rowcount on any such statement, so a separate count() call is not required. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. Thanks. What is the purpose of SELECT period.realperiod AS period_realperiod FROM period WHERE period.cycle = ? that appears to be generated by the session.query ... call? Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Batch Delete with ORM
On Oct 4, 2010, at 8:06 PM, Mark Erbaugh wrote: Thanks. What is the purpose of SELECT period.realperiod AS period_realperiod FROM period WHERE period.cycle = ? that appears to be generated by the session.query ... call? the delete() and update() methods on Query want to invalidate all objects currently in the session which match the criterion. There are three options for this behavior, and in 0.5 the default is fetch which is what that appears to be doing. I would set it to evaluate so that it only scans through memory instead of hitting the DB, or None so that it doesn't invalidate anything, if you aren't concerned about coming across those objects in the same transaction. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Batch Delete with ORM
On Oct 4, 2010, at 8:30 PM, Michael Bayer wrote: On Oct 4, 2010, at 8:06 PM, Mark Erbaugh wrote: Thanks. What is the purpose of SELECT period.realperiod AS period_realperiod FROM period WHERE period.cycle = ? that appears to be generated by the session.query ... call? the delete() and update() methods on Query want to invalidate all objects currently in the session which match the criterion. There are three options for this behavior, and in 0.5 the default is fetch which is what that appears to be doing. I would set it to evaluate so that it only scans through memory instead of hitting the DB, or None so that it doesn't invalidate anything, if you aren't concerned about coming across those objects in the same transaction. That makes sense. Thanks, Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
