Batch Delete with ORM

[Mark Erbaugh]

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

[Michael Bayer]

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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Mark Erbaugh]

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+...@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

[Michael Bayer]

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.

[Mark Erbaugh]

That makes sense.

Thanks,

Mark

[Chris Withers]

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

[Warwick Prince]

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!? :-)

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To post to this group, send email to sqlal...@googlegroups.com.

[Mark Erbaugh]

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