SQL Delete in session does not work

[Dirk Makowski]

Hi all,

I am building some CRUD pages for a Pyramid web app, and encountered some (from my point of view as a SQLAlchemy novice) strange behaviour. The UI sends an ID and inside the controller I'd like to delete the corresponding record. I use SQL expressions inside a session for this. The delete statement is correctly built and executed, but the record is still in the database.

A code snippet to test this follows. I have 7 records in the DB, and the first count says 7. After the delete, the count correctly says 6, but still the db has 7 records. Even an explicit transaction.commit() does not help.

    DbSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

    sess = DbSession()

    qry = select([func.count(trm.c.id)])

    total = sess.execute(qry).scalar()

    print "Total:", total

    qdel = trm.delete().where(trm.c.id == 1)

    sess.execute(qdel)

    total = sess.execute(qry).scalar()

    print "Total:", total

    transaction.commit()

Hopefully, anybody here can help me out. Thanks in advance.

Dirk

[Dirk Makowski]

P.S.

In my first attempt, I used the ORM like this

        sess = DbSession()

        entity = sess.query(Role).filter(Role.id==id).one()

        sess.delete(entity)

        sess.flush()

This had worked well. But this way the record first is selected before it is deleted, which I'd liked to eliminate.

[Mike Conley]

Look at the delete() method of query.
http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query.delete

sess.query(Role).filter(Role.id==id).delete()

read the description of synchronize_session parameter to determine what is needed in your application 

--
Mike Conley

[Dirk Makowski]

Excellent, Mike, thanks.

This solves my 2nd post, and there's also an equivalent for update. So I can use

sess.query(Role).filter(Role.id==id).update(values, synchronize_session=False)

where I previously had loaded the entity and then updated it.

Fine, because I am using the ORM again, the problem in post 1 is alleviated. Nonetheless I am still curious about that phenomenon.

Dirk

[Mike Conley]

Looking at the limited snippet in the first post, it seems that the commit should be
    sess.commit()

where does "transaction" come from?


--
Mike Conley

[Dirk Makowski]

The Pyramid framework uses the Zope transaction manager to supply a unit-of-work. From its PKG-INFO:

Name: transaction

Summary: Transaction management for Python

Home-page: http://www.zope.org/Products/ZODB

Author: Zope Corporation

        This package contains a generic transaction implementation for Python. It is

        mainly used by the ZODB, though.

It is injected in SA's session like this:

DbSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

If we call session.commit(), the program will bail with an assertion error, telling we have to use the transaction manager's commit(). This is the way to go

import transaction

transaction.commit()

-----------------------

I have updated my code to use

   sess.query(_Entity).filter(_Entity.id==id).delete(synchronize_session=False)

In some cases it works, in one case it does not, but instead behaves like the code in post 1. The statement is issued (at least, the SA logger prints it, but I assume it never reaches the database), no errors, no rollbacks, but the record stays in the database.

Because the code sometimes is successful, I assume the problem to be in the model, rather than in the session/transaction management. I use declarative model classes like this:

Principal ----- 1:n-------+

                           RoleMember

Role ------------ 1:n------+

The deletes are successful on Principal and Role, the problem occurs on RoleMember which is declared like this:

class RoleMember(DbBase, PymMixin):

    __tablename__ = "rolemember"

    __table_args__ = (

        UniqueConstraint('role_id', 'principal_id'),

        {'schema': 'pym'}

    )

    principal_id = Column(BigInteger,

            ForeignKey("pym.principal.id", onupdate="CASCADE", ondelete="CASCADE"),

            nullable=False)

    role_id = Column(BigInteger,

            ForeignKey("pym.role.id", onupdate="CASCADE", ondelete="CASCADE"),

            nullable=False)

    role = relationship('Role', backref='principal_assocs')

    principal = relationship('Principal', 

        primaryjoin='Principal.id==RoleMember.principal_id',

        backref='role_assocs' )

    def __str__(self):

        return "<RoleMember(id={0}, role_id='{1}', principal_id='{2}'>".format(

            self.id, self.role_id, self.principal_id)

I'm now studying the docs if there's an argument for relationship() that prevents the delete. Or sth. similar...

Any illumination on this is welcome ;)

Dirk

[Michael Bayer]

On Aug 16, 2011, at 6:07 PM, Dirk Makowski wrote:

In some cases it works, in one case it does not, but instead behaves like the code in post 1. The statement is issued (at least, the SA logger prints it, but I assume it never reaches the database), no errors, no rollbacks, but the record stays in the database.

if you see a SQL statement in the log, and it doesn't fail immediately (and you aren't squashing errors in some way), the statement went to the DB for sure.

[Dirk Makowski]

Slowly I am becoming bald...

Here is a stand-alone script that reproduces the behaviour. Code is a bit verbose, had copied it from my actual project, and it uses PostgreSQL database, and you have to issue an "CREATE SCHEMA sample" beforehand. Sorry about that.

The script's output is below. When I inspect the table with psql, the "deleted" record is still present. And SQLAlchemy has logged the statements without errors.

BEGIN TEST

=== COUNTING BEFORE ============================== 

2011-08-17 01:33:41,779 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2011-08-17 01:33:41,779 INFO sqlalchemy.engine.base.Engine SELECT count(sample.rolemember.id) AS count_1 

FROM sample.rolemember

2011-08-17 01:33:41,779 INFO sqlalchemy.engine.base.Engine {}

=== FETCHING ID TO DELETE ============================== 

2011-08-17 01:33:41,780 INFO sqlalchemy.engine.base.Engine SELECT sample.rolemember.id AS sample_rolemember_id, sample.rolemember.ctime AS sample_rolemember_ctime, sample.rolemember.mtime AS sample_rolemember_mtime, sample.rolemember.principal_id AS sample_rolemember_principal_id, sample.rolemember.role_id AS sample_rolemember_role_id, sample.rolemember.owner AS sample_rolemember_owner, sample.rolemember.editor AS sample_rolemember_editor 

FROM sample.rolemember ORDER BY sample.rolemember.id 

 LIMIT %(param_1)s OFFSET %(param_2)s

2011-08-17 01:33:41,780 INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'param_2': 0}

=== DELETING ============================== 

2011-08-17 01:33:41,781 INFO sqlalchemy.engine.base.Engine DELETE FROM sample.rolemember WHERE sample.rolemember.id = %(id_1)s

2011-08-17 01:33:41,781 INFO sqlalchemy.engine.base.Engine {'id_1': 7L}

=== COUNTING AFTER ============================== 

2011-08-17 01:33:41,782 INFO sqlalchemy.engine.base.Engine SELECT count(sample.rolemember.id) AS count_1 

FROM sample.rolemember

2011-08-17 01:33:41,782 INFO sqlalchemy.engine.base.Engine {}

=== RESULT ============================== 

Total before: 7

Deleted id: 7

Total after: 6

[Michael Bayer]

On Aug 16, 2011, at 7:39 PM, Dirk Makowski wrote:

Slowly I am becoming bald...

Here is a stand-alone script that reproduces the behaviour. Code is a bit verbose, had copied it from my actual project, and it uses PostgreSQL database, and you have to issue an "CREATE SCHEMA sample" beforehand. Sorry about that.

The script's output is below. When I inspect the table with psql, the "deleted" record is still present. And SQLAlchemy has logged the statements without errors.

Your script calls the delete() under the __main__ section but then just ends, there's no call to commit().  Add a commit() in and the data is committed (assuming the zope system works, I tested here using just plain Session.commit() - see output below).

=== COUNTING BEFORE ============================== 

2011-08-16 19:47:32,132 INFO sqlalchemy.engine.base.Engine SELECT count(rolemember.id) AS count_1 

FROM rolemember

2011-08-16 19:47:32,132 INFO sqlalchemy.engine.base.Engine {}

=== FETCHING ID TO DELETE ============================== 

2011-08-16 19:47:32,133 INFO sqlalchemy.engine.base.Engine SELECT rolemember.id AS rolemember_id, rolemember.ctime AS rolemember_ctime, rolemember.mtime AS rolemember_mtime, rolemember.principal_id AS rolemember_principal_id, rolemember.role_id AS rolemember_role_id, rolemember.owner AS rolemember_owner, rolemember.editor AS rolemember_editor 

FROM rolemember ORDER BY rolemember.id 

 LIMIT %(param_1)s

2011-08-16 19:47:32,133 INFO sqlalchemy.engine.base.Engine {'param_1': 1}

=== DELETING ============================== 

2011-08-16 19:47:32,135 INFO sqlalchemy.engine.base.Engine DELETE FROM rolemember WHERE rolemember.id = %(id_1)s

2011-08-16 19:47:32,135 INFO sqlalchemy.engine.base.Engine {'id_1': 1L}

=== COUNTING AFTER ============================== 

2011-08-16 19:47:32,136 INFO sqlalchemy.engine.base.Engine SELECT count(rolemember.id) AS count_1 

FROM rolemember

2011-08-16 19:47:32,136 INFO sqlalchemy.engine.base.Engine {}

=== RESULT ============================== 

Total before: 7

Deleted id: 1

Total after: 6

Use e.g. psql to inspect db table

2011-08-16 19:47:32,137 INFO sqlalchemy.engine.base.Engine COMMIT

classics-MacBook-Pro:sqlalchemy classic$ /usr/local/pgsql/bin/psql -U scott test

psql (8.4.4)

Type "help" for help.

test=> select * from rolemember

test-> ;

 id |           ctime           | mtime | principal_id | role_id | owner | editor 

----+---------------------------+-------+--------------+---------+-------+--------

  2 | 2011-08-16 19:47:32.10476 |       |            1 |       2 |     1 |       

  3 | 2011-08-16 19:47:32.10476 |       |            2 |       3 |     1 |       

  4 | 2011-08-16 19:47:32.10476 |       |            2 |       2 |     1 |       

  5 | 2011-08-16 19:47:32.10476 |       |           98 |       3 |     1 |       

  6 | 2011-08-16 19:47:32.10476 |       |           98 |       2 |     1 |       

  7 | 2011-08-16 19:47:32.10476 |       |          101 |       3 |     1 |       

(6 rows)

BEGIN TEST

=== COUNTING BEFORE ============================== 

2011-08-17 01:33:41,779 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2011-08-17 01:33:41,779 INFO sqlalchemy.engine.base.Engine SELECT count(sample.rolemember.id) AS count_1 

FROM sample.rolemember

2011-08-17 01:33:41,779 INFO sqlalchemy.engine.base.Engine {}

=== FETCHING ID TO DELETE ============================== 

2011-08-17 01:33:41,780 INFO sqlalchemy.engine.base.Engine SELECT sample.rolemember.id AS sample_rolemember_id, sample.rolemember.ctime AS sample_rolemember_ctime, sample.rolemember.mtime AS sample_rolemember_mtime, sample.rolemember.principal_id AS sample_rolemember_principal_id, sample.rolemember.role_id AS sample_rolemember_role_id, sample.rolemember.owner AS sample_rolemember_owner, sample.rolemember.editor AS sample_rolemember_editor 

FROM sample.rolemember ORDER BY sample.rolemember.id 

 LIMIT %(param_1)s OFFSET %(param_2)s

2011-08-17 01:33:41,780 INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'param_2': 0}

=== DELETING ============================== 

2011-08-17 01:33:41,781 INFO sqlalchemy.engine.base.Engine DELETE FROM sample.rolemember WHERE sample.rolemember.id = %(id_1)s

2011-08-17 01:33:41,781 INFO sqlalchemy.engine.base.Engine {'id_1': 7L}

=== COUNTING AFTER ============================== 

2011-08-17 01:33:41,782 INFO sqlalchemy.engine.base.Engine SELECT count(sample.rolemember.id) AS count_1 

FROM sample.rolemember

2011-08-17 01:33:41,782 INFO sqlalchemy.engine.base.Engine {}

=== RESULT ============================== 

Total before: 7

Deleted id: 7

Total after: 6

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/yIobsELwojwJ.
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.
<sa-test_many2many_relationship.py>

[Dirk Makowski]

Wow, that was fast :)

You are right, the commit() here finally performs the delete. However, the situation "main section" etc only arose in this script I compiled for testing. The project uses Pyramid where transaction.commit() is called automatically at the end of each request. And as I mentioned above, the same code (without explicit commit()) works fine for Role and Principal, just RoleMember sucks.

Good code from the controller for Principal

@view_config(name='xhr_delete', context='pym:resources.System_UsrMgr_User', renderer='json')

def xhr_delete(context, request):

    try:

        id = int(request.POST['id'])

        sess.query(_Entity).filter(_Entity.id==id).delete(synchronize_session=False)

        sess.flush()

        resp = { 'status': True, 'msg': 'Ok' }

        return resp

    except (StatementError, NoResultFound) as exc:

        resp = { 'status': False, 'msg': repr(exc), 'errors':{} }

        return resp

Bad code from the controller for RoleMember:

@view_config(name='xhr_delete', context='pym:resources.System_UsrMgr_RoleMember', renderer='json')

def xhr_delete(context, request):

    try:

        id = int(request.POST['id'])

        sess.query(_Entity).filter(_Entity.id==id).delete(synchronize_session=False)

        #sess.flush()

        transaction.commit()

        resp = { 'status': True, 'msg': 'Ok' }

        return resp

    except (KeyError, StatementError, NoResultFound) as exc:

        resp = { 'status': False, 'msg': repr(exc), 'errors':{} }

        return resp

[Dirk Makowski]

Boy, is my face red now.

I had initialised the _Entity variable in RoleMember's controller wrongly which directed the delete on a different table...

SOLVED.

Anyhow, many thanks for your help and sorry for my 3-am-code. (Actually it's 2:30 am here ;)

Kr, Dirk